Solved

Access Append two Joined Tables

Posted on 2009-05-14
6
187 Views
Last Modified: 2012-05-07
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
Comment
Question by:Prakesh
  • 4
6 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24390680
Check this Demo database.
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24390686
This time with mdb....
Normalize-Append.mdb
0
 
LVL 7

Accepted Solution

by:
shambalad earned 125 total points
ID: 24390741
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
 

Author Comment

by:Prakesh
ID: 24414098
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
 
LVL 7

Assisted Solution

by:shambalad
shambalad earned 125 total points
ID: 24417461
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now