Query most recent records for unique field values

[rousseaud]

Hello-

I'm having a little trouble. I want to create a query that will return the most recent records (by date) for all unique values in a certain field in the query. I'll be pulling data from 3 different tables as well. Since my description is usually inadequate, here's a simple example:

Before Query:

Date Field
1/1/07 A
1/2/07 A
1/2/07 B
1/2/07 C
1/3/07 A
1/4/07 B

After Query:

Date Field
1/3/07 A
1/4/07 B
1/2/07 C

Does that make sense? Is there a simple solution?

[MMcCarthy]

For one table the solution would be

SELECT Max([Date]), Field FROM TableName
GROUP BY Field

As for joining three table I would need more information. If you can post the actual meta data of the tables as well as details of what you want returned we will try to help.

Here is an example of how to post table MetaData :
Table Name=tblBookings

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[jconstan]

hey there,

i was searching for solutions to my problem and came across this discussion. this is the EXACT problem i am having.

I want to pull the most recent date from the "status subform" pulls the project "name" from its main form.

I tried using Max(Date) to get the most recent date, however it only pulls "1/4/07 B" which is the latest of ALL the dates, not just he "B" dates. (from above illustration)

i tried matching it to the main table as well but that isn't working either :S
Any suggestions?

[MMcCarthy]

hey there,

i was searching for solutions to my problem and came across this discussion. this is the EXACT problem i am having.

I want to pull the most recent date from the "status subform" pulls the project "name" from its main form.

I tried using Max(Date) to get the most recent date, however it only pulls "1/4/07 B" which is the latest of ALL the dates, not just he "B" dates. (from above illustration)

i tried matching it to the main table as well but that isn't working either :S
Any suggestions?

Try using DMax.

=DMax("[Date]","TableName","[name]='" & [Forms]![MainFormName]![name] & "'")

[jconstan]

Try using DMax.

=DMax("[Date]","TableName","[name]='" & [Forms]![MainFormName]![name] & "'")

hey sorry to be a bother (pure newb and self taught one at that) i used DMax but am getting the msg "enter parameter value"

here are the tables, forms and fields im dealing with

Main form = Status>>>Table = Project Management...Relevant Field = Project Name
Sub form = Status Updates Subform>>>Table = Status Updates Sub...Relevent Field = "Date"

Thanks again for the help

[MMcCarthy]

I have to go out for a couple of hours. I'll look at this when I get back if someone hasn't already posted on it.

[jconstan]

I have to go out for a couple of hours. I'll look at this when I get back if someone hasn't already posted on it.

alright not a problem, thank you!

[Leanne123]

hey jconstan

i think i am having the same problem

i currently have a sub datasheet in one of my queries, which feeds into a report.

i am wondering how to filter the sub datasheet to show the latest update, which is what i think you are doing as well.

i think if you use a sub datasheet as i did it will save some problems, such as being able to bring up all the updates for a specific record...although then you get my problem which is i can not filter the sub datasheet :S

let me know how its goin

[MMcCarthy]

hey sorry to be a bother (pure newb and self taught one at that) i used DMax but am getting the msg "enter parameter value"

here are the tables, forms and fields im dealing with

Main form = Status>>>Table = Project Management...Relevant Field = Project Name
Sub form = Status Updates Subform>>>Table = Status Updates Sub...Relevent Field = "Date"

Thanks again for the help

My apologies, this got lost in the shuffle.

OK, try this (the "Status Updates Sub" table has to have a field [name] which corresponds to the same project name in the main form)


=DMax("[Date]","Status Updates Sub","[name]='" & [Forms]![Status]![Project Name] & "'")

[Leanne123]

can you query the date within the report instead? maybe that will work?

[MMcCarthy]

hey there thanx for the response,

i tried using the code, however it is telling me to enter the parameter value: forms!Status!Project Name!...the form name and field are both correct so i dont know what the problem is :S

As this post is from Leanne, I'm confused?

Why is there an exclamation mark after ProjectName?

If the field ProjectName has a space then it needs to be enclosed in square brackets as follows:

Forms!Status![Project Name]

Edit: As the last post has been edited this doesn't make sense to the thread any more. Please don't play around like this or the experts will get very annoyed and not be inclined to assist you.