I am creating a bill of lading in Access as a report. It pulls in sales data from an outside program and stores it in two tables: a sales order table and a sales order line item table in an one-to-many relationship where one sales order can correspond to many line items.
So for imaginary sales order #3000, we pull the line items and fill in the lot numbers manually as parameters.
Line item Shipped quantity Lot # 152 250 2007,2009 162 200 2060
My problem is that I would like to be able to further split up the line items, i.e. so that one line item could correspond to several production lots, each with a certain quantity of product. For the example below, I would like to specify the number of lot numbers (2) for line item 152. I would first enter how many lot numbers that line item has and then enter the shipped quantity and lot number for each.
Line item Shipped quantity Lot # 152 125 2007 152 125 2009 162 200 2060
So I am looking to design the report and related queries/tables to take into account three parameters:
1. how many lot numbers each sales order line item will use
2. shipped quantity per lot
3. actual lot numbers
The sales order data is pulled at the report level and the line item data is within a sub-report.
Would this require VBA or is there a way to do it with purely with queries and user-entered parameters?
Thanks in advance and sorry if I am not describing the problem well.