Quantity on Hand

[Hakob_Grigoryan]

I am a little bit new to access. I have a table where I am registering data about goods purchased. Now I want to make another table related with it where the data about output of those goods will be registered. For example: received 10 tires via particular invoice, out 5 on 20/11/13, and 4 on 21/11/13. Afterwards I want to see the remainder of each item. Can anyone please assist on this issue?

 

[MarkK]

Hello, welcome to the forum

How many you have is always a function of how many you received, minus how many you shipped . . .

OnHand = Received - Shipped

. . . so it's common practice to always calculate this amount from other sources rather than store it somewhere.

 

[Hakob_Grigoryan]

Re: Hello, welcome to the forum

How many you have is always a function of how many you received, minus how many you shipped . . .

OnHand = Received - Shipped

. . . so it's common practice to always calculate this amount from other sources rather than store it somewhere.

That's clear. But for that I must Sum the shipped qty in the second table than deduct it from Received qty from the first table.

1st table
Products IN Qty
tires - 10 psc

2nd table
Out Qty date
2 psc 14/11/2013
7 Psc 15/11/2013

How to sum in 2nd table than deduct it from 1st one. To see current qty on hand.

 

[MarkK]

There are a few ways, you can open two recordsets, one using SQL like this . . .

SELECT Sum(Quantity) As Shipped
FROM tblShipping
WHERE ProductID = 1234
AND ShipDate < #somedate#

. . . and the other needs to sum items received, for the same product, for the same date, and then you do the subtraction.

You can use DSum(). Twice.

You can write a query with two sub-queries.

You have a few options. Is there one of those that seems like you could figure out how to do it?

 

[Hakob_Grigoryan]

Thanks for your feedback.
I don't figure out anything about SQL. I would like to try option with Dsum().

 

[MarkK]

So, find a technical reference, like VBA help, search for DSum, and familiarize yourself with how to use the function. It shows you what parameters are required to make it work, and then post back if you need more help.

 

[Hakob_Grigoryan]

So, find a technical reference, like VBA help, search for DSum, and familiarize yourself with how to use the function. It shows you what parameters are required to make it work, and then post back if you need more help.

thanks. I will try

 

[Hakob_Grigoryan]

I made it without Dsum. Just made a query based on those 2 tables, summed the [out qty] in design view of the query and deducted it from [in qty]. Now I can see the qty on hand of only those records that had [qty out], but those that were received but did not output yet I can't see. I am attaching the sample database for you to understand better what I am talking about. If you have time please have a look .

 

[jdraw]

Hakob,
Glad you have it working. As lagbolt said, quantity on hand is usually a calculation
QtyOnHand = QtyReceived - QtyShipped

Here is a link to more information re Inventory
http://allenbrowne.com/appinventory.html

 

[MarkK]

Your "out" table doesn't seem to specify the product. How do you know what you shipped?

I would expect to see these tables . . .

tProduct
ProductID
Name
UnitPrice (subject to change over time)

tOrder
OrderID
CustomerID (SupplierID for purchase orders)
OrderDate

tOrderDetail
OrderDetailID
OrderID
Quantity (how many you shipped/received . . .
ProductID . . . of this product)
UnitPrice (fixed at time of order)

You need to clearly link quantities and products, so with these tables, to calculate what you shipped you sum the Quantity of a specific product, on or before a certain date, something like . . .

SELECT SUM(tod.Quantity)
FROM tOrderDetail As tod
    INNER JOIN tOrder As to On tOrderDetail.OrderID = tOrder.OrderID
WHERE tod.ProductID = 1234
AND to.OrderDate <= #someDate#

 

[jdraw]

Hakob,

As lagbolt said the Product, Quantity and AgreedUponPrice in the OrderDetails is critical.
If you don't store the AgreedUponPrice, Quantity and Product this way, you'll have great difficulty when you
--want to provide a clearance price, or loyalty reward price,or fire sale etc.
--want to change the Price of a Product

 

[Hakob_Grigoryan]

Lagolt and jdraw,

I highly appreciate your feedback.
The point is that this database is for storing procurement information for production. Here must be stored info about purchase orders, pending purchases, quotations form suppliers, the purchase, and the out put. We are not going to resell. I have a database (which as you understand I've made very unprofessional, it is a real mess ) that contains all the mentioned info except the quantity on hand info. As I understand I have no other choice than to learn VBA & SQL to make it work properly.

 

[MarkK]

Yes, to do certain jobs it is almost essential to use certain tools. In Access, VBA and SQL are almost essential.
Best of luck,