How to Repair Dll Error Code In a Simple Way?: How to Fix - Append 2 tables in one to many relationship?

Append 2 tables in one to many relationship

Hello all, I'm hoping that the gurus here can help me with a problem.

I have 2 tables, tblInvoices with autonumber pk and tblInvoiceDetails with pk of PartNumber and fk Autonumber .
The relationship between tblInvoices and tblInvoiceDetails is a one to many.
I have some data in a spreadsheet that needs to be added to the
existing tables.
I can write the queries so that everything updates properly if there is only one corresponding record going into the tblInvoiceDetails, but my vb skills aren't polished enough to give me the code needed to add data if I have more than one
transaction corresponding to the tblInvoices.
In other words, if I buy one item the queries append the information correctly but if I buy more than one item the information isn't added correctly.
I'm sure that we need some loops to do this but like I said,
my vb skills are severely lacking.

Example data would be something like this:

Spreadsheet

Dec 21, 2011 John Smith coffee 5
Dec 21, 2011 John Smith cream 1
Dec 21, 2011 John Smith sugar 2
Dec 22, 2011 Jane Doe coffee 2
Dec 22, 2011 Jane Doe scone 1
Dec 22, 2011 Jane Doe tea 3
Dec 22, 2011 Jane Doe sugar 1

tblInvoices
Autonumber Date Customer
101 Dec 21, 2011 John Smith
102 Dec 22, 2011 Jane Doe

tblInvoiceDetails
Autonumber PartNumber Quantity
101 coffee 5
101 cream 1
101 sugar 2
102 coffee 2
102 scone 1
102 tea 3
102 sugar 1
Thanks in advance for all the help!.

Anwsers to the Problem Append 2 tables in one to many relationship

Download SmartPCFixer to Fix It (Free)

I'd start by importing the spreadsheet into a single non-normalized Master table, which you can then use to decompose the data into normalized tables. This can be done with a few append queries. The trick here is to first append the distinct rows into
the referenced tables, Customers, and Products in your case. Then append rows into the referencing tables with further append queries which must be executed in the correct sequence:

1. Append rows into the Invoices table by joining the Customers and master Tables on the Customer text columns (all customers must have distinct names of course). Append the CustomerID from Customers and the InvoiceDate from Master. By using the SELECT DISTINCT
option only one row per customer/invoice date will be inserted (this does assume no more than one invoice per customer per date of course).

2. You can now append rows into InvoiceDetails by joining Invoices, Products and Master and inserting the ProductID from Products, The InvoiceID from Invoices and the Quantity from Master.

As it happens you'll find an example of this sort of thing as DecomposerDemo.zip in my public databases folder at:
https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The demo uses a simple contact list where each contact can have multiple employees, and takes you through the various stages one by one. It can be run as many times as you wish by clicking the rest button on the final form. Although the type of data differs
from yours the principles involved are much the same.

Cleaning your computer, components, and peripherals help keep everything in good working condition and helps prevent germs from spreading. You can't image how dirty the inside of your computer case can get. All the dust and dirt is going to prevent proper air flow and may even prevent the fan from working.

Recommended Method to Fix the Problem: Append 2 tables in one to many relationship:

How to Fix Append 2 tables in one to many relationship with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer. Run it, and it will scan your computer. The junk files will be shown in the list.