By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Query most recent records for unique field values

P: 17
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?
May 10 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
For one table the solution would be

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([Date]), Field FROM TableName
  2. 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
May 12 '07 #2

P: 12
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?
Jun 8 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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] & "'")
Jun 8 '07 #4

P: 12
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
Jun 8 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 8 '07 #6

P: 12
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!
Jun 8 '07 #7

P: 2
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
Jun 11 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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] & "'")
Jun 11 '07 #9

P: 2
can you query the date within the report instead? maybe that will work?
Jun 11 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 11 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.