Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 25th, 2003, 05:36 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Check quantity stock level

Hi good morning everyone...

I really need anyone's help. I'm developing a database using Access XP with DAO 3.6. I want to create a form with a combo box consist of productID...so when I select any productID, it would display product name and its quantity stock level within the same form, in which that quantity in stock would be automatically decreased everytime a customer placed an order...regardless of the date they placed an order

tables:
tblProducts("ProductID", "productName", "UnitOfMeasured", "quantityInStock", "SellingPrice")
tblOrder("OrderID", "fkCustomerID", "orderDate", "datePaid")
tblOrderDetails("OrderLineNo", "fkOrderID", "fkProductID", "amountOrdered", "UnitPrice")

the one I created is not gave me such functionality...eg. qtyInStock = 80...when a customer placed an order for productID = 2 ..on 20/08/03...with OrderedAmount = 25...the product quantity was reduced (80-25) to 55....but when other customer placed an order for the same productID on 21/08/2003....with OrderedAmount = 10...the product quantity was reduced from 80 to 70 ...which it supposed to be 45(from 55-10 ..on 20/08/03)....

Here is the query that I created:
SELECT tblProducts.ProductID, tblProducts.ProductName, tblOrders.OrderDate, tblOrderDetails.AmountOrdered, [tblProducts.QuantityInStock]-[tblOrderDetails.AmountOrdered] AS [Quantity in Hand]
FROM tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderID = tblOrderDetails.fkOrderID) ON tblProducts.ProductID = tblOrderDetails.fkProductID
GROUP BY tblProducts.ProductID, tblProducts.ProductName, tblOrders.OrderDate, tblOrderDetails.AmountOrdered, [tblProducts.QuantityInStock]-[tblOrderDetails.AmountOrdered];

I'm new to VBA...and I'm still learning about it...so can anyone help me with the query or VBA coding to automatically updated the quantity in stock

Many many many thanks

Cheers,

Fehrer
Reply With Quote
  #2 (permalink)  
Old August 25th, 2003, 10:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Fehrer,

Here's a little Quantity-on-Hand project I've put together before and it works well. May give you some ideas:

http://users.bigpond.net.au/abrowne1/AppInventory.html

HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old August 25th, 2003, 07:41 PM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Bob Bedell
 Hi Fehrer,

Here's a little Quantity-on-Hand project I've put together before and it works well. May give you some ideas:

http://users.bigpond.net.au/abrowne1/AppInventory.html

HTH,

Bob

Hi Bob,

Thanks for ur help...it seems like the function that I wanted...
the user would be able to update the products' quantity when there is an incoming delivery...and stocks quantity would also automatically decreased when a customer placed an order...regardless of the order date

However, it seems a bit complicated...as I have to modify my tables structure...and it may lead to duplicate values....and denormalisation
I just want to know...how do I use DSum() expression to the table of acquisitions and table tblOrderDetails (in my case)...do u mean to the query or table? And as I saw from the sample North Wind database...Is it fine to have a table that only connected to one table but not connected to other table?

I'll give it a try ur quantity on Hand project...and I'll let u know about the progress...

Once again...thanks Bob

Cheers

Fehrer
Reply With Quote
  #4 (permalink)  
Old August 25th, 2003, 10:29 PM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Bob Bedell
 Hi Fehrer,

Here's a little Quantity-on-Hand project I've put together before and it works well. May give you some ideas:

http://users.bigpond.net.au/abrowne1/AppInventory.html

HTH,

Bob

Hi Bob,

I have tried and tested your coding...It works very well...Thanks very much...
but it seems that I have to manually calculated the quantity on hand on tblStocktakes...and enter the value to the table tblStocktakes...and supposed if i have over 300 products...it can be tiredsome

And If I want to check the stock quantity before the stocktakeDate on the stocktake tables...it always return the quantity value entered at the StocktakeDate field...eg...productID = 7...qtyInStock(before any Purchase) = 50...stocktakeDate = 25/08/2003...customers have purchase twice on 15/08/2003 for OrderAmount=10...and on 18/08/2003 for orderAmount = 10....I set the stocktakeDate on 20/08/2003 with the quantity=30 (50-10-10)...
When I enter ?OnHand (7, 17-08-2003) at the Immediate window....it always return quantity=30...which it supposed to be 40 (50-10)...because customer only purchase once on 15/08/2003
any other way to get around this?

Here is the copy of my table structure:
http://www24.brinkster.com/kimho76/tablesRel.gif (u have to copy paste to new windows exp to see it)

