Getting "Latest" Exam Date on Query

[lrpulliam]

I have a query and I am pulling up the exam dates on some patients. I want
the query to list the "latest" exam date but I'm getting "all" the exam dates.
I haven't been able to figure out how to put the criteria in to where it'll
only pull up each patients lastest exam date.

Can someone help me out with the syntax?

 

[KARL DEWEY]

DMax function will do it for you.

 

[lrpulliam]

Hi Karl,

Thanks for answering. How do you use DMax in a query? I clicked on the
"Totals" and changed the "total" column from "Group by" to "Max" and it
didn't work. I tried to type DMax in the criteria box of the ExamDate column
and that didn't work.

 

[KARL DEWEY]

Set the [exam date] to Max but leave the [patient] as Group By.

 

[Steve]

What is the "total" column you mention here?

In my previous response I stated "Under the ExamDate field, change Group By
to Max."

Steve

 

[lrpulliam via AccessMonster.com]

In the query design grid where it says (Field, Table, Total, Sort, Show,
Criteria, and Or). I put in max under the ExamDate but it doesn't change
the number of records. Everything else in the query is set to group by.

What is the "total" column you mention here?

In my previous response I stated "Under the ExamDate field, change Group By
to Max."

Steve

Hi Karl,

[quoted text clipped - 14 lines]

 

[Steve]

A totals query won't give you the results you want when you have lots of
fields in the query. Create a new query and only include the PatientID and
ExamDate fields. Follow the previous directions and make the new query a
totals query. Leave your existing query as a Select query. Open your
existing query in design view and add the new query to the query window.
Connect PatientID in the new query to PatientID in the existing query. Pull
down MaxExamDate into the query grid. This will be the latest exam date.

Steve

In the query design grid where it says (Field, Table, Total, Sort, Show,
Criteria, and Or). I put in max under the ExamDate but it doesn't change
the number of records. Everything else in the query is set to group by.

What is the "total" column you mention here?

In my previous response I stated "Under the ExamDate field, change Group
By
to Max."

Steve

Hi Karl,

[quoted text clipped - 14 lines]

Can someone help me out with the syntax?

 

[lrpulliam via AccessMonster.com]

Great, I'll give it a shot. Thanks Steve.

A totals query won't give you the results you want when you have lots of
fields in the query. Create a new query and only include the PatientID and
ExamDate fields. Follow the previous directions and make the new query a
totals query. Leave your existing query as a Select query. Open your
existing query in design view and add the new query to the query window.
Connect PatientID in the new query to PatientID in the existing query. Pull
down MaxExamDate into the query grid. This will be the latest exam date.

Steve

In the query design grid where it says (Field, Table, Total, Sort, Show,
Criteria, and Or). I put in max under the ExamDate but it doesn't change

[quoted text clipped - 13 lines]

 

[KARL DEWEY]

You need to do one further thing.
Either Connect ExamDate in the new query to MaxExamDate in the existing
query.
Or
Place [MaxExamDate] as criteria for [ExamDate] field.

--
Build a little, test a little.

Great, I'll give it a shot. Thanks Steve.

A totals query won't give you the results you want when you have lots of
fields in the query. Create a new query and only include the PatientID and
ExamDate fields. Follow the previous directions and make the new query a
totals query. Leave your existing query as a Select query. Open your
existing query in design view and add the new query to the query window.
Connect PatientID in the new query to PatientID in the existing query. Pull
down MaxExamDate into the query grid. This will be the latest exam date.

Steve

In the query design grid where it says (Field, Table, Total, Sort, Show,
Criteria, and Or). I put in max under the ExamDate but it doesn't change

[quoted text clipped - 13 lines]

Can someone help me out with the syntax?

 

[lrpulliam via AccessMonster.com]

OK, thanks Karl, I'll try that as well.

You need to do one further thing.
Either Connect ExamDate in the new query to MaxExamDate in the existing
query.
Or
Place [MaxExamDate] as criteria for [ExamDate] field.

Great, I'll give it a shot. Thanks Steve.

A totals query won't give you the results you want when you have lots of [quoted text clipped - 12 lines]

Can someone help me out with the syntax?