Solved

Access Append two Joined Tables

Posted on 2009-05-14
6
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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