Help with append query

[KerryL]

I'm having trouble getting an append query to add data to a second table depending what it finds in the first table.

TABLE 1 = tblContacts
FIELDS INVOLVED = ContactID & Category

TABLE 2 = tblContactCats
FIELDS INVOLVED = ContactID & CategoryID

TABLE 3 = tblCategories
FIELDS = CategoryID & CategoryName

tblContacts.ContactID linked to tblContactCats.ContactID
tblContactCats.CategoryID linked to tblCategories.CategoryID


tblContactCats is currently empty. Here's what I need the append query to do:
For each record that contains an entry in tblContact.Category that matches a record in tblCategories.CategoryName, a new record should be created in tblContactCats where tblContactCats.ContactID = tblContacts.ContactID AND tblContactCats.CategoryID = tblCategories.CategoryID.

IOW, I'm trying to add records to tblContactCats that link records in tblContacts to the categories in tblCategories.

Hope I've explained it well enough to make sense. If not, please let me know.

 

[PHV]

INSERT INTO tblCategories(ContactID,CategoryID)
SELECT A.ContactID, B.CategoryID FROM tblContacts A INNER JOIN tblCategories B ON A.Category=B.CategoryName

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

 

[KerryL]

Insert into tblCategories? I'm using tblCategories.CategoryID and tblContacts.ContactID to create a record in tblContactCats. Why would I insert into tblCategories?

Or am I misunderstanding your inner join explanation?

 

[PHV]

Oops, sorry for the typo:
INSERT INTO tblContactCats (ContactID,CategoryID)
SELECT A.ContactID, B.CategoryID FROM tblContacts A INNER JOIN tblCategories B ON A.Category=B.CategoryName;

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