Issue with Report and Table set up

[KristenD]

I have attached the zipped file for my database. I am having an issue with one my reports, qryOSHAJob. If I have an active employee transfer to a new job, it still has a record that shows up where the employee is before.

Currently I have the table set up only to collect job information if the employee is active. It may be that the employee stays active and will transfer jobs every few months but I want the report to take only the most current record. Is there some code that needs to be set up behind the report?

Do I need to add another field to put the most current date in that table?

Any Suggestions Please???? Thank You!!!!!!!

 

[vbaInet]

We need to understand the problem before we look at your db Kirsten. What exactly is the problem here?

 

[KristenD]

The issue is the query/report qryOSHAJob/rptOSHAJob.

It is based tblEmp, tblEmpInfo and tblOSHA. The issue is when tblEmpInfo has more than one entry for an employee it displays both, not the most current entry. I want to be able to see a history of jobs but also only see the most current job in the report/query. This table will be used in another report for Licensing so I only need the most current info on those reports.

 

[vbaInet]

So you want to see the most recent job per employee? Is the record going to edittable?

Have a look at this in the meantime:

http://allenbrowne.com/subquery-01.html

 

[KristenD]

Yes, I have the form set as data entry but it only shows one record at a time, I can't scroll through the records for each EmpID. I think the records are editable as of right now though.

I don't want the records to be overwritten. I would like to be able to pull the employee and see the jobs the employee has been at.

 

[vbaInet]

And see the jobs or see the most recent job? Please clarify what you're trying to achieve here.

 

[KristenD]

For the purposes of the OSHA report and Licensing Log reports/queries I would like to see the current job (when the employee is active) so the most recent entry for the employee.

Currently for these reports I have spreadsheets that I use but since I have to run a separate report through our payroll system then transfer the information to the spreadsheet it is a very lengthy process. I am trying to through the database have all the information in one spot as our payroll/HR system tracks none of this.

 

[vbaInet]

So what did you make of the link I gave you earlier? Pay attention to the section "Top n records per group". But note this will make your query read-only so it's ideal for reports.

If you want to get the latest job per employee and still make it edittable, then you need to use the DMax() function in the criteria row of the query.

 

[KristenD]

I did read through the link. I am a little unsure of how to do it as I am still a beginner writing code and such.

Do I have to have a field in the actual table to use the DMax() function? How does it know which entry is the most recent?

 

[vbaInet]

DMax() will be a column in your query. Link the DMax to each row using the Criteria argument (i.e. the third argument) of the function. Then under this column set a criteria of > 0.

 

[KristenD]

Ok, I will play around with this and see if I can't get it to work. I will post if I have more issues!

Thank you so much!!

 

[vbaInet]

No problem. Ensure to post your trial SQL statement if you don't succeed.

 

[KristenD]

Ok, I am having a tough time figuring this out. I'm not sure how the DMax function would work in here. The table the query is using already has an autonumber for its PK and then looking at the first link from Allen Browne, I see how that would work but not exactly sure how to get it to work how I need it work.

Here is the code I currently have:
[CODESELECT tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.EmploymentStatus, tblLicensing.LicenseID, tblLicensing.License, tblLicensing.Category, tblLicensing.Qualifications, tblLicensing.ExpirationDate, tblLicensing.Restrictions, tblEmpInfo.JobNumberFK, tblEmpInfo.EmpInfoID, tblEmpInfo.EmpType, tblEmpInfo.CraftCode
FROM (tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK) INNER JOIN tblLicensing ON tblEmp.EmployeeID = tblLicensing.EmpIDFK
WHERE (((tblEmp.EmploymentStatus)="Active"));
[/CODE]

I am not sure where to put the WHERE clause in there how to tell it to look for the most recent entry. I'm guessing it would look for the higher LicenseID.

Thank you!

 

[vbaInet]

First of all, create a column in your query that will get the max Job ID per Employee using the DMax() function. Think about it. You may start with creating the DMax() without criteria and then move from there.

 

[KristenD]

Ok. I will give that a try.