and this is my coding...modified from yours:
Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
    'Purpose: Return the quantity-on-hand for a product.
    'Arguments: vProductID = the product to report on.
    ' vAsOfDate = the date at which quantity is to be calculated.
    ' If missing, all transactions are included.
    'Return: Quantity on hand. Zero on error.
    Dim db As DAO.Database 'CurrentDb()
    Dim rs As DAO.Recordset 'Various recordsets.
    Dim lngProduct As Long 'vProductID as a long.
    Dim strAsOf As String 'vAsOfDate as a string.
    Dim strSTDateLast As String 'Last Stock Take Date as a string.
    Dim strDateClause As String 'Date clause to use in SQL statement.
    Dim strSQL As String 'SQL statement.
    Dim lngQtyLast As Long 'Quantity at last stocktake.
    Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
    Dim lngQtyPurchased As Long 'Quantity used since stocktake.

    If Not IsNull(vProductID) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProductID
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
        End If

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
        End If
        strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _
            "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY StockTakeDate DESC;"

        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Quantity, 0)
            End If
        End With
        rs.Close

        'Build the Date clause
        If Len(strSTDateLast) > 0 Then
            If Len(strAsOf) > 0 Then
                strDateClause = " Between " & strSTDateLast & " And " & strAsOf
            Else
                strDateClause = " >= " & strSTDateLast
            End If
        Else
            If Len(strAsOf) > 0 Then
                strDateClause = " <= " & strAsOf
            Else
                strDateClause = vbNullString
            End If
        End If

        'Get the quantity acquired since then.
        strSQL = "SELECT Sum(tblAcquisitionDetails.Quantity) AS QuantityAcq " & _
            "FROM tblAcquisition INNER JOIN tblAcquisitionDetails ON tblAcquisition.AcqID = tblAcquisitionDetails.fkAcqID " & _
            "WHERE ((tblAcquisitionDetails.ProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblAcquisition.AcqDate " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!QuantityAcq, 0)
        End If
        rs.Close

        'Get the quantity used since then.
        strSQL = "SELECT Sum(tblOrderDetails.AmountOrdered) AS QuantityPurchased " & _
            "FROM tblOrders INNER JOIN tblOrderDetails ON " & _
            "tblOrders.OrderID = tblOrderDetails.fkOrderID " & _
            "WHERE ((tblOrderDetails.fkProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblOrders.OrderDate " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyPurchased = Nz(rs!QuantityPurchased, 0)
        End If
        rs.Close

        'Assign the return value
        OnHand = lngQtyLast + lngQtyAcq - lngQtyPurchased
    End If

    Set rs = Nothing
    Set db = Nothing
    Exit Function
End Function

Actually...I have done similar function like this using TRIGGER on oracle sql...this is the coding:


SQL> -- Trigger for checking quantity in stock
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER product_qty_stock_check
2 AFTER INSERT OR UPDATE on asg_orderline
3 for each row
4
5 DECLARE
6 qty_in_hand NUMBER (4);
7 qty_aft_sales NUMBER (4);
8
9 BEGIN
10 SELECT qty_in_stock INTO qty_in_hand
11 FROM asg_product
12 WHERE id = :new.product_id;
13
14 qty_aft_sales := qty_in_hand - :new.qty_ordered;
15
16 if qty_aft_sales < 0 then
17 raise_application_error(-20003,'Cannot make an order because there is not enough stock');
18 else
19 UPDATE asg_product SET qty_in_stock = qty_aft_sales
20 WHERE id = :new.product_id;
21 end if;
22 END;
23 /

Trigger created.

SQL> select * from asg_product where id = 30002;

                         Sunshine Shoes Distributor
                        ===========================
                                                                        
SALES DEPARTMENT Sales Rep Responsibility
                                                                        
        ID PRODUCT_NAME PRICE QTY_IN_STOCK
---------- ------------------------------ ---------- ------------
     30002 ************************ Junior orange 150 1000
                                                                        


SQL> INSERT INTO asg_orderline (order_line_no, product_id, product_name, qty_ordered, price, order_id)
2 VALUES (&order_line_no, &product_id, '&product_name', &qty_ordered, &price, '&order_id');
Enter value for order_line_no: 1
Enter value for product_id: 30002
Enter value for product_name: ************************ Junior orange
Enter value for qty_ordered: 23
Enter value for price: 150
Enter value for order_id: 0019
old 2: VALUES (&order_line_no, &product_id, '&product_name', &qty_ordered, &price, '&order_id')
new 2: VALUES (1, 30002, '************************ Junior orange', 23, 150, '0019')

1 row created.

SQL> select * from asg_product where id = 30002;

                         Sunshine Shoes Distributor
                        ===========================
                                                                        
SALES DEPARTMENT Sales Rep Responsibility
                                                                        
        ID PRODUCT_NAME PRICE QTY_IN_STOCK
---------- ------------------------------ ---------- ------------
     30002 ************************ Junior orange 150 977

So, is it possible to create a function like this with Access XP and VBA?
in which the stock quantity would be automatically reduced...wihout having to manually enter the stock quantity at the stocktake table...
and the stock quantity itself would return the updated quantity ...regardless of the stocktakeDate I set on the stocktake table....and How? sorry if it is too long Bob...I hope...my explanation is clear enough...Anyway...Thanks once again Bob...you've been a great help for me :)

Or is there anyone can help?

Thank you in advanced

Cheers

Fehrer
Reply With Quote
  #5 (permalink)  
Old August 26th, 2003, 10:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Fehrer,

First, thank Allen Brown for the function. That was his web site you visited.

Second, the stock take process is an unavoidably manual process. It entails going out into the store and physically counting the current quantity on hand as of a specific date and manually adjusting you inventory. These values will have to be entered manually in the stock take table to get an accurate count of items on the shelves as of the stock take date. If you need to calculate quantity on hand prior to a stock take date, you can derive that number from your original aquisition quantity less you sold quantity less your real quantity following the stock take. That'll give you the difference between what you should have on hand and what you really have on hand.

Third, as far as calculating a Total Ordered, Total Acquired, and Total On Hand fields goes, here is a query that might help you:

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.ProductID)
    INNER JOIN tblProducts
    ON zSel_AcqByProduct.ProductID = tblProducts.ProductID;

You'll notice that qSel_QtyOnHand contains two embedded queries. There SQL is:

zSel_AcqByProduct

SELECT
    tblAcquisitionDetails.ProductID,
    Sum(tblAcquisitionDetails.Quantity) AS TotalAcqQty
FROM tblAcquisitionDetails
GROUP BY tblAcquisitionDetails.ProductID;

zSel_OrdByProduct

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

You can also do these calculations using VBA as follows:

Private Sub cboSelect_AfterUpdate()
    Dim strCriteria As String
    Dim intAcquired As Integer
    Dim intDisposed As Integer
    Dim intOnHand As Integer

    strCriteria = "[ProductID] = '" & cboSelect & "'"

    intAcquired = Nz(DSum("tblAcquisitionLine!
       Quantity", "tblAcquisitionLine", strCriteria), 0)
    intDisposed = Nz(DSum("tblOrderLine!Quantity", "tblOrderLine",
       strCriteria), 0)

    If intDisposed > 0 Then
        intOnHand = intAcquired - intDisposed
    Else
        intOnHand = intAcquired
    End If

    txtAcquired = intAcquired
    txtDisposed = intDisposed
    txtOnHand = intOnHand

End Sub

Finally, I had a rather extensive discussion regarding Inventory Control a while back with John Ruff on the ms_access Yahoo form. John's insights are invaluable. You can view that discussion at: http://groups.yahoo.com/group/ms_access/. Just type 'Quantity On Hand' in the 'Search Archive' text box. John helped me out immensely. In fact he helped me put together the queries posted above.

Regards,

Bob

Reply With Quote
  #6 (permalink)  
Old August 26th, 2003, 01:31 PM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default



Hi Bob,

Ohh, my God!!...after reading ur extensive discussion with John Ruff...I really really really appreciate ur effort in solving my problems...I don't know the best way on how to thank you for this...
I hope God would always bless on every way u do in this life....:)
U really have a leader nature behaviour...

However, as ur advise seems a bit more complicated than before...in which I have to modify my table structure again...eg adding zSel_AcqByProduct table and zSel_OrdByProduct table....
As u can see from the url...http://www24.brinkster.com/kimho76/tablesRel.gif ...my table structure has already been complicated with 9 tables....
It would take me some time on testing ur sql queries...or is there any way to adjust those queries as above based on my tables structure...sorry for this....

Anyway, I'll try to test ur advice and let u know about the progress....

Million thanks to u Bob...

Cheers,

Fehrer
Reply With Quote
  #7 (permalink)  
Old August 26th, 2003, 03:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Fehrer,

Wow! Thanks for the kind words. Much appreciated.

You wrote:

Quote:
quote:However, as ur advise seems a bit more complicated than before...in which I have to modify my table structure again...eg adding zSel_AcqByProduct table and zSel_OrdByProduct table....
Please note that zSel_AcqByProduct and zSel_OrdByProduct are select queries, not new tables. zSel_AcgByProduct selects from tblAcquisitionDetails, and zSel_OrderByProduct selects from tblOrderDetails. qSel_QtyOnHand is then a select query that uses zSel_AcgByProduct and zSel_OrdByProduct as subqueries.

tblAcquisitionDetails and tblOrderDetails are part of Allen Brown's original table schema, so if you are working with that schema or something remotely like it (I unfortunately couldn't gain remote access to the url you posted, error message) you shouldn't have too much trouble adapting the queries. I may have altered table/field names a bit, but that would be all.

