Holly_Associated
Registered User.
- Local time
- Today, 06:06
- Joined
- Mar 21, 2013
- Messages
- 53
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;
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;