FIFO inventory related query - Help!

[BamaBBQ1]

I am new to Access (...currently on pg 235 of Access 2010 for Dummies). I created an Access 2010 file to help manage our warehouse inventory on a FIFO basis. Surprisingly, my queries work! However, I have one field I am struggling with.



Since I am new to Access (...and this Forum), I wasn't sure how to upload my queries or tables to this thread. So for the time being I have attached three .pdf files.

The first attachment "Product_Tables (example report).pdf" is a sample results set I manually typed up in Excel to show the format I need.

The second attachment "Product_Totals.pdf" shows the actual query results I was able to achieve. Close but no cigar. I need the query to show the location where this product is stored in our warehouse. For example, if the minimum freshness date is 3/7/2011, where is that product located in the warehouse?

The third attachment "Product_Totals (with locations)" is the actual query results when I added the location field to my query. It completely destroys the sort order of the products and "minimum" freshness dates.
The good news is, I can derive the information I need from this query, but it's not as compact as I would like.

I figure this can be easily resolved with an expression of some sort, but haven't been able to crack it yet.

Thanks in advance for your help. I'm super-pumped about Access and looking forward to using this forum (...hopefully to help others soon!).

Thanks,
Russ

[ketbdnetbp]

BamaBBQ1 -

If I understand it correctly, in the end, you want the query to sort by product first, and then by date, earliest date first, and display the storage location. Is that correct or is it something else?

Secondly, in the product field, you show several bits of info (Number - Identifier, etc.). In the underlying products table, is this one field or is it several that you combined later in a query?

Lastly, it would be helpful if you posted the db (minus confidential info) or the SQL for the query that destroyed the sort order.

Thanks,

[BamaBBQ1]

ketdbnetbp,

Thank you, I will answer these in order:

You asked: "If I understand it correctly, in the end, you want the query to sort by product first, and then by date, earliest date first, and display the storage location. Is that correct or is it something else?"

Answer: Yes, that is correct, however you will notice in my attached file "Product_Totals (example report).pdf" I also show the Sum of Pallets listed. Also, for example, there may be several pallets of the Product "56000001" with many different freshness dates, however there will only be ONE minimum date. THAT'S the one I want to see. (...our customer requires us ship the earliest (minimum) date out next.)

You asked: Secondly, in the product field, you show several bits of info (Number - Identifier, etc.). In the underlying products table, is this one field or is it several that you combined later in a query?

Answer: This is one field, not a combination of multiple fields. For example, products are described as "56000001 - Blue 1 Lt".

Finally, I tried to attached the entire db, but it's 688 kb, slight over this sites 500 kb limit. If you email me I'll be happy to forward it to you, russ.elrod@agentnational.com .

Thank you so much for your time on this!! Let me know if anything else is needed.

Russ

[NTC]

I believe that part of the issue here is that in a single query - one can not always (or at least not easily) have both the detail, and the sum (min, max, count, etc).

and so a report (or in some cases a form) is sometimes a better vehicle to bring together the detail list from the report's record source (query) with summary data information either summed on the fly in group footer - or calculated outside the report in a separate query and inserted as a sub report. just a thought.

hope this helps a little.