Finally, this topic was touched on a bit on the databaseadvisors.com forum not too long ago also. Here is a post from that discussion that provides additional schema information for implementing inventory control in a warehouse setting. You might find it interesting:

Quote:
quote:
Anyone know of a sample inventory/warehouse database or a good article about
constructing such a database? Nothing fancy - just some basics and
reminders.

Thanks in advance,

Bill
Quote:
quote:
  Don't have anything to send, but I'll explain the design I've used over
the years. Has always proved quite flexible. BTW, 'PK' is primary key,
'CK' Candidate key, and 'FK' foreign key. The design below uses the
surrogate key approach for the most part.

tblInv - Inventory master - One record per stocking item
InvID - PK
Description
StockUOM - Stocking unit of measure
PurUOM - Purchase unit of measure
ConvFactor - (PurUOMQty * factor) = StockUOM Qty
Weight
Volume
ShelfLife
ClassCode - Used to group inventory items
ABCCode - A, B, or C item - Used for cycle counting, inventory analysis,
etc.


that would be the basics. Costing can be a separate table or done here.
Depends on what your using this for; if MFG then you'll want a separate
table. If something simple, then a cost field in the inventory master may
do it for you.


If your doing MRP, then you would add things like:

PlanningTimeFence
DemandTimeFence
DockToStockLeadTime
FixedLeadTime
VariableLeadTime
EOQ - (Economic Order Qty)
IssuePanSize
etc.

tblWarehouses - One Record per warehouse
WarehouseID - PK
Description
Address1
Address2
City
State
Zip
Telephone
PrimaryContact (Might want to break contacts out into a seperate table)

tblLocations - One record per location per warehouse
LocID - PK - Autonumber
WarehouseID - CK1A
LocCode - CK1B - User friendly code for a location (ie. Aisle/Rack Number)
Type - Stock or WIP
Nettable - yes/no

tblLots - One record per location per part. If doing full lot tracking,
then a new record gets created for every transaction. If not, once a lot
record exists for a part/location combination, it can be used over and over.
LotID - PK - Autonumber
LocID - FK
InvID - FK
DateCreated
OrigQty
CurQty
ShelfLife
Cost


tblInvTrans - One record per part per transaction - Records all the in's and
out's (movement of inventory) of parts.
TranID - PK - Autonumber
TranDate - Date/Time of transaction
EntryDate - System Date/Time
InvID - FK
TranType - 'I'n or 'O'ut
DocType - Balance Forward, Order, Cust Ship, Receipt, Vendor Return, Cycle
Count, etc.
DocRef - Order #, PO#, etc that this transaction was driven by.
ReasonCode - Reason for trans (i.e. Normal, Key punch error, Paperwork
error, etc).
Qty - note may be negative or positive to allow for reversals of
transactions.
LotID
ProgID - Identifier that indicates program that generated this transaction.


  If the physical inventory table did not exist (tblWarehouses, tblLocations
, and tblLots) then you would also use this to generate QOH. The sum of all
transactions (based on Trantype and qty) would be quantity on hand. When
purging, the sum of deleted records is brought forward with a Balance
Forward transaction.

  DocType can be anything you want to identify the type of transactions that
occur. DocRef is simply a reference, not an actual linking field.

and last if your doing MRP:

tblInvMRP - One record per part per commit/replenishment
TranID - PK - Autonumber
InvID - FK
TranType - 'C'ommitment or 'R'eplenishment
TranDate - Date/Time Commitment/Replenishment will occur
DocType - Order, PO, or MFG Order.
DocRef - Order #, PO#, etc that this transaction was driven by.
ProgID - Identifier that indicates program that generated this transaction.
WarehouseID
Qty


  There is quite a bit here. Look it over and let me know if you have any
questions.


Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman@earthlink.net

Best success with your application.

Regards,

Bob


Reply With Quote
  #8 (permalink)  
Old August 27th, 2003, 03:52 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Hi Bob,

I have tested ur queries...qSel_QtyOnHand queries looks alright and logical with the Immediate If....in which On Hand Qty was derived from [TotalAcqQty]-[TotalOrdQty]...

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.ProductID)
    INNER JOIN tblProducts
    ON zSel_AcqByProduct.ProductID = tblProducts.ProductID;

