Check quantity stock level

Fehrer · August 29th, 2003, 08:02 AM

Quote:

quote:Originally posted by Fehrer

Hi Bob,

Thank you for ur passion in helping and assisting me...very much appreciated.

I have checked two times the table relationships, in which all are 1 to many relationships and its spell check to the queries...nothing is wrong with that...

As you can see from my table structure at the url (http://www24.brinkster.com/kimho76/tablesRel.gif), here is my tables identifier...
tblCustomers
- CustomersID (PK)

tblOrders
- OrderID(PK)
- fkCustomerID(FK)

tblOrderDetails
- OrderLineNo (PK)
- fkOrderID(FK)

tblProducts
- ProductID(PK)

tblAcquisitionDetails
- AcqDetailID(PK)

tblAcquisition
- AcqID(PK)

tblStocktake
- StocktakeID(PK)

tblSuppliers
- SupplierID(PK)

These are the queries adapted from yours...

qSel_QtyOnHand

SELECT zSel_AcqByProduct.ProductID, tblProducts.ProductName, zSel_AcqByProduct.TotalAcqQty AS [Total Acquisitions], IIf(IsNull([TotalOrdQty]),0,[TotalOrdQty]) AS [Total Orders], IIf(IsNull([TotalOrdQty]),[TotalAcqQty],[TotalAcqQty]-[TotalOrdQty]) AS [On Hand Qty]
FROM (zSel_AcqByProduct LEFT JOIN zSel_OrdByProduct ON zSel_AcqByProduct.ProductID=zSel_OrdByProduct.Prod uctID) INNER JOIN tblProducts ON zSel_AcqByProduct.ProductID=tblProducts.ProductID;

zSel_AcqByProduct

SELECT tblAcquisitionDetails.ProductID, Sum(tblAcquisitionDetails.Quantity+tblProducts.Qua ntityInStock) AS TotalAcqQty
FROM tblAcquisitionDetails INNER JOIN tblProducts ON tblAcquisitionDetails.ProductID=tblProducts.Produc tID
GROUP BY tblAcquisitionDetails.ProductID;

zSel_OrdByProduct

SELECT tblOrderDetails.ProductID, Sum(tblOrderDetails.AmountOrdered) AS TotalOrdQty
FROM tblOrderDetails
GROUP BY tblOrderDetails.ProductID;

And this is the result from qSel_QtyOnHand query
"ProductID","ProductName","Total Acquisitions","Total Orders","On Hand Qty"
1, "Unicorn - 6x4 Ltr", 80, 252, -172
7, "Unicorn - 20x1 Ltr", 90, 252, -162
33, "Unicorn - 209 Ltr", 80, 252, -172

The only different from yours is the datatype of ProductID field...I use number datatype, while yours is text datatype....and i think it won't affect the results...
the one that keeps me busy is zSel_OrdByProduct query...because Sum(tblOrderDetails.AmountOrdered) AS TotalOrdQty field would add all the AmountOrdered...I have tried to use subqueries, HAVING clause etc ...but didn't make any difference

Also when I execute that zSel_OrdByProduct query...it would ask me to enter the parameter value of tblOrderDetails.ProductID...in which there was no square bracket ([]) as the criteria on the queries....I think I'll leave the VBA coding to the last once the queries can produce accurate results...

What do u think Bob about my case Bob?
Do you use Sum(tblOrderDetails.AmountOrdered) AS TotalOrdQty field on your zSel_OrdByProduct query?
Anyone has experienced the same problem with me?

I'm desperately needs some helps here.
Many thanks

Cheers,

Fehrer

Bob Bedell · August 29th, 2003, 09:27 AM

Hi Fehrer,

Here's what I do when I get jammed up. I break things down to simpler and simpler prototypes till I get something that runs and then incrementally build back up again. Seems like you've got the cart before the horse. I'd recommend first building Allen Brown's sample schema together with the three queries I posted to get something that runs. Then build from there once the concepts become clearer.

Your queries are not the queries I posted. You've made extensive modifications to zSel_AcqProduct, and frankly I don't understand the revisisons. For example:

Sum(tblAcquisitionDetails.Quantity+tblProducts.Qua ntityInStock) AS TotalAcqQty

Why are you adding tblProducts.QuantityInStock to the acquisition quantity here? You shouldn't even have a QuantityInStock field in your products table. The whole point of this exercise is that QuantityInStock is a calculated field, not a stored value. QuantityInStock should only ever exist as a derived value in a query result set, never a stored value in a base table. And even if it is stored, and you add it to your acquired quantity, your acquired quantity will be inflated.

Why don't you shoot me an e-mail address and I'll send you an application that has all these parts working together. You can then use that as a model and build from there. :)

Regards,

Bob

Fehrer · August 30th, 2003, 04:19 AM

Hi Bob,

How such a nice person u are...:)
yeah....actually i have built all other functions required for my application and leave this inventory control to the last...i think u're right...i have to rebuild them again...here is my email address:
[email protected]

Once again many thanks Bob, I wish i could do something in return 4 u...

Cheers,

Fehrer

Bob Bedell · August 30th, 2003, 06:55 AM

Hi Fehrer,

db in the mail. Have fun with it! :)

Regards,

Bob

Bob Bedell · August 30th, 2003, 07:12 AM

Hi Fehrer,

Just wanted to mention that I apparently never finished rptInvoice in the copy of the db I sent, so it doesn't run.

What the code behind rptInventory is doing is flagging Products that need to be reordered (quantity on hand is less than 200).

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me!txtQuantityOnHand <= 200 Then
        lblReorder.Visible = True
    Else
        lblReorder.Visible = False
    End If
End Sub

That's all.

Bob

Fehrer · September 1st, 2003, 01:42 AM

Hi Bob,

Thanks a lot...I got the db already...such a simple and brilliant inventory control...It's all working fine and very well now....

All the best in everything for you..

Cheers,

Fehrer

Bob Bedell · September 1st, 2003, 09:48 AM

Hi Fehrer,

Glad to here its working for you. Its been a pleasure.

Best,

Bob

jemacc · March 29th, 2004, 06:11 PM

Can you share the db with me. I have followed all the links and notes. I am interested in this database and how I learn a few this from its design. Thanks