Import minimal data and create multiple purchase orders

I have an inventory database to that imports data from a barcode scanner and then saves it to a transaction history table. My goal has been to make all data entry as simple as possible. However, I am having difficulty creating purchase orders in a manner that I can record them correctly. As it is now, items are recorded as being ordered on a certain date by a code "O". this is saved in the transaction history with any other transaction data. I need to find a way to take the raw data (productID, date, transaction code, and location code) and match the supplier to the product and put all the products ordered on that date on the same supplier order.
My basic thought is to make a table using the raw data and extract all of the products ordered. This table would pull in the supplier and pricing data. I am not sure how to handle the data after making the table taking into account that supplier A has 6 items for one order, supplier B has 4 items for another order.  I have attached the data tables that I use. The [fromScan] table is where the data from the barcode scanner is temporarly stored. I use a series of queries to evaluate the data and append it to the transaction table.
Any advise on how to proceed is appreciated.
Mike  SampleData.accdb
MikeOsborneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks as if you need to setup a Master/Detail sort of table design if you wish to add multiple items to a single Purchase Order. You'd have a POHeader table, which would contain things like PONum, Description, Date, etc, and then a PODetail table, which would contain details of each line item included in that PO.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.