Complex Query/Queries for a Report

[Rawb]

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:

<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>

What I want it to show is:

<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

Is it possible to build a single Query to get all that information? And if not, What kind/how many Queries would I need?

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!

[NassauBob]

My, now that is helpful information! If everyone could provide even half of the insight into their database file as you have, things would be much easier to see!

As far as the combined shipping quantities go, have you looked into using subtotals in the report? You can set up header and footer section for whatever grouping levels you create (in this case Order Number), and put the subtotal figures in the footer, such as SUM(ship_qty), etc. And once you have those numbers right, you can start to add formatting flair, such as "were shipped so far..." Does that make sense?

I think your query is providing enough information, maybe your report just needs to be massaged a little to show it the way you want. ;-)

[Rawb]

My main problem, however is that I don't get any data on lines that haven't shipped yet. It's not that they show up with "0" shipped, they don't show up at all.

Here's my query:

SELECT SoMaster.po_num, SoMaster.so_num, SoDetail.line_num, SoDetail.glass_type, SoDetail.height, SoDetail.width, SoDetail.ord_qty, InvoiceDetail.inv_qty, [inv_qty]/[ord_qty] AS [Percent Shipped], InvoiceDetail.inv_num, InvoiceMaster.ship_date, InvoiceDetail.due_date
FROM (InvoiceDetail INNER JOIN SoMaster ON InvoiceDetail.so_num = SoMaster.so_num) INNER JOIN SoDetail ON (SoMaster.so_num = SoDetail.so_num) AND (InvoiceDetail.so_line_num = SoDetail.line_num) AND (InvoiceDetail.so_num = SoDetail.so_num)
WHERE (((SoMaster.po_num)="12345"))
ORDER BY SoMaster.so_num, SoDetail.line_num;

Is there a way I could change the query so that it includes lines with no shipments? Or to change my report so that it looks at two Queries?

P.S.
Sorry about the Query, I went a little overboard with referential integrity on the tables

P.P.S.
At this point it's basically just a mental exercise. I finally got tired of having to do everything in Access and set up a web server that connects to the database through ODBC. Now any time I want a custom report, I just build it using PHP

[NassauBob]

Could you possibly set the default value of ship_qty to zero, so it will have a numeric value? Or maybe add a left_to_ship column, with the number of items still remaining unshipped. Just adding that value to a select query as a condition will allow those with zero shipped items to appear.