Finding only the most recent visit with a customer

[JudgeYugiDad]

This seems simple but I am hitting a brick wall. I have a customer
visit table that lists each visit I make to a customer. I want to be
able to run some reports that will show the last time (not each time) I
visited each customer. My original thought was to create a crosstab
query to show the Max date in the total field and then run a second
query to use that date to update the "last visit" field in my customer
master table. When I created the second table and set it to update the
"last visit" filed from the total field in the crosstab query, I got an
error message stating the update must be from an updatable query.

Any suggestions on a better way to do this. I have also tried to hide
the details in the reports and while that gets me only one line per
customer, the date is not the most recent one.

 

[jlepack]

I haven't mastered subqueries yet (but someone will give you a solution
like that I'm sure) so I use two queries.

The first query will have two fields, the first being MaxofVisitDate
grouped by CustomerID, both pulled from your visit table.

The second query will have both of those two fields in query1 linked to
the corresponding fields in your visit table. Then choose the fields
that you want output.

Cheers,
Jason Lepack

 

[jlepack]

qry_last_visit:
SELECT tbl_visit.customerID, Max(tbl_visit.visitDate) AS MaxOfvisitDate
FROM tbl_visit GROUP BY tbl_visit.customerID;

qry_last_visit_details:
SELECT tbl_visit.* FROM qry_last_visit INNER JOIN tbl_visit ON
(qry_last_visit.MaxOfvisitDate = tbl_visit.visitDate) AND
(qry_last_visit.customerID = tbl_visit.customerID);

Cheers,
Jason Lepack