Page 1 of 3 123 LastLast
Results 1 to 15 of 31

Thread: Minimum Values

  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Minimum Values

    I'm trying to pull a on hand inventory query.

    I have Product # Quantity Date Time

    The trouble is if I just group by minimum value of quantity that on occassion there is a return. So it would pull the 0 quantity since its the min value but there is actually a higher quantity in stock.

    I have tried grouping by Last Date, Last Time, Last Quantity but the three don't seem to pull the right records. Can someone please give me a tip?

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Please post the SQL of your query, some sample data and the desired results.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    Why not make a query to show everything that: Is Not Null and <>0
    Then do your min value query
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  4. #4
    Join Date
    Nov 2008
    Posts
    26
    PHP Code:
    SELECT Roll_Transactions.[Item Number], Roll_Transactions.RollLast(Roll_Transactions.[On-Hand Weight]) AS [LastOfOn-Hand Weight], Max(Roll_Transactions.[Transaction Date]) AS [MaxOfTransaction Date], Last(Roll_Transactions.[Time Stamp]) AS [LastOfTime Stamp]
    FROM Roll_Transactions
    GROUP BY Roll_Transactions
    .[Item Number], Roll_Transactions.Roll

    This query contains every transaction that the product goes through. Receipt, withdraw, returns, etc. I just simply want it to pull the last transaction date, last transaction time (since more than one transaction can take place on the same date) and what that ending balance would be.

  5. #5
    Join Date
    Feb 2004
    Posts
    214

    ...

    I would still query out ann null and zero values first. Then do your min. This is the easiest way I know how to do this.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  6. #6
    Join Date
    Nov 2008
    Posts
    26
    But w/ my experience so far, if I pull out the zero values first, then it brings in the next minimum quantity. I'm trying to get a snapshot of inventory at a current timeframe, so if there is a zero quantity, I don't want that product to show up on my report at all.

    Does that make sense or is there another way to pull out the zeros that I'm not seeing?

  7. #7
    Join Date
    Feb 2004
    Posts
    214

    ..

    I may not be understanding your question fully.
    If you have a product # that has a quantity of zero, then if you make a query that shows quantity<>0, then this will exclude all product #'s that have a quantity of 0.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  8. #8
    Join Date
    Feb 2004
    Posts
    214

    ....

    Post a sample of your table data might help me a bit also.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  9. #9
    Join Date
    Nov 2008
    Posts
    26
    Which would be fine except my DB has all the transactions for that particular product. Therefore if I eliminate the zeros, it will just pull in the previous transaction quantity, making the report show a quantity that isn't actually present.

    For example.

    Product 1 is received w/ 50 quantity

    We use some leaving us w/ a 20 quantity

    We use it again down to 0 quantity


    So if I remove the 0 quantity in the query, then it will show that we still have 20 in stock when we actually don't.

  10. #10
    Join Date
    Feb 2004
    Posts
    214

    ...

    Ok, you can try this.

    Make a query with the min value (including zero's)
    Make a second query pulling in the data from the first query and exclude all <>0's.

    Since you are pulling in only the data from the first query, it will not roll to the next lowest quantity if it had a 0.
    Last edited by mr. blonde; 11-06-08 at 13:15.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  11. #11
    Join Date
    Nov 2008
    Posts
    26
    This still doesn't work b/c of the returns portion. A transaction could be down to 0 but then a return takes place. But if I only pull out the minimums first, it shows that we have 0 in stock instead of the actual total.

    I'm not sure how to actually post sample data on here, so I attached a snapshot of what I'm talking about. Notice the on hand weight and the date.
    Attached Thumbnails Attached Thumbnails db_snapshot.JPG  

  12. #12
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    sample data and the desired results

    Quote Originally Posted by garethdart
    Please post the SQL of your query, some sample data and the desired results.
    Thanks! Have a look now...

    ...Ah - can you give us a screen grab of the Table with the source data please?
    Last edited by garethdart; 11-07-08 at 10:12.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  13. #13
    Join Date
    Feb 2004
    Posts
    214

    ...

    With that query, what do you want it display?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  14. #14
    Join Date
    Nov 2008
    Posts
    26
    I would like it to only show the last transaction.

    On Hand Weight 238
    Transaction Date 10/25/2008
    Time Stamp 9:58


    The last 2 fields (Date and Time) actually wouldn't be necessary for the report I want to put together but I've tried including them here hoping I could pull the data I needed by grouping by Last. To this point that has not worked.

    By the way, thanks for all the help.

  15. #15
    Join Date
    Nov 2008
    Posts
    26
    Quote Originally Posted by garethdart
    Thanks! Have a look now...

    ...Ah - can you give us a screen grab of the Table with the source data please?
    Is this what you are wanting or something else?

    Thanks
    Attached Thumbnails Attached Thumbnails db_snapshot2.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •