Complex Query Help

[Degs29]

Well, complex for me, as I've mostly learned Access through trial and error. Thanks to pbaldy and rzw0wr for starting me on the right path with my tables and forms. So basically I have the following four forms (and their respective tables):



ProductForm - Lists all finished products and their required raw materials and raw material consumption rates (one main record per product)

Form:

ProductNumber
ProductName
DefaultOrder - typical customer order quantity

Subform:

RawMaterial - a list of all raw materials used in the creation of the above Product
Consumption - the respective consumption rate of each raw material (ie. Product A may use 2 of Raw Material A and 0.5 of Raw Material B, etc.).

ReceivingForm - Lists all raw materials received (one main record per packing slip)

From:

Supplier
PurchaseOrderNumber
DateReceived

Subform:

RawMaterial
Quantity

ShippingForm - Lists the daily shipments (one main record per day)

Form:

Date

Subform:

ProductNumber
Quantity

OrderForm - Lists items on order (I haven't created this form yet, but plan to shortly)

Form:

RawMaterial
Quantity


So what I need is to calculate the following for each item of raw material:

Net = Receiving.Sub.Quantity - (Shipping.Sub.Quantity₁ * Product.Sub.Consumption₁ + Shipping.Sub.Quantity₂ * Product.Sub.Consumption₂ + ... + Shipping.Sub.Quantity_n * Product.Sub.Consumption_n) + Order.Quantity

Or, if that didn't make sense, I want to know how much of each raw material I have on hand and on order at any given time based on shipped quantities of finished product. So the amount of Raw Material A received; subtract the amount of any finished product that uses that raw material multiplied by its respective consumption rate; add any amount of Raw Material on order. Then the same thing for each kind of raw material.

I need a query to compute this so I can display this information on a report. Frankly, I'm not sure where to start. Thanks for any and all help!

[Dal Jeanis]

Since we're talking about a query, we're talking about the structure of the underlying tables. What you've shown us is forms.

Thus, I'd ask for the structure of the tables, and ask you to pick "a place to stand" regarding the query. In other words, is this a query against the entire database, to determine overall ordering, or a query based on what's needed for a particular workorder?

[Degs29]

Okay, I have seven tables, corresponding to each form and subform listed in the original post:

ProductMain:

ProductNumber
ProductName
DefaultOrder

ProductSub:

RawMaterial
Consumption

ReceivingMain:

Supplier
PurchaseOrderNumber
DateReceived

ReceivingSub:

RawMaterial
Quantity

ShippingMain:

Date

ShippingSub:

ProductNumber
Quantity

OrderTable:

RawMaterial
Quantity


I hope that's what you needed. As for the query, it would apply to our entire inventory, not just a specific work order. Thanks for looking into this.

[Dal Jeanis]

Hmmm. Okay, so...

1) First, I'll assume that RawMaterial is a foreign Key to a table you haven't listed, that's used as a common key between all the tables. Otherwise, the inevitable mis-spellings will ruin your ability to link up your data.

2) I'll assume that the ProductSub table has a foreign Key to ProductNumber,the ReceivingSub table has a foreign key to PurchaseOrdernumber, and the ShippingSub table has a foreign key to something on ShippingMain.

3) And I'll assume, because it isn't listed, that you don't have a periodic inventory table (say, end of year) that sets the amounts as of a given date. Which means you'll occasionally have to enter a "Fake" shipping order to deal with spoilage and/or loss of raw materials, or a fake receiving order to account for the sudden appearance of an extra box of widgets. This design seems to be often suggested on Access forums, because it means you are not storing "redundant" data. Like how many boxes of widgets you can actually find, rather than the number that the database says you should be able to find.

4) Also, I don't see any table for finished goods that haven't been shipped, so I'll assume you're running a JIT/back-order-only shop.
　
So, here's the total units of all raw materials that you have already shipped:

SELECT ProductMain.ProductNumber, Sum(ShippingSub.Quantity * ProductSub.Consumption) As RawUsed 
FROM ProductMain, ProductSub, ShippingSub
WHERE ProductMain.ProductNumber = ProductSub.ProductNumber AND
ProductMain.ProductNumber = ShippingSub.ProductNumber
GROUP BY ProductMain.ProductNumber

　
Here's the total units of all raw materials you have already received:

