Query for most recent date-and-time when records have multiple times for 1 date

[Joy Pxley]

Hi,

Totally new to Access and struggling with an insane deadline, so I appreciate any help I can get.

I have a table with records of client interactions. Most clients have multiple records. Each record has a date field and a time field. I need to create a set of queries to find the most recent date-and-time, and then link it to the outcome listed in that record.

I was able to follow directions on the site linked below to construct one query that found the most recent date for each client (that part worked), and a second query that linked that most recent date to the rest of the record and showed the outcome (that part did not work).


http://office.microsoft.com/en-us/ac...010341693.aspx

That's when I realized that I have have multiple records (different times) for the same client on the same day, sometimes on the last day. So the second query returned multiple records for some clients and multiple outcomes not the "final outcome" (last time) that I need.

I've been searching around and can't figure out how to make a new query or combination of queries that finds the last time within the last date within a client.

Any guidance is welcome!

Thanks!
Joy

[pbaldy]

It was probably a mistake to store them separately, but I assume it's too late to change. You can create a query that grabs all the fields from the table plus create a combined field. In design view it would look like:

CombinedDateTime: DateFieldName + TimeFieldName

Then use that as your base rather than the table, and use the combined field. You may find this simpler:

http://www.baldyweb.com/LastValue.htm

[Joy Pxley]

Thanks Paul! I'm not used to the "select" "from" etc. way of talking about queries, but I think I can figure it out. One question though: what does it mean that in the second query there's a "*" after VehicleMiles.?

[pbaldy]

That symbol is a shortcut that means "all" in that context. In other words, instead of listing the fields individually, that will select all fields.