By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Warehouse Database

P: 10
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
Expand|Select|Wrap|Line Numbers
  1. Sum(Qty)
So here's what I did.
Query1
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMain.*, tblMain_1.*, IIf([tblMain.Location]>[tblMain_1.Location],[tblMain_1.Qty],IIf([tblMain.Location]=[tblMain_1.Location],[tblMain.Qty],0)) AS IF
  2. FROM tblMain, tblMain AS tblMain_1;
Query2
Expand|Select|Wrap|Line Numbers
  1. SELECT query1.tblMain.Part, query1.tblMain.Qty, Sum(query1.IF) AS Cumulative
  2. FROM query1
  3. 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. :)
Aug 27 '08 #1
Share this Question
Share on Google+
1 Reply


aas4mis
P: 97
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.
Sep 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.