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.