How to automatically update a join table

[jeswel]

Hi, I have probably a simple problem, that has been plaguing me for weeks.

I have 3 tables (i'm simplifying all the fields)

tblPHYSICIANS (50 unique records)
PHYSICIAN_id

tblSPECIALISTS (200 unique records)
SPECIALISTS_id
PHYSICIAN_rank

tblJOIN
PHYSICIAN_id
SPECIALISTS_id
PHYSICIAN_rank

The goal is to populate my join table so each of the 50 PHYSICIAN_id's can rank individually the 200 SPECIALISTS_id's, resulting in 10,000 records. This is important especially if someone adds a new SPECIALIST_ID record to the tblSPECIALISTS. I have the relationship set up so it's a one to many relationship from each main table into the Join table. Wishful thinking or is this possible to automate?
Thank You!!
Jessi

 

[PHV]

So, you want a cross join (cartesian product) ?

INSERT INTO tblJOIN
SELECT PHYSICIAN_id, SPECIALISTS_id, PHYSICIAN_rank
FROM tblPHYSICIANS, tblSPECIALISTS

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

INSERT INTO
tblJoin ( physicianID, specialistID, Rank )
SELECT
tblPhysician.physicianID, tblSpecialist.specialistID, Null AS Rank
FROM
tblPhysician,
tblSpecialist
WHERE (((tblPhysician.physicianID) Not In (select physicianID from tblJoin))) OR (((tblSpecialist.specialistID) Not In (select specialistID from tblJoin)));

If you add a physician or a specialist it will create a place holder record for every combination and not overwrite existing records.

 

[jeswel]

PHV, it worked perfectly! thank you so very much.

-Jessi