Populate Linked Table

[tomco22]

I am designing a database to assign employees to the various work positions each day. Two tables, tblPosition and tblDate are joined by the tblPositionDate table. The tblPositionDate table has a compound primary key consisting of “date” and “position”. What is the best way to auto-populate the tblPositionDate table with every position name for a given date? For compactness I would prefer to populate up to a particular date rather than filling in the entire year. The absolute best case would be if the population took place when my form first goes to a given date.

Tom
MCSD
I Nab Terrorists

 

[PHV]

Something like this ?
INSERT INTO tblPositionDate ([date field],[position field])
SELECT [date field],[position field] FROM tblDate, tblPosition
WHERE [date field] = [Forms]![form name]![date control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I think he's asking for each position and the selected date, enter a record:

INSERT INTO tblPositionDate ([date field],[position field])
SELECT [Forms]![form name]![date control],[position field] FROM tblPosition

I don't know if you can use a parameter like that or not though!

So,

when my form first goes to a given date

say 'October 16', you want to enter

10/16/2004 position1
10/16/2004 position2
10/16/2004 position3

right?


Leslie

 

[tomco22]

Thanks PH & Leslie. Your input solved my issue.

Tom
MCSD
I Nab Terrorists