Access query question

  Muckle 00:18 13 Jul 2004
Locked

I have a table in a database that contains fields for first name, last name, session start (date & time) and session end (date & time). I need a query that will display just the most recent session start details for each person - each person has multiple entries in the table. It sounds as though it should be straight forward, but I can't get anything to work.
Can anyone help please?

  AccessMoron 13:47 13 Jul 2004

group by first, last names and max of the session start, end date

  Muckle 15:26 13 Jul 2004

I have tried that, but it doesn't seem to work - does it depend on the data being formatted? My tables use data imported from a text file and as such there are no 'field properties' set.

  AccessMoron 09:10 15 Jul 2004

Start and end date MUST be dates or formated YYYYMMDD.

In the query builder, for the field names SessionStart change it to the following.

CDate(SessionStart).

Repeat For session end.

This will only get you the maximum session start, and the mazimum session end, not the session end that goes with the max session start. You would need an extra ID field and a subquery to do this.

  Gingermum 10:50 16 Jul 2004

Your tables are set up incorrectly. You should have one for each person with a one to many relationship to a session table containing start and end sessions.
Set up the relationship in the relationship window and enforece referntial integrity.
Then you can set up a query using both tables.
Under "SessionStart" or whatever you call the field, you can put "Max(SessionStart") into the criteria line.
Until you'get your people out into a table of their own it will not work but you can easily use queries to do this for you.

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

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

Iconic New York graphic designer Milton Glaser on his uplifting new subway posters

New iMac Pro release date, UK price & specs rumours

Comment suivre le parcours du père Noël ?