Warehouse Database

[jjkeeper]

Hi, a colleague asked me to see if it's possible to create a warehouse database for him, so things will go faster and more efficient on his end. (yes, in the midst of my annual database project...) I tried a few things, got stuck, tried to look for information on the web but to no avail.

Anyway, here is the scenario.
3 main fields from tblMain:
Index - P.Key
Part - name of the product
Location - location of the product stored in the warehouse
Qty - quantity of the product available in that location

I need a subtotal of each record instead of the overall total from

Sum(Qty)

So here's what I did.
Query1

SELECT tblMain.*, tblMain_1.*, IIf([tblMain.Location]>[tblMain_1.Location],[tblMain_1.Qty],IIf([tblMain.Location]=[tblMain_1.Location],[tblMain.Qty],0)) AS IF
FROM tblMain, tblMain AS tblMain_1;

Query2

SELECT query1.tblMain.Part, query1.tblMain.Qty, Sum(query1.IF) AS Cumulative
FROM query1
GROUP BY query1.tblMain.Part, query1.tblMain.Qty;

This works fine, if what I want is just to show the cumulative subtotal of every record irregardless of Location and Part. However, I need to differentiate by Part. Note that Cumulative is calculative field.

Example:
Part Location Qty Cumulative
X A1 3 3
X A2 4 7
X A3 10 17
Z B1 11 11 ------ here the Qty for Z does not add up with Qty for X
Z B2 20 31
Z B3 25 56

And here is what I get from what I did:
Part Location Qty Cumulative
X A1 3 3
X A2 4 7
X A3 10 17
Z B1 11 28 ------ here the Qty for Z continue adding up with Qty for X
Z B2 20 48
Z B3 25 73

The usage of this database will be:
1) user set criteria for qty of part to be taken out.
2) database shows user the records where Cumulative < Qty_Criteria
3) then user follows the database's results to proceed to take out the parts.

Disclaimer: The query idea, I got it from another web user. The idea is not mine. I'm just a noob. :)

[aas4mis]

Where did you get tblMain_1 from? A simple query for 1 table for sum of a part would be "SELECT part, sum(qty) FROM tblMain GROUP BY part;". If you want individual locations you need to do a seperate query, the GROUP BY negates individual records.