Solved

Access Append two Joined Tables

Posted on 2009-05-14
6
189 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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