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
Expand|Select|Wrap|Line Numbers
- Sum(Qty)
Query1
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- SELECT query1.tblMain.Part, query1.tblMain.Qty, Sum(query1.IF) AS Cumulative
- FROM query1
- GROUP BY query1.tblMain.Part, query1.tblMain.Qty;
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. :)