Query to find average cost over time

[JCross]

Hi All! I have a complex question - I'll do my best to make it clear.

I have a table of invoices, stored by date. The invoices contain item numbers, and item costs. I need a query to look at tblInventory for the last date an Inventory was entered - then go to tblInvoice and find all Invoices between today, and the last date an Inventory was entered.

With these invoices, I need the Item Number on my form found (txtItemNumber), and the price averaged. So I end up with an average price for my Item from all Invoices between today and the last Inventory.

I'm not even sure if this is possible - too complex? Please help! And let me know if I need to explain further.

Thank you!

Jennifer

 

[thouston]

I think I understand your question.....if this doesn't make sense I probably didn't...

First make a query that simply groups your tblInventory table and get the maximum value of date (grouped by whatever else you may be interested in - I'm not sure what else is in that table: eg inventory type?). Alternatively set up a parameter to restrict the data. Either way you will end up with a date that forms the lower end of the period of interest.

Save this query and then use it in a new query with tblInventory (in this case you don't need to join the the table and query by date although if you've also grouped by & carried through other variables these will need to be linked to appropriate tables as necessary).

Make another grouping query, this time using a "where" criterion and make your date value >= [MaxOfdate] (the name of the maximum date field saved in your first query). You shouldn't need to specifiy a < criterion unless you have dates stored that are beyond today's date - if so just add And<Date()

You can then either group by any of the other criteria you saved in the first query or restrict the data according to those values (using another "where" clause or a parameter), and finally group by Item Number and take the Average of Price.

Hope this makes sense....if not let me know your relevant field names & I'll make up a small example.

 

[JCross]

That sounds like it's going to work! For some reason I just couldn't get the logic straight in my head. Thank you so much - I'll let you know how it turns out.


Jennifer

I've been worrying FOREVER about this query and you did it. It works and it took 10 minutes and I am forever grateful.