Huge report with visits per customer-Grab the information of the last visit

[tsakdim]

Hi Experts,




we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.


I want to create a querry that exports a table with 3 columns:


1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit


I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.

I cannot find a solution.


Have you any idea how to handle this in MS access 2010?


Many Thanks ,


Dimitris

[June7]

This requires criteria based on value in another record of same table. Need nested subquery or domain aggregate functions (DLookup, DMax, etc)..

Review: http://allenbrowne.com/subquery-01.html#TopN

Try the TOP N example to return the most recent record for each customer and use that query as basis for a report with the Avg calc in report footer.

[Dal Jeanis]

The trivial method is to create a temp table with the date of last visit for each customer, and then query the out of stock for each record on the temp table.

You almost reached the more advanced method. If you have a query that gets the date of last visit for each customer, then that query should be able to be joined to the other table to determine out of stock condition on that date.

If you post the formats of the tables, and the query that's almost working, then we can help you figure it out.