Problem with filtering in query table

[Dougbum]

I've been generating a report using two forms for data, and everything has worked well until recently.
My report contains a field which tracks Therapist visits by date. Since the subform now contains multiple dates for each patient, the report prints a line for each date, resulting in two or three lines for each patient.
I would like the report to print a single line for each paient with the most recent date only. I've tried a fix suggested by another user in the Forum...(Top Value) in the query, but this resulted in only a single record being printed for the entire database. I then tried to add MAX(date) to the CRITERIA line for the DATE field, but now I get the message
"Cannot have aggregate function in WHERE clause". I'm stumped at this point, so any help would be greatly appreciated.

Doug

 

[MStef]

Send a short example of your MDB. (tables, forms, report), ACCESS 2000 or 2002.

 

[Dougbum]

Thanks for your response...

The MDB is in Access 2003, and I would prefer not to post the database as it contains "Patient-sensitive" information.

If my description of the problem was not clear, I would be glad to answer any specific questions you might have.

Again, thanks for your help,

Doug

 

[MStef]

Hello Doug!

I have improvised something.
Look at "DemoMostRecentDateA2000.mdb" (attachment).
Open MainForm and try. I think it is what you need.
Adapt it in your mdb.

 

[Dennisk]

normally I would do this as two queries.

First query would be a total query on your patient referral table, group by PatientID and max of referral Date.

Use this query as the recordsource for query 2 along with the original referral table linking on patientid and referral date.

 

[Dougbum]

My thanks to MStef and Dennisk

Got your responses, I'll give them a try and see if something works.
Best wishes for the Holiday season...

Doug

 

[Dougbum]

Here is my MDB without patient info...

I looked at the sample MDB from MStef, and it works the way I would like mine to work. I'm not sure how you got the 'tblWorking" dates to come up as the most current dates from the "Detail" table.

The "Detail" table would be my "Download" table, so how do I build a "Working" table from there? I apologize for my confusion, but the QUERY table for the Report was an ACTION QUERY and I kept getting a caution message when I tried to open it. I've never worked with an action query before so I'm sure I'm doing something wrong.

Again, any help will be GREATLY appreciated!!!

Doug

 

[Rabbie]

I think you didnt attach your DB. You should use a select query for a report not an action query.

 

[Dougbum]

Sorry about that!

The zipped filesize is too large to post...
Rabbie - I'm using a select query for my report, the action query was from a sample DB that MStef had posted.

I'll have to experiment with Dennisk's suggestion and hope I can figure this thing out! As I said in a prior post, MStef's sample DB does work, but I'm just not sure how he created the "Working" table to get the most recent dates from the "Detail" table, which does display on the report.

I'm sure there is a solution, I just don't have the experience to figure this out on my own...

Doug

 

[MStef]

Hello Doug!
For the most recent date in "tblWorking" look at Query1, Sort on
"therapyDay" = DESCENDING.

 

[Dougbum]

Hi MStef

I am unable to open Query1, I get a message that it's an action query, and when I select YES to open it, it will not open.

I am using Access 2003, and I believe your DB is written in Access 2000. Is that a problem?

With both Dennisk and your input, I will try to create a second query to sort by date and set the criteria to MAX Date, then refer to that query in the report query as the recordsource for the Date field.

Hopefully I'm on the right track!

Best wishes,

Doug

 

[MStef]

What do you mean with "Open query", look at DESIGN query.

 

[Dougbum]

OPEN vs DESIGN

Thanks MStef.
I was trying to open the query instead of looking at it in Design view!!!

I have a much better understanding of how your DB is designed and it should work for me. I'll spend this weekend working on re-configuring your DB to mine.

Again, many thanks for your help with this problem.

Doug

 

[Dougbum]

Problem continues with Query filtering!

My thanks to MStef and Dennisk for their prior help with this problem.

MStef... I still can't get your DB configured to run on my DB, I obviously haven't substituted the correct fields to match your design and I don't have enough Access experience to understand how you designed the DB.

Dennisk... I created a primary Query based on my original Download table and tried to filter the date using MAX in the criteria field, but I keep getting a "data mismatch" error. The "Date" field is configured properly, so maybe I'm not using the MAX expression correctly. I've tried various combinations of Max 'Date, MAX "Date" and Max(Date), but continue to get error messages.
My download table contains the following fields...

ID - primary key
ContactID - for the patient
Date - the dates for the monitor download (multiple dates are there)
Clinic - the location of the download
ReadBy - Clinician who interpreted the download

I'm sure there is a simple fix, but the problem has me going in circles!!!

My thanks to everyone who has helped and to all who continue to provide any assistance/guidance.

Doug

 

[MStef]

Hello Doug!

It must be you are a beginner in Access.
Look at another version for your problem.
"DemoMostRecent2A2000.mdb", open MainForm and see.

P.S. Look at next page.

 

[MStef]

Look at Dennisk's verzion, it works too.

 

[Dougbum]

Thanks MStef

YUP! I'm obviously a NEWBIE...

I took a quick look at the updated DBs from you, including the Dennisk fix as applied to your DB. The process makes a bit more sense to me now and I"ll take the next few days to experiment with your suggestions. I have to play with the DB after hours or on the weekend (the job comes first).

Many thanks again,

Doug

 

[Dougbum]

Query fix creates a new problem

The query I was using for my report was not configured for a MAX expression in the "total" field, now that the original problem has been fixed, the report will only show patients who have a date in the "MaxTherapyDay" field.

New patients will not have any info in the Date field until they have had an appointment. That means they do not show up on the Report as active patients in the database.

Can I "force" the query to display/print these patients without any data in the "Date" field?

Doug

 

[MStef]

Hello Doug!

Look at a new Dennisk's version.
New "Query3", to change "Query2".
Important: Join Properties 2, between "Table1" and "Query3" (in "Query2").
Open MainForm and try.

 

[Dougbum]

Query Filter working

MStef...your latest fix did it.

My deepest appreciation for your patience with this problem!

You're the BEST !!!

Doug