SELECT RawMaterial, Sum(Quantity) As RawReceived
FROM ReceivingSub
GROUP BY RawMaterial

Joining those two is fickle, because you don't want to accidentally lose a material that you've received but you haven't shipped, or vice versa. I'd tend to use the RawMaterialList table (my assumption #1) in a left-join with each of them, or something along those lines.

If this discussion got you what you needed, then please mark the thread solved. If not, please post another specific question.

[Degs29]

That's some great assuming you did there. Every one of those assumptions are correct.

That second bit of code works great, but I'm having trouble with the first bit. So far what it does is mash together the different bits of raw material into one lump sum. So if a product uses 2 units of RM (Raw Material) A and 1 unit of RM B, then it adds them together so that the product uses 3 units of a generalized raw material. Perhaps this is where the left-join comes into play, but I'm not even sure what that is or how to do that. Keep in mind I'm a newbie (I didn't even know you could make queries in SQL until now...). I'm going to experiment on my end and see what I can do, but I'd appreciate any insight you have!

[Degs29]

Okay, I've altered your code and it seems to be giving me the results I wanted:

SELECT ProductSub.RawMaterial AS RawMaterial, Sum(ShippingSub.Quantity * ProductSub.Consumption) As RawUsed
FROM ProductMain, ProductSub, ShippingSub
WHERE ProductMain.ProductNumber = ProductSub.ProductNumber AND ProductMain.ProductNumber = ShippingSub.ProductNumber
GROUP BY ProductSub.RawMaterial

I've only done some preliminary testing of this, but so far so good. Can you see any faults with it?


EDIT: I can now make a simple query where I subtract RawUsed from RawReceived (and also RawOnOrder when I've done that part). Looks like clear sailing to me. If everything checks out in a couple days I'll mark this thread as solved. Thanks for your help!

[Degs29]

Joining those two is fickle, because you don't want to accidentally lose a material that you've received but you haven't shipped, or vice versa. I'd tend to use the RawMaterialList table (my assumption #1) in a left-join with each of them, or something along those lines.

Left join is working for RawReceived, but I can't figure out how to get it working for RawUsed. It's having difficulty with there being both a WHERE clause and a LEFT JOIN clause.

EDIT: I've made two queries as a temporary/permanent workaround (one with the WHERE clause and one with the LEFT JOIN clause).

[Dal Jeanis]

Yeah, I've been around this merry-go-round a few times. I find that, by explicitly stating my assumptions, I can give the other person feedback on what they did and didn't tell me, avoid confusing the other person with inapplicable advice, and at the same time nail down everything I think is important about the business case. (...which usually keeps me from saying anything TOO stupid...)

The left join is only if you want to make sure that you get an output record for every material, even the ones that never had a record for used or received. If you code SQL using one explicit JOIN, you need to do them all as explicit joins. That would look more or less like this:

 
SELECT ProductSub.RawMaterial AS RawMaterial, 
       SUM ( NZ(ShippingSub.Quantity * ProductSub.Consumption),0)) As RawUsed
FROM 
(ProductMain LEFT JOIN 
   (SELECT ProductSub.ProductNumber, 
           ProductSub.RawMaterial, 
           ProductSub.Consumption, 
           ShippingSub.Quantity, 
    FROM ProductSub INNER JOIN ShippingSub 
    ON ProductSub.ProductNumber = ShippingSub.ProductNumber) 
ON ProductMain.ProductNumber = ProductSub.ProductNumber)
GROUP BY ProductSub.RawMaterial

then any product that had never been shipped will come back in the list, but with a zero instead of the calculation. I've masked the null as a zero using the NZ function -

Sum ( NZ(ShippingSub.Quantity * ProductSub.Consumption),0)) As RawUsed

It was more efficient to code that INNER JOIN into a full SELECT clause that returned only the fields that you wanted to see. You could even make it a query in its own right, if you wanted.

But, then again, if the version you've already coded is working, you should just understand the technique described above and move along. There's no sense engaging in serial rewrites while you're on the steep part of the learning curve.

[Degs29]

After some extensive testing, everything is checking out. I'm thrilled with what I'm able to do with it as it stands. I undoubtedly will be tweaking it throughout the year, but for now it's good. Not a moment too soon (our year end is today and we'll be taking a physical inventory count on the weekend, making it the perfect time to implement this system). Thanks again for your input!

[Dal Jeanis]

...and there was much rejoicing...