MS Access whiz needed

  Epsomfillet 21:37 21 Jul 2004

I need to create a report (well, a query first)which looks at ONLY THE MOST RECENT ENTRY in a particular field. The field shows multiple dates of contacts with clients and I am trying to generate a report which shows when we last made contact with any individual client from a whole list of clients (if u get my meaning??. Any ideas would be great, thanks.

  AccessMoron 22:33 21 Jul 2004

If the field is a date then order by the field DESC, and select the top 1

  Epsomfillet 16:50 22 Jul 2004

Thanks for your help - but I think what u r suggesting is that I sort the dates and pick the most recent. I want to know how to make Access do this Automatically (I want to enter something into the querie that would make this happen). To give some background: each client has had several visits (say) and I am only interested in generating a report which shows the most recent visit (for each client).

  Gingermum 13:53 23 Jul 2004

From your description, you should have 2 tables, one for clients and one for recording contacts. This represents a one to many relationship. Set up the relationship in the relationship window and enforece referntial integrity. Then you can set up a query using both tables.

Click the totals button on the toolbar to display the totals line. Leave "Group by" under your contact's name field and any other fields from the contacts table. Under "SessStartDate" or whatever you call the field, click on the crop down list and select "Max".

  AccessMoron 11:49 24 Jul 2004

what you need is a query that groups by contact and the max of visit days. How ever gingermum is correct, to have a correct db you will need a table to hold the contact details and another linked by a primary key that records the vist details. This would enable you to create a report that shows the contact details and a sub report that would show the last vist just by sorting the visit history query DESC and selecting the TOP 1


SELECT TOP 1 VisitDate FROM myTable ORDER BY VisitDate DESC

  Epsomfillet 16:24 25 Jul 2004

Thanks to both of you - I think i get it now!.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?