SQL for inserting rows from a lookup table

[credo]

Hello,
Can someone point me in the right direction for building the SQL to do the following (I've used generic names for the example - hopefully to make the concept clearer) : I've tried an insert/append query but am not sure how to insert multiple rows based on another field criteria.

There are 2 tables.

Table 1 = Tbl_ToBeUpdated (ID, departure).
Table 2 = Tbl_SourceTable(ID, LGW,LHR,STN,MAN etc.... about 20 airport departure point fields). The departure point fields are text and are either "Yes" or "No" indicating whether that ID departs from that airport.There are multiple departure points per record.
eg.
ID LGW LHR STN MAN BHX etc... BFS
0001 YES NO NO YES NO YES

I need to insert records into Tbl_ToBeUpdated based on how many depearture points are Yes,so in above example there would be 3 records inserted into the new table for ID = 0001.

Tbl_ToBeUpdated
ID departure
0001 LGW
0001 MAN
0001 BFS

I need to fdo this for over 1500 id's. Any help appreciated.

 

[tlbroadbent]

Here is one method utilizing a UNION query.

INSERT INTO tblDeparturesNew
SELECT *
FROM [SELECT ID, 'LGW' AS Departure
FROM tblDepartures
WHERE LGW=True
Union
SELECT ID, 'STN' AS Departure
FROM tblDepartures
WHERE STN=True
Union
SELECT ID, 'BFS' AS Departure
FROM tblDepartures
WHERE BFS=True]. AS uq;

There may be a limitation on the number of UNIONs that Access will allow in one query but I don't know what the limit is. I just created a query with 22 UNIONs in Access 2000 and encountered no problems. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.