Multiple EmployeeID fields in a table

G

Guest

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
 
D

Duane Hookom

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
 
G

Guest

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.
 
D

Duane Hookom

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.
 
G

Guest

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?
 
D

Duane Hookom

Yep. You should have a subform that allows any number of tasks with the
associated fields. This is very common.
 
G

Guest

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!
 
Top