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.
:banghead:
Have you any idea how to handle this in MS access 2010?

Many Thanks in advance guys,

Dimitris

 

[Cronk]

I have an idea about how to handle it in Access.

However I would need to know the data structure first. Maybe you could tell us the field names, particularly how to determine what stock was not there.

 

[tsakdim]

I have an idea about how to handle it in Access.

However I would need to know the data structure first. Maybe you could tell us the field names, particularly how to determine what stock was not there.

Thanks for your reply!

I attach a sample of our report.

Regarding the Out of Stock indication, we use one column that says "Y"(es) for the out of stock and "N"(o) for the available items.

We replace the "Y" with 1 and the "N" with 0 and we want to get the average as percentage with 2 decimal (e.g 33,50%-->0,335).

Thanks again,

Dimitris

 

[Cronk]

I know what you want output. However I do not know the table structure on which the query is to run. Provide the table and field names where the data is stored in Access, or post a copy of the database with either no data or a small amount of data.

 

[tsakdim]

I know what you want output. However I do not know the table structure on which the query is to run. Provide the table and field names where the data is stored in Access, or post a copy of the database with either no data or a small amount of data.

Hi Cronk,

I attach the table.

Thanks,

Dimitris

 

[Geotch]

I handle this with a couple if queries.

Query one, customer with max of visit date (using the group button in a query)

2nd query uses query one and the table. When you join customer to customer and date to max of visit date you will only get the results from the last visit.

Query 3 off of the 2nd query, use the group by button again to get counts of products an. Counts of out of stock to get averages.

I always use multiple queries starting out to step through it and understand it better.

I could throw an example together if needed.

 

[tsakdim]

I handle this with a couple if queries.

Query one, customer with max of visit date (using the group button in a query)

2nd query uses query one and the table. When you join customer to customer and date to max of visit date you will only get the results from the last visit.

Query 3 off of the 2nd query, use the group by button again to get counts of products an. Counts of out of stock to get averages.

I always use multiple queries starting out to step through it and understand it better.

I could throw an example together if needed.

First of all, many thanks for your post.

I tried it but the average of Out of Stock remains the same either I choose count of products or not either I choose max of call date or not.

Could you share with me your querry in case I made something wrong?

Thanks again.

Dimitris

 

[Cronk]

I'm not surprised the averages are the same.

Using the data you supplied, there are 5 companies. On the last date each was visited, the results are where OOS is greater than one
Customer1: 16 products, each with OOS of 1, or 16 in total, average 1
Customer2: 1 products, each with OOS of 1, or 1 in total, average 1
Customer3: 8 products, each with OOS of 1, or 8 in total, average 1
Customer4: 11 products, each with OOS of 1, or 11 in total, average 1
Customer5: 3 products, each with OOS of 1, or 3 in total, average 1

 

[Geotch]

I'm not sure Cronk's results are correct. I came up with 6 customers. 4051 products on the last call date 39 products out of stock and my averages are different. I might be misunderstanding the question.

I've uploaded my database with the queries, final answer in Q2_Last.....

 

[Mihail]

@Geo
Agree. I obtained the same averages.