Sum by part

[Uvuriel03]

I have a table in which information for an inventory is stored. There are many part numbers and different quantities received and exported for each. I have also created a form from which you can view information on each inventory entry, and what I would like to do is have a display on the form of a total sum received minus the total sum exported.

So, for example,
Part #------Quantity in-----Quantity Out (if any)
A-----------5--------------0
A-----------8--------------2
A-----------1--------------0

So if you looked up a part A, it would show you:
A - [Total Quantity Remaining]

And the total quantity remaining would be the sum of the quantities in (5+8+1=14) minus the sum of the quantities out (0+2+0=2) for a total of 14-2=12.

It would ONLY add the parts for the one displayed, though. So if there were Part Bs mixed into the example, it wouldn't add those up.

Any help is muchly appreciated!!

 

[pbaldy]

Try

SELECT PartNum, Sum(QuantityIn) - Sum(QuantityOut) AS Remaining
FROM TableName
GROUP BY PartNum

You could add a criteria to restrict to part A.

 

[Uvuriel03]

Thanks, but I'm not sure where should I put this. I'm still toying around with how I'm actually going to have this number displayed--I was thinking of just having an unbound text box with some kind of formula as the control source.

 

[pbaldy]

That's a query. You could use a DLookup to pull a specific part from it. Or you could use a DSum as the control source of a textbox. When you decide how you want to use it, we can figure out the best way to get it.

 

[Uvuriel03]

I'm thinking the Dsum as a source of a textbox sounds like the route I would like to go with.

 

[neileg]

A general tip for inventory design:

If you store all the inventory events, eg opening balance, goods received, goods issue, stock adjustment, in a single table with the quantity as a +ve figure for increases and a -ve figure for reductions, the a simple sum of the transactions will give you the balance on hand. This is a much simpler approach that using two fields for ins and outs.

 

[pbaldy]

Here's a good reference on syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[Uvuriel03]

Well the reason for the ins and outs is to keep track of the details. We receive shipments in, and we need to keep track of which trucking companies delivered how many loads on which dates.

I'm unfamiliar with the +ve and -ve figures, what could I do with them, exactly?

 

[neileg]

Say you receive 10 items of a particular part and store this as +10. You then issue 6 items and store this as -6. If you sum the transactions in the table for that part you will get 4 which is your stock in hand.