Trying to get a last record using 2 queries

[KristenD]

I am coming along with my Employee tracking database, much thanks to you all in the beginning. I have done some tweaking and working around somethings and now I have run into a brick wall on this latest tweakage.

I am looking to create a report that tracks the current job site of the employee. I cannot get any query I create to pull the last record of the employee number. I have been playing around and am thinking I may possibly need 2 queries and then join them to get the info I need. However, any time I try that using a Totals Query it will only use the PK and it will not let me try and use the FK for that table. What am I doing wrong???

Here is one of the queries I have been trying to work off which gets me halfway to what I want to see on the report.

SELECT tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.EmploymentStatus, tblEmpInfo.EmpInfoID, tblEmpInfo.JobNumberFK, tblJobs.JobSite, tblEmp.CraftCode
FROM tblJobs INNER JOIN ((tblEmp INNER JOIN tblEmpRating ON tblEmp.EmployeeID = tblEmpRating.EmployeeID) INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK) ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;

 

[KristenD]

That one was not the correct query, I'm sorry

Here is the correct query I am currently working on:

SELECT tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.CraftCode, tblEmpInfo.JobNumberFK, tblJobs.JobSite
FROM tblJobs INNER JOIN (tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK) ON tblJobs.JobNum = tblEmpInfo.JobNumberFK
GROUP BY tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.EmploymentStatus, tblEmp.CraftCode, tblEmpInfo.JobNumberFK, tblJobs.JobSite
HAVING (((tblEmp.EmploymentStatus)="Active"));

 

[plog]

Can you post sample data from your tables and then what the result is you want based on that sample data?

 

[KristenD]

I have exported the data into an excel file. Basically what I am trying to do is I have a table EmpInfo that has EmpInfoID, EmpIDFK, JobNumberFK.

This is a pop up form and I log each time the employee has a transfer, lay off, rehire, etc. I want to be able to pull the last entry for the employee that is active. I can get the query to pull all the active employees but it pulls ALL the entries of each job site where the employee has worked. I only want to pull the last entry for the employee.

I hope I'm explaining myself well enough! I will also post an attachment with my relationships for the DB. I am working in Access 2007.

 

[plog]

That looks like data from 1 table. Let's try again:

Can you post sample data from your tables and then what the result is you want based on that sample data?

 

[KristenD]

I am trying to get the last record/job number entered for each "active" employee.

I have uploaded my relationship report as well as the design view of the query.

For example an employee may have worked three jobs in the past year but I only want to see his last job entered.

Does this help?

 

[plog]

You can reiterate (unfortunately, I used that word correctly) what you are trying to do all you want (the count is up to 3 times so far), but until I get what I ask for, I'm not going to be able to help.

 

[MSAccessRookie]

I have exported the data into an excel file. Basically what I am trying to do is I have a table EmpInfo that has EmpInfoID, EmpIDFK, JobNumberFK.

This is a pop up form and I log each time the employee has a transfer, lay off, rehire, etc. I want to be able to pull the last entry for the employee that is active. I can get the query to pull all the active employees but it pulls ALL the entries of each job site where the employee has worked. I only want to pull the last entry for the employee.

I hope I'm explaining myself well enough! I will also post an attachment with my relationships for the DB. I am working in Access 2007.

If I am interpreting your attachment correctly, if provides the results of a Query that joins your three Tables, and you are trying to eliminate duplicated entries.

It appears that the problem here lies in the fact that an employee can work at more than one site, and your Group By arguments include the Site. If you want the max for the Employee, regardless of site, then you need create a SubQuery that gets the Max of JobNumberFK from tblEmpInfo based on EmpIDFK Only, and use the results of that query to collect the information from the additional Tables.

-- Rookie

 

[KristenD]

It seemed to work! Thank you!!

SELECT tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.CraftCode, tblEmpInfo.JobNumberFK, tblJobs.JobSite
FROM qryMaxEmpInfoID INNER JOIN (tblJobs INNER JOIN (tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK) ON tblJobs.JobNum = tblEmpInfo.JobNumberFK) ON qryMaxEmpInfoID.MaxOfEmpInfoID = tblEmpInfo.EmpInfoID
WHERE (((tblEmp.EmploymentStatus)="Active"))
ORDER BY tblEmpInfo.JobNumberFK;