Microsoft Access - query - create a build - Asked By Samantha Titus on 05-Jun-12 04:37 AM

I have a section in my database that have upto 10 rows of information for one person. currently when I run a query it gives me the same person with all the rows.(in otherwords all the 10 rows)   I want to run a query that will give me the persons name and the last line in that section, is this possible and if so how do i do that?

How do you know if a line is the last line?  Is there a date associated with it?

You can use a where clause that checks detail records against the max date associated with the person.  Can you post your table structure, or at least the pertinent pieces?

 frm_Input.zip

i have attached a screen shot of the actual information.  on the section of Work history you will see that I have many rows.
what I need there is to have the query give me information such as the employees name, clock number, etc then only print the last line in the work history section,

This query will include the last row of data in each record, you would just use the Last columns to populate your header:

SELECT tblEmpTrans.Employee, tblEmpTrans.SiteWorked AS LastSite, tblEmpTrans.PeriodFrom AS LastDate, T1.SiteWorked, T1.PeriodFrom, T1.PeriodTo, T1.CoreCrew, T1.BlackListed
FROM tblEmpTrans INNER JOIN tblEmpTrans AS T1 ON tblEmpTrans.Employee = T1.Employee
WHERE (((tblEmpTrans.PeriodFrom)=(SELECT Max(T2.PeriodFrom) AS MaxOfPeriodFrom FROM tblEmpTrans as T2 WHERE T2!Employee = tblEmpTrans.Employee GROUP BY T2.Employee;)));