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
Who is Participating?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.