One to Many Query - Most Recent Child Record (1 Viewer)

[Holly_Associated]

Hi Experts,
I've been Googling for a while and have found a lot of opinions/versions of an answer but as this is the first time I have delved into query SQL I thought I might ask if anyone would be kind enough to help me.

I have a "parent" table: tblEmployeeDetails
And a child table: tblAddressList
which are linked by the common numerical field: EmployeeID

For each Employee, I should have an address history going back at least 5 years, and so the tables have a one (Employee) to many (Addresses) relationship.

tblAddressList includes date/time fields DateFrom and DateTo

So, I am trying to create a query in which all of my parent records appear (All employees names show) regardless of having a subsequent child (address) record as I haven't yet collected all addresses. I've solved this by changing the join type I think.

I would also like only the most recent address (for those on which I have a full history) to appear. So I am thinking most recent DateFrom or something along those lines?

I have the following which shows all of my parent records and ALL associated child records. Please could someone indicate how I would just select the most recent child using the DateFrom field?

From SQL View:
SELECT tblEmployeeDetails.EmployeeID, tblEmployeeDetails.Title, tblEmployeeDetails.FirstName, tblEmployeeDetails.Surname, tblAddressList.AddressLine1, tblAddressList.AddressLine2, tblAddressList.Town, tblAddressList.County, tblAddressList.PostCode, tblAddressList.DateFrom, tblAddressList.DateTo
FROM tblEmployeeDetails LEFT JOIN tblAddressList ON tblEmployeeDetails.EmployeeID = tblAddressList.EmployeeID;

 

[rzw0wr]

Try the Dmax() function for the DateFrom field.

Dale

 

[Holly_Associated]

Hi Dale,

OK I've put this:

DMax("DateFrom","tblAddressList")

in the criteria row of DateFrom on the query design screen and it only returns the most recent DateFrom (1 Employee, 1 Record) and ignores all of the other Employees. I tried using SELECT Max(... but it was giving me errors wherever I put it in the SQL screen. Please could I have a further pointer?

 

[rzw0wr]

Take everything out of your query except the following.
tblEmployeeDetails.EmployeeID, tblEmployeeDetails.FirstName, tblEmployeeDetails.Surname, tblAddressList.DateFrom.

Use the Summation icon in the query design view.
GroupBy everything but the DateFrom field. Make that max.

See is this gives you what you want.

Dale

 

[Holly_Associated]

Hi Dale,
Yes! That works??
So why doesn't it work when I have the other fields in there? If you could explain I can hopefully avoid this in future.

 

[Holly_Associated]

Also, I do need to see the address? How can I add this without showing all the records again? (I just tried adding AddressLine1 and it shows all addresses again)

 

[rzw0wr]

It is grouping on the other fields also.
Put them back in 1 by 1 and you will see what I mean.
I don't know how to explain it without writing a book.
The query will group on each field in the query. There is no way to tell it to return the field and not group on it.

Dale

 

[Holly_Associated]

OK I think I understand.
Is there a way to to pull up the address based on the MaxOfDateFrom column? i.e. a query inside a query or something similar.
I know Access can do pretty much anything, you just have to know how to tell it!

 

[rzw0wr]

I think you could take the query you just make and use it plus the table.
Give that a try. Nothing ventured, nothing gained.

Don't change the join type, leave it as access sets it as an inner join.

Dale

 

[Holly_Associated]

Well, I found this http://stackoverflow.com/questions/...he-last-records-in-a-one-to-many-relationship which appears to show a solution for what I need but when I try to incorporate my own names into the SQL screen:

SELECT tblEmployeeDetails.EmployeeID, tblEmployeeDetails.Title, tblEmployeeDetails.FirstName, tblEmployeeDetails.Surname, tblAddressList.AddressLine1, tblAddressList.AddressLine2, tblAddressList.Town, tblAddressList.County, tblAddressList.PostCode, tblAddressList.DateFrom, tblAddressList.DateTo
FROM tblEmployeeDetails INNER JOIN
(
SELECT EmployeeID,
MAX(date) MaxDate
FROM tblAddressList
GROUP BY EmployeeID
) MaxDates ON tblEmployeeDetails.EmployeeID = MaxDates.EmployeeID INNER JOIN
tblAddressList ON MaxDates.EmployeeID = tblAddressList.EmployeeID
AND MaxDates.MaxDate = tblAddressList.DateFrom

I am getting this error on run:
Syntax error (missing operator) in query expression 'tblEmployeeDetails.EmployeeID = MaxDates.EmployeeID INNER JOIN tblAddressList ON MaxDates.EmployeeID = tblAddressList.EmployeeI'.

I'm not sure why it isn't picking up the final D or if I've just completely pillaged the coding?

Any help much appreciated!

 

[nanscombe]

In the attached database I have attacked the problem in stages:

Stage 1: qry001_LatestFromDateForEmployee

Get the EmployeeId and latestDateFrom from tblAddressList

SELECT tblAddressList.EmployeeID, Max(tblAddressList.DateFrom) AS LatestFromDate
FROM tblAddressList
GROUP BY tblAddressList.EmployeeID;

Stage 2: qry002_LatestAddressForEmployee

Get the most recent address line by linking tblAddressList to qry001_LatestFromDateForEmployee

SELECT tblAddressList.*
FROM qry001_LatestFromDateForEmployee INNER JOIN tblAddressList ON (qry001_LatestFromDateForEmployee.LatestFromDate = tblAddressList.DateFrom) AND (qry001_LatestFromDateForEmployee.EmployeeID = tblAddressList.EmployeeID);

Stage 3: qry003_EmployeeWithMostRecentAddress

Gives Employee with their most recent address

SELECT tblEmployeeDetails.empName, qry002_LatestAddressForEmployee.Address
FROM tblEmployeeDetails INNER JOIN qry002_LatestAddressForEmployee ON tblEmployeeDetails.EmployeeID = qry002_LatestAddressForEmployee.EmployeeID;

 

[Holly_Associated]

Nigel, wow, thank you for taking the time to do this! I've just had a little look at home and you've hit the nail on the head! I can also incorporate this with all my other one-to-many tables and it isn't completely baffling, just logical. I will set this in my DB when I get to my desk in the morning and report back.

Thank you again!

 

[Holly_Associated]

Hi All,
With many thanks to Nigel for showing me the way, I now have my query and report acting perfectly. I made a few tweaks and managed to cut the process down to two queries and include any blanks (A blank DateFrom in the Address and those Employees with no Address at all)...