Morever, I modified zSel_AcqByProduct as the TotalAcqQty was not only derived from the quantity on the tblAcquisitionDetails...but also have to be added with QuantityInStock on the tblProducts (the original quantity possessed before any delivery and purchases)...so the queries looks like this:

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

this queries was also fine and works well....

However....there is a problem with zSel_OrdByProduct...in which TotalOrdQty was the sum of all AmountOrdered regardless of the product ID...thus...TotalOrdQty was always the same...and, On Hand Qty field = (TotalAcqQty - TotalOrdQty) on qSel_QtyOnHand queries shows negative value...the value of TotalOrdQty is very large...bigger than TotalAcqQty as it was the sum of all AmountOrdered regardless of productID

eg. customerID=001 placed an order of ProductID=2 for AmountOrdered=5....customerID=002 placed an order of ProductID=2 for AmountOrdered=5...customerID=003 placed an order of productID=3 for AmountOrdered=10...customerID=004 placed an order of productID=4 for AmountOrdered=10

In this case...supposed the totalAcqQty = 20 for productID=2...then
On Hand Qty for productID=2 was -10(20-5-5-10-10)...which is supposed to be only 10(20-5-5)...

zSel_OrdByProduct
SELECT [tblOrderDetails].[ProductID] AS [Product ID], Sum(tblOrderDetails.AmountOrdered) AS TotalOrdQty
FROM tblOrderDetails
GROUP BY [tblOrderDetails].[ProductID];

I have tried to put WHERE clause for this queries to point that Sum(tblOrderDetails.AmountOrdered) is only for specific productID asked on the parameter...unfortunately I can't work it out :(
Do u have ideas Bob?
Or is there anyone can help me please?

Anyway...u can't access the url for my table structure http://www24.brinkster.com/kimho76/tablesRel.gif by just clicking from this window...or right click then open on the new window...somehow brinkster prohibit that...u have to open new window for ur browser (Ctrl N)...then copy paste the url to the address bar...

Many thanks

Cheers,

Fehrer
Reply With Quote
  #9 (permalink)  
Old August 28th, 2003, 09:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Fehrer,

I took a look at your table structure and its essentially identical to mine (with the exception of your tblPseudoID).

The query I posted works fine for me against this schema. The numbers in my result set are accurate. Check your field/table identifiers and be sure everthing maps correclty and is spelled correctly. Here's a partial result set of Northwind.mdb data:

"ProductID","ProductName","Total Acquisitions","Total Orders","On Hand Qty"
"2435","Apple Peeler/C/S Stand",336.00,5.00,331.00
"2430","Apple Peeler/Corer/Slicer",1168.00,4.00,1164.00
"2425","Apple Wedger",482.00,1.00,481.00
"1485","Baker's Rollerpin",470.00,0.00,470.00

I use the query behind an inventory viewing form with the following 2 event handlers:

Private Sub cboSelect_AfterUpdate()
On Error GoTo cboSelect_AfterUpdate_ERR

    Dim strSQL As String
    Dim strCriteria As String

    strCriteria = "ProductID = '" & cboSelect & "'"

    strSQL = "SELECT * " & _
                "FROM qSel_QtyOnHand " & _
                "WHERE " & strCriteria

    fsubInventory.Form.RecordSource = strSQL
    fsubInventory.Requery

cboSelect_AfterUpdate_EXIT:
    Exit Sub

cboSelect_AfterUpdate_ERR:
    MsgBox Err.Description
    Resume cboSelect_AfterUpdate_EXIT

End Sub

Private Sub cmdViewAll_Click()
On Error GoTo Err_cmdViewAll_Click

    Dim strSQL As String

    cboSelect = ""

    strSQL = "SELECT * " & _
                "FROM qSel_QtyOnHand " & _
                "ORDER BY ProductName;"

    fsubInventory.Form.RecordSource = strSQL
    fsubInventory.Requery

Exit_cmdViewAll_Click:
    Exit Sub

Err_cmdViewAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewAll_Click

End Sub


Regards,

Bob



Reply With Quote
  #10 (permalink)  
Old August 29th, 2003, 07:59 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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 the results

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



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
stock management- closing stock and opening stock bright_mulenga Access 1 July 16th, 2007 07:11 AM
Quantity Error rsm42 ASP.NET 1.0 and 1.1 Basics 1 December 31st, 2006 01:25 PM
Shopping Cart quantity update problem j2 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 August 21st, 2006 02:39 PM
Explicit Variable Check at IIS Level pvasudevan Classic ASP Basics 1 April 26th, 2004 05:07 AM



All times are GMT -4. The time now is 05:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.