Selecting most recent entry in a table

[mjwillyone]

Hello,



I am creating a report in Microsoft Access 2007.

I have two tables (with the following fields

Table 1: Name
Fields:
projid (text)
firstname
lastname


Table 2: Dates
Fields:
id
projid (text) - linked to the projid field from the table above
annualspringattendance (text)

The Name database holds the names of our employees and their id number. The Dates database holds a listing of the meeting attendance of all of our employees. I am attempting to create a report that will show each employee and in large print the most recent meeting attended, then in much smaller print, the next 2 meetings.

I am not sure how this is done since this must look at the projid, then look at the entries made and select the entry for a given projid that is last in the list (the most current) and work backwards to find a few others (if there are any.)

It must also have something shown if there are no records for a given projid.

Thank you SO much!
Mike

[ranman256]

join the 2 tables. bring down name and meeting date. (which I dont see. do you have the date?)
turn on totals, set max on meeting date. MAX(meetingDate)

make another query using the one above to pull proj ID for that person and that date.

[mjwillyone]

Thank you for the insight. The date is actually in the "annualspringattendance" field. This is a text field, not a date field... not sure if this makes a difference.

The fact that the field that holds the date is not actually a date field means that it can't necessarily be sorted...

Mike

[ranman256]

yes, dates are not texts. you should have a DATE field for dates.

[Missinglinq]

...This is a text field, not a date field... not sure if this makes a difference...

It not only means you can't sort on it...you can't do any meaningful manipulation with it...including determining 'the most recent meeting attended!'

Go to Table Design and change the it to a DateTime Field. If the data is entered in a correct date format, you won't lose any data, despite the warning the Access Gnomes will give you when you do this! As always, it's a good idea to make a copy of the Table before making a major change like this!

Linq ;0)>