If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple EmployeeID fields in a table
I believe my tblECR Needs to be broken apart to achieve 1 EmployeeID field
per table, but I'm not too sure on the best approach for this. This is an engineering ECR tracking database where each record shows multiple tasks performed by multiple employees. Can someone point me toward north on this? Thanks! Tables: tblCustomers CustomerID CustomerName tblECR ECRNumber PartNumber CustomerID DateInitiated |-------------OriginatedBy | ChangeSummary | JobNumber |-------------LoggedBy | ImportanceID |-------------ReviewedBy | DateReviewed | DateReq | VantageChecked | Attachments | ReqDwg |-------------DwgRevBy | DwgRevDate | ReqMaterials |-------------MaterialsRevBy | MaterialsRevDate | ReqCutlist |-------------CutlistRevBy | CutlistRevDate | ReqProgram |-------------ProgramRevBy | ProgramRevDate | EngNotes | | tblEmployees |-------------EmployeeID FirstName LastName tblImportance ImportanceID Importance tblParts PartNumber PartDesc |
#2
|
|||
|
|||
Multiple EmployeeID fields in a table
I would remove the dates and employees from tblECR. Assuming ECRNumber is
the primary key, create a related table like: tblECRTasks =================== ecrtskID autonumber primarykey ECRNumber links to tblECR.ECRNumber EmployeeID links to tblEmployees.EmployeeID TaskID links to a new table of tasks TaskCompleteDate -- Duane Hookom MS Access MVP -- "Dave Hoder" wrote in message ... I believe my tblECR Needs to be broken apart to achieve 1 EmployeeID field per table, but I'm not too sure on the best approach for this. This is an engineering ECR tracking database where each record shows multiple tasks performed by multiple employees. Can someone point me toward north on this? Thanks! Tables: tblCustomers CustomerID CustomerName tblECR ECRNumber PartNumber CustomerID DateInitiated |-------------OriginatedBy | ChangeSummary | JobNumber |-------------LoggedBy | ImportanceID |-------------ReviewedBy | DateReviewed | DateReq | VantageChecked | Attachments | ReqDwg |-------------DwgRevBy | DwgRevDate | ReqMaterials |-------------MaterialsRevBy | MaterialsRevDate | ReqCutlist |-------------CutlistRevBy | CutlistRevDate | ReqProgram |-------------ProgramRevBy | ProgramRevDate | EngNotes | | tblEmployees |-------------EmployeeID FirstName LastName tblImportance ImportanceID Importance tblParts PartNumber PartDesc |
#3
|
|||
|
|||
Multiple EmployeeID fields in a table
Thanks Duane!
That's the kick I needed. This started as a one table example that I whipped up to show the powers that be what was possible but they insisted on using Excel. I kept up with the Excel records for a while & have a couple hundred records that I'd like to keep. Any way you can see to re-shuffle the data or do I need to start over? I don't think importing is an option since the formatting is a mess (each line of a description on a different row, etc. THE HORROR!). They now have 450 tabs on 3 Excel files & they're all a disaster. |
#4
|
|||
|
|||
Multiple EmployeeID fields in a table
You might be able to salvage some stuff from Excel. It's hard to say. I
sometimes import and other times copy and paste. With 450 tabs in 3 files, you have a lot of work ahead of you. -- Duane Hookom MS Access MVP -- "Dave Hoder" wrote in message ... Thanks Duane! That's the kick I needed. This started as a one table example that I whipped up to show the powers that be what was possible but they insisted on using Excel. I kept up with the Excel records for a while & have a couple hundred records that I'd like to keep. Any way you can see to re-shuffle the data or do I need to start over? I don't think importing is an option since the formatting is a mess (each line of a description on a different row, etc. THE HORROR!). They now have 450 tabs on 3 Excel files & they're all a disaster. |
#5
|
|||
|
|||
Multiple EmployeeID fields in a table
Also, I'm guessing now that I'll have multiple task records for each ECR I'll
need a subform to display what's been done & allow the user to check off that a task has been completed? "Duane Hookom" wrote: I would remove the dates and employees from tblECR. Assuming ECRNumber is the primary key, create a related table like: tblECRTasks =================== ecrtskID autonumber primarykey ECRNumber links to tblECR.ECRNumber EmployeeID links to tblEmployees.EmployeeID TaskID links to a new table of tasks TaskCompleteDate |
#6
|
|||
|
|||
Multiple EmployeeID fields in a table
Yep. You should have a subform that allows any number of tasks with the
associated fields. This is very common. -- Duane Hookom MS Access MVP -- "Dave Hoder" wrote in message ... Also, I'm guessing now that I'll have multiple task records for each ECR I'll need a subform to display what's been done & allow the user to check off that a task has been completed? "Duane Hookom" wrote: I would remove the dates and employees from tblECR. Assuming ECRNumber is the primary key, create a related table like: tblECRTasks =================== ecrtskID autonumber primarykey ECRNumber links to tblECR.ECRNumber EmployeeID links to tblEmployees.EmployeeID TaskID links to a new table of tasks TaskCompleteDate |
#7
|
|||
|
|||
Multiple EmployeeID fields in a table
Thanks Duane,
If you're still watching this thread I have one more question. I set up my database as you suggested & have a subform showing completed tasks for the ECR. The subform is locked & I have a button to bring up another form that automatically enters the ECR number in the proper field & the user must enter their name, task completed & date. when they close the form the subform at the bottom of the main ECR form shows the list of completed tasks. However one of the tasks is to actually enter the ECR itself. It seems to make more sense visually to leave the "OriginatedBy" & "DateInitiated" fields in the main table so the user can just tab through the fields & save the record for action at a later date. If I do this & link the EmployeeID to 2 tables, I get no records in my query. Can you give me some advice on how to approach this? Thanks for your help! "Duane Hookom" wrote: Yep. You should have a subform that allows any number of tasks with the associated fields. This is very common. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query is not updatable - | Doug Johnson via AccessMonster.com | Running & Setting Up Queries | 3 | January 21st, 2006 12:36 AM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |