Select record based on max value

[gwarnock85]

I have a database that I am using to record some basic patient information.

The tables I have include:
- PatientDB [main table - primary key is Patient_UID].
- Locations [holds names of different locations in a hospital linked to PatientDB by its primary key Location_UID].

- Analgesia_Primary [holds names of pain relieving methods, linked to PatientDB by its primary key Analgesia_UID] - there are 2 other similar tables that record different classes of pain relief in the same way using primary key link.

- DailyAssessment [this table has a many-to-one relationship with PatientDB]

The DailyAssessment table has its own primary key, Assessment_UID to record individual assessments for each patient, and links to PatientDB table using an integer value which is the same as Patient_UID.

What I am trying to do is get a query which will produce (for a report):

All patient details that are active (using criteria PatientDB.Active=True), the names of the different pain relief options they are on from other tables, and just the most recent entry for that patient from DailyAssessment (I have been using the max value).

I cannot seem to get everything I want from one query and have experimented with sub-queries but do not seem to get anywhere, and often end up with about 60 records being returned even though I do not have that many patients!

Any suggestions would be appreciated, and I can provide more field names etc as needed.

I am using Access 2007.

[twinnyfo]

gwarnock85,

Welcome to Bytes!

I do not see you accomplishing this with just one query. Primarily, if you are looking for the most recent Assessment, that will have to be a subquery to get the max. Granted, that "can" be embedded in the SQL string itself, but it is a subquery nonetheless.

If you would be so pleasant as to post what you have tried so far, that would be great. Then we can look at it and offer suggestions.

Hope we can hepp!

[PhilOfWalton]

Try building a query based on the Patients, location and something like:-

DLookup("Assesment", "DailyAssessment", "PatientID = " & PatientID & " AND AssementDate = " & DMax("AssesmentDate", "DailyAssessment", "PatientID = " & PatientID))

Sorry, that is air code so there may well be errors.

Base your Report on the above, and have 3 sub reports to show the various pain reliefs used.

Phil

[twinnyfo]

Good idea, Phil! I forgot about using the DMax() function. That will certainly work well if all the syntax gets worked out.

[NeoPa]

While subqueries can make a query a fair bit more complicated, especially for those who are new to it, using Domain Aggregate queries (such as DLookup()) is something to be warned against. It's an approach that makes it much harder for the SQL engine (Jet or ACE when using native Access but others are available via ODBC of course.) to determine the most efficient approach for you.

It can help to get the results you need but if you need to expand on it later then it's an inbuilt flaw.

It can be great for use within code, but just be aware that it's not generally recommended for use within SQL or queries.

[PhilOfWalton]

@Neopa

I agree, Domain Aggregate functions should be avoided, and given the actual table structure, it may me possible to use Sub Queries. As you say, they are not easy, and I am vert far from being proficient at designing them.

My rather primitive method would be to build a Totals Query to find the Maximum Date for that Patient's assessment, then link that query to another query based on the patient and DailyAssment and join the 2 PatientIDs and the Assessment Date.

Possibly if gwarnock85 were to post an image of his relationship window, he might get more concrete advice.

Phil

[NeoPa]

It sounds like your primitive method is pretty well bang-on then Phil ;-)

There's always an option to contain the one query within the other using Subqueries in SQL, but that isn't the only way. A defined QueryDef object works just as well. Particularly as far as the SQL engine is concerned. It's very impressive what even Jet & ACE can do as far as optimising queries goes.

[twinnyfo]

Side question (but related), NeoPa,

Whether the query is built as a separate QueryDef or whether it is embedded within the SQL itself, aren't both methods "essentially" using a SubQuery? One version uses a predefined Object, namely a QueryDef, the other uses the same SQL used for the QueryDef but inserts it into the main Query--both are then JOINed to one of the main query's keys or used as criteria.

Just want to make sure I understand the nature of SubQueries.

[NeoPa]

TwinnyFo:
Whether the query is built as a separate QueryDef or whether it is embedded within the SQL itself, aren't both methods "essentially" using a SubQuery?

Yes. You have that perfectly correct.