Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Access Append two Joined Tables

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!
Thanks!
0
Prakesh
Asked:
Prakesh
  • 4
2 Solutions
 
shambaladCommented:
Check this Demo database.
0
 
shambaladCommented:
This time with mdb....
Normalize-Append.mdb
0
 
shambaladCommented:
Here's how this works:
1. Clear the import table 'tblPromosImport' by running query '01 qryClearTblPromosImport'.
2. Import the data from the workbook into the import table.
3. Load the promo table from the import table using query '02 qryLoadTblPromosFromImport'.
3. Load the detail table from the import table using query '03 qryLoadTblPromosDetailFromImport'

Attached is the workbook I used for the import.
Promos.xls
0
 
PrakeshAuthor Commented:
Thanks for the response! I think I'm getting close. What if the Promo field is a text field and not a name field, and can possibly have duplicates?
0
 
shambaladCommented:
Access does not have a 'name' data type. The Promo field is a text field. When the promo table is loaded, the record is assigned a unique number ,'PromoID' by Access. This, in turn is the key used to link the detail record to the header record.
Note that in the example, the 'Promo' text field is not, of itself, unique. What is considered unique is the composite of the 'Promo' field and the 'Customer' field. Perhaps you have a secondary field like that?
For that matter, if a duplicate promo number is added in a different batch, it won't be a problem because the primary table key is not the 'Promo' field.
Todd
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now