Wednesday, July 6, 2016

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.

2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been removed


Related: error message fsui.exe/ wlidcli.dll is missing
,Solution to Error: Excel: Automatic number generation
,Troubleshoot:i wanna change language of all windows in Windows 7 Home Premium
,Troubleshooting:IF Function, if the vendor ID in column A matches any of the vendor ID's in column F, I want 1099 to appear in column D. Error
,Fast Solution to Problem: ReadyBoost is not working properly in my computer
,Troubleshoot:Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive. Error,External Hard Drive not listed in Windows 7 backup wizard Tech Support,Tech Support: I'm always being signed off so annoying,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.,Referencing data in Access using Excel [Anwsered],Need Best Way To Present Data [Anwsered],Same question but for windows 7 home edition,sometimes fullscreen won't activate [Solved],Solution to Error: We bought a new computer with windows 7 and it is constantly freezing. How do we fix this?,Solution to Error: Windows 8 update crash (2013-07-22),brclr.dll.mu_ Missing Error Fixer,How To Resolve Missing fin_myagtui.eng Problem,Corrupted f_0006ad File - How to Fix,nkebigvg.cpl Not Found Error Fixer,Corrupted olcontrols.ocx File - How to Fix
Read More: How Can I Fix - AM I RUNNING 32 OR 64 VERSION OF WINDOWS VISTA??,Troubleshooting:All shortcuts on my desktop are directed to Internet Explorer Error,APPCRASH problem, Registry class not found [Solved],How to Fix Error - Any additional help to improving 7 Home video playback on my new Netbook??,Tech Support: Any Way to RESTART Win 7 While in Remote Desktop ?,After Windows Update CD/DVD drive does not work,After installing windows 7 to laptop the labels print out of line how can I fix it,After my computer sleeps the wireless always disconnects and will no reconnect,After signing into Pogo.com, I get a message" This tab has been recovered"....what does this mean and how do I fix it.....,All desktop icons changed to windows live and does not open

No comments:

Post a Comment