q

[KristenD]

I created a junction table in order to create a many-to-many relationship between two tables. My question is this: Do I run an update query in order to populate the information from the two primary keys (which are the foreign keys in the junction table) in the tables?

Basically I took:
tblEmpInfo (PopUp Form based on another table if Emp is Active)
EmpInfoID <PK>
EmpIDFK
JobNumber

tblEmpRating
EmpRatingID <PK>
EmpID2
EffectiveDate
SuptID
Status
Craft

And created the junction table:
tblEmpRateInfo
EmpRateInfoID <PK>
EmpInfoIDFK
EmpRatingIDFK

I currently have data in the other tables.

Also, to throw a monkey wrench in the works, the tblEmpInfo only has data entered into it IF the employee is active. But I also want to make sure that it is tracking the same for each status change. For Example:
The data entered for EmpID 1234 who is active is transferred to Job C123412 on 12/5/12. So what I would like to see in a query is that status change reflecting the new Job number so it corresponds with the date. I hope I'm explaining myself correctly!

 

[KristenD]

UPDATE: I cannot do an update query because the foreign keys are primary keys that use an auto number.

Suggestions??

 

[John Big Booty]

Perhaps this sample will give you some ideas.

 

[KristenD]

If I'm understanding this correctly, I have to do the update query through a form?

 

[KristenD]

I can post my zipped database if you would like.

 

[John Big Booty]

If I'm understanding this correctly, I have to do the update query through a form?

It's a whole lot easier to control that way