OK, I posted about this in the Reports Forum yesterday. I realized this morning however, that maybe I'm going about this the wrong way.
I work at a manufacturing facility where we cut glass. We buy huge plates of raw, untempered glass and then cut it to whatever shape and size our customer wants. We use an Access Database to manage our orders, scheduling, and billing systems.
Occasionally, with our more active customers, we get what's called an "Open Orders" list. This is simply a list of every order they have with us that's not completed and fully shipped.
I'm trying to put together a Form/Report system in Access that will allow me to show our own version of an Open Orders list. That way, when we get one from the customer, I can just plug in their Purchase Order numbers and get a list showing everything off of those orders that has shipped.
I already have a basic Form/Query that shows shipments off of up to four different Orders at a time. The problem is that it doesn't show items off of those orders that haven't shipped. Nor does it show a "Total" line for items that required multiple shipments. For example:
We have an order with three lines in it.
Line 1 was for 1000 pieces of clear glass.
Line 2 was for 150 pieces of green glass.
Line 3 was for 25 pieces of UV-ray reducing glass.
Let's pretend that we've made the following shipments off of that order:
Line 1 has had 2 shipments; one of 250 pieces and one of 375.
Line 2 was fully shipped in a single shipment of 150.
Line 3 hasn't had anything ship off of it.
My Query (well, the report based off the query actually) would show the following:
What I want it to show is:Code:<Order Number> Line 1: 250 (12.5%) Shipped on <Date> Line 1: 375 (18.75%) Shipped on <Date> Line 2: 150 (100%) Shipped on <Date>
Is it possible to build a single Query to get all that information? And if not, What kind/how many Queries would I need?Code:<Order Number> Line 1: Shipment 1: 250 (12.5%) Shipped on <Date> Shipment 2: 375 (18.75%) Shipped on <Date> Total Shipped: 625 (31.25%), 1375 (68.25%) Remaining Line 2: Shipment 1: 150 (100%) Shipped on <Date> Total Shipped: 150 (100%), 0 (0%) Remaining Line 3: No Shipments, 25 Remaining
The table information is as follows:
SOMaster
-so_num - the internal tracking number
-po_num - the PO number the customer uses
-cust_id - the customer's internal ID number (for billing)
-ship_id - the shipping location's internal ID number
SODetail
-so_num - the internal tracking number of the item
-line_num - the line number of the item
-glass_type - the type of glass wanted (thickness, reflective, etc.)
-height - the height of the glass
-width - the width of the glass
-shape_id - the shape ID number of the glass (specifying radiused corners, angles, etc.)
-ord_qty - quantity ordered
-last_sched_qty - last quantity scheduled on the item
-tot_sched_qty - total quantity scheduled of the item (if we break some and have to schedule more)
-ship_qty - total quantity shipped
-due_date - when the item is needed by the customer
InvoiceMaster
-inv_num - the invoice number
-cust_id - the customer's internal ID number (for billing)
-ship_id - the shipping location's internal ID number
-date - the date of the invoice
InvoiceDetail
-inv_num - invoice number
-inv_line_num - the invoice's line number
-so_num - internal tracking number being invoiced on that line
-po_num - customer's PO being invoiced on that line
-inv_qty - quantity being invoiced
-price - the price of the glass being invoiced
P.S.
Yay for wall of text!