Results 1 to 4 of 4
  1. #1
    BamaBBQ1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    4

    Warehouse bin location not shown

    I need a small programming job performed in Access? And by small, I mean, very small. I've written a short Query involving some inventory and I have a problem with one feature.
    Any recommendations? I'm willing to pay for your time, but again, its not a big job. For someone who knows Access, I'm guessing 1 hour or less.

    Thanks


    Russ
    russ.elrod@ac-an.com

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Provide complete description of the issue. You can even attach db. Follow instructions at bottom of my post.

    Maybe someone will enjoy the challenge and solve for free.

    I removed duplicate thread.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    BamaBBQ1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    4

    Post File Attached

    Thanks so much!! db attached. When you run the query, notice it returns three columns: "Product", "SumOfPallets", and "MinOfFreshness_Date". I need it to list two additional columns: "Row", and "Cell".

    For example, where is the product located in our warehouse with the Freshness_Date of 3/7/2011 ? By our warehouse layout, we place items in "rows" and "cells", much like a life size spreadsheet.

    The problem I've had with Access is, any attempt I make to insert the Row and Cell listings causes my summary list to blow up into an itemized list. Useful, but not what I need.

    I'm sure the fix is simple, but above my pay grade! :-)

    Thanks again,
    Russ
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There is no reason to include the Row and Cell tables in queries using the Inventory_Transactions. The Row/Cell data is in the record. The Row and Cell tables have no other information.

    Also, can't include the Row and Cell data in that query because it is an aggregate query. Row and Cell values are linked to detail records, not the aggregate calcs.

    As example, there are 19 transactions for product: Black 1 Lt - 56000000; but those transactions have a variety of Row/Cell combinations. You want the Row/Cell associated with the latest Freshness date for each product. That is tricky because the criteria for selecting the Row/Cell is dependent on data in another record of the same table.

    Here is one way to accomplish.

    Remove the Row and Cell and ItemTypes tables from the aggregate query. Join that query to the Inventory_Transactions table on the Product fields. Criteria under the Transaction_Date field: =[MinOfFreshness_Date]

    SELECT Product_Totals.*, Inventory_Transactions.Row, Inventory_Transactions.Cell
    FROM Inventory_Transactions INNER JOIN Product_Totals ON Inventory_Transactions.Product = Product_Totals.Product
    WHERE (((Inventory_Transactions.Freshness_Date)=[MinOfFreshness_Date]));

    The result will be 6 records because apparently 2 products have 2 transactions with same freshness date. Perhaps need to exclude the records with negative Pallets. So criteria under Pallets: >0
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Warehouse and Inventory Template
    By miguel.escobar in forum Access
    Replies: 0
    Last Post: 06-12-2012, 12:41 PM
  2. Calculating Current Stock in Inventory/Warehouse
    By yohansetiawan in forum Access
    Replies: 5
    Last Post: 03-14-2012, 09:05 PM
  3. Warehouse location adjustment
    By tj63069 in forum Database Design
    Replies: 5
    Last Post: 01-11-2012, 06:19 PM
  4. Simple calculations to be shown in reports
    By Newaccessuser in forum Access
    Replies: 8
    Last Post: 03-03-2011, 06:58 AM
  5. Inventory/Warehouse Control
    By Maker in forum Access
    Replies: 3
    Last Post: 09-01-2010, 10:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums