Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to take values from 2 tables to append to a 3rd table

Status
Not open for further replies.

danwand

Programmer
Jun 8, 2003
100
GB
Hi,

I am working on a staff training database with the following tables

tblStaffDetails tblInductionTraining
StaffID - (primary key) ITTopicID - (Primary key)
name topic
address produrereferral
etc internalAudit

I also have a 3rd table
tblStaffInductionTraining
StaffID -(primary key)-(foriegn key from tblStaffdetails)
ITTopicID -(primary key)-(foreign key fromtblInductionTraining)
Date
TrainingDone? (Yes/no field)

Both tblstaffdetails and tblInductionTraining already contain information previously entered.
All staff members will have a corresponding record (tblStaffInductionTraining) for each and every induction training topic whether they have done the training or not

If i was to manually create tblStaffInductionTraining the data in the table would typically be as follows

StaffID ITTopicID Date Done
1 1 11/10/03 yes
1 2
1 3
2 1
2 2
2 3 01/09/03 yes


What i need to know is how to automatically get the data into tblStaffInductionTraining using some type of query (Append table or make table??) based on the other 2 tables (tblStaffDetails, tblInductionTraining) holding a record for each and every staffID and each and every ITTopicID.

Not too sure if i have explained the situation clearly but please write for clarification.

Any help would be greatly appreciated.


Dan
 
You want a cartesian product ?
DoCmd.RunSQL "INSERT INTO tblStaffInductionTraining (StaffID, ITTopicID)" _
& " SELECT StaffID, ITTopicID FROM tblStaffDetails, tblInductionTraining"

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

Exactly what i needed for now...

Dan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top