I'm trying to get some kind of query to work for a report. Here are the details. Please bear with me as this is a bit of a long explaination though simple in spirit :)
There are two tables linked by a keyfield. The relationship is one to many. So table A links to many in table B by that key field. Keep in mind that this key field is not the primary key in table B but it is indexed(FYI). Anyway, there is a date field in table B for which I am trying to get the latest of in a group of common values made from the key link field.
The catch seems to be that in order to utilize the GroupBy() and the Max() functions you need to follow certain rules. It seem that these rules do not allow the use of wildcards so as to include all the fields in a query result. Thus, I resolved to use three queries to produce the results that I need. 1.) a query that gets the latest date and one other field to link to from table B. 2.) an inner join to get all fields in table B linked to each row returned from the first query. 3.) a query linking table A to table B to get all fields from both tables where each row then has the latest date in B.
The end results preferrably would be that all fields from both tables are in a single returned row. Each row contains the latest date from the group of records with a common linked key field.
While this appears to work in Access through SQL views and trial and error, I have a somewhat more difficult time getting it to work in an MFC application with an embedded SQL call through DAO since I'm using a single recordset to contain the results. The main question is can this be done with a single query in Access SQL? And if not, would a querydef call to the main query above be the most efficient way to call this query in my application?
Here are my current queries:
from Table B (with latest dates)
SELECT Exam.PatientAutoKey, Max(Exam.Date) AS MaxOfDate
GROUP BY Exam.PatientAutoKey;
from Table B (gets the rest of the fields linked up with latest date)
SELECT DISTINCT Exam.PatientAutoKey, Exam.ExamRecordID,Exam.Date,
...all the rest of the fields...
FROM Exam INNER JOIN MaxExamDates ON Exam.PatientAutoKey = MaxExamDates.PatientAutoKey
from Table A - link all the fields in table A to those in table B.
FROM Patient INNER JOIN ExamByMaxDate ON Patient.PatientAutoKey = ExamByMaxDate.PatientAutoKey
ORDER BY Patient.PatientLastName;