Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Append two Joined Tables

Posted on 2009-05-14
6
Medium Priority
?
198 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 500 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 500 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

721 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