Access Append two Joined Tables
Posted on 2009-05-14
In my database, I have 2 tables: 1 table holds promotion (Promos) info, and one table holds items awarded as part of a Promo (PromosDetails). I have my database transferring an excel spreadsheet using the vba transferspreadsheet function. My problem is this: The excel file might have multiple Promotions in it. However, each Promotion need not have PromoDetails associated with it. The PromoID key field is a foreign key in the PromosDetails table. When I use the append query, it adds the data to the Promos table without a problem. I need to do a check to see if the Promo has PromoDetails, and if so, put that into the PromosDetails table. I do not want the PromosDetails table to hold blank data. Somehow, I need to write the logic to say that:
-After appending the excel data to the Promos table, see if it has PromosDetails info.
-If it has PromosDetails info, find the PromoID that it created, and put that info into the 'many' table.
-To make matters confusing, 1 Promo can have multiple PromosDetails associated with it.
I hope this makes sense!