Calculating Stock On Hand

[namu23]

Hi,

I know this is a very common question but it seems that I am still lost and i think my database is little different from other designs. I was thinking that my tables are fine until i cannot move any further and i get stuck. I hope someone can help me build my query to calculate the stock on hand. Thank you.



Here, i would like to show the relationship of the two tables.





Should you have other suggestions or the best way to setup my tables and query, i would be very thankful.

[orange]

http://allenbrowne.com/appinventory.html

[namu23]

Hi Orange,

Thanks for sending the reference. Sorry but I forgot to say that I do not know how to code in vba and SQL. I just need to build the correct Query to calculate the stock on hand. Hope you can help me. Thank you.

[orange]

From the link I gave you:

"How do I update the Quantity On Hand each time an item is sold/used?" The simple answer is: You don't! You calculate the value when you need it.

The calculation is very simple: the total number acquired, less the number disposed of. Just use DSum() on the table of acquisitions to get the number acquired for any product. Another DSum() expression on the table of uses/invoices gets the number disposed of."

What have you tried? What result?

[namu23]

Hi Orange,

Yup, that is what i want to achieve because i don't want to update the quantity on hand nor store them somewhere. I just want to calculate the stock on hand every time i run the query. I did not try the Dsum though because i dont know how to do it.

Kindly check my screenshot below for the query i have tried.



The result is this which is not correct.



as you will notice on my first post, the sum of quantity received for PODETAILS_ID 8 has to be 250 only.

[orange]

Please post a copy of your database.

[namu23]

Kindly check the attachment please.

Thank you.

MyDB.zip

[orange]

I could not follow your relationships diagram. For your own benefit you should put in descriptions for your tables and fields. It would be less confusing.
I rearranged your query as

SELECT tb_Receiving.PODetails_ID, Sum(Nz([tb_Receiving].[QuantityReceived],0)) AS QtyReceived
, Sum(Nz([tb_issuances].[QuantityIssue],0)) AS QtyIssued
, Sum(Nz([QuantityReceived],0)-Nz([QuantityIssue],0)) AS [Stock on Hand]
FROM tb_Receiving LEFT JOIN tb_Issuances ON
 tb_Receiving.QuantityReceived = tb_Issuances.QuantityIssue
GROUP BY tb_Receiving.PODetails_ID;

And got this result

PODetails_ID	QtyReceived	QtyIssued	Stock on Hand
8	250	100	150
9	250	25	225
10	150	100	50
11	250	30	220
12	100	100	0

Are you only receiving and issuing 1 Product/Item??

[namu23]

Hi Orange,

Sorry for my late reply. I copy paste the SQL but when i run the query, it is not calculating and i'm getting the same result every time.

The relation between the tb_Receiving and tb_Issuance is they are connected through tb_Receiving.ID and tb_Issuances.Receiving_ID.

My explanation is as follow:

> As you noticed the screenshot in my first post, there are two 8 on the tb_Receiving.PODetails_ID. It means first delivery is not complete so i received the item for the 2nd time on different date.
> Also, in my first post, there are two 2 tb_Issuance.Receiving_ID because i issue the item on 2 different dates.

And yes, i am only receiving and issuing 1 item at a time but with different quantity.

Thank you.

[orange]

As I said earlier I can not understand your relationship diagram.
When you post, you usually describe the issue, your environment and your expertise/familiarity with database and Access.
You posted some images with minimal description.

In post 5 you said the 350 quantity was an error --- that was a flag to me saying there was something wrong with your query. It's your data and your query, so if you can't make sense of it, how can someone unfamiliar with your issue and your environment.

I see in your receiving table that you have 2 receipts of PODetailsID = 8, but I also see no mention of Product or Item, so I don't know what you are receiving. However, I do see the total of PODetailsID = 8 received as 250.

I tried playing with your numbers to make some sense of the data. I passed on a best guess.

This sort of comment

It means first delivery is not complete so i received the item for the 2nd time on different date.

No body would guess that -- you received quantities of something of different dates. If you get a partial delivery, you should have a means to identify partial delivery.
Same thing with Invoices and Payments if that's part of your business.
However, if you want to describe your receiving and issuance tables in plain English, and exactly WHAT you are trying to do, I'll take a look.

But a few images and no details and no descriptions in plain English will not get the readers to respond.

Is every receipt and issuance for the same thing/item?

[namu23]

Hi Orange,

Thank you for highlighting me about the "means to identify the partial delivery and that you cannot see the Product Item" (I was thinking like, yeah how can you calculate something if you don't know the products you want to calculate?) In that case, i modified the table relationship of tb_Receiving and tb_Issuances. What i did is i connected them to the tb_PurchaseOrderDetails.ID.



and then i made a separate query for the receiving, issuance, stock



and viola! i think i just solved my problem




Thanks a lot. I really appreciate your help!

[orange]

Glad you have it sorted out. Good luck with your project.

Remember, readers only know what you tell them, so treat us like people in a line at McDonalds --
pretend we don't know you, nor Access , nor database. If we said "What's the big issue today?" -- tell us
in simple plain English the old what, why, where, when, how and how much from the 30000 foot overview.

You may find links here of value.