Using Query to Flag changes in crafts

[Blankman]

I am currently trying to audit employee craft changes over a period of time. I have been able to pull all the data into a pivot table how ever the query is picking up the employee name and counting them for every time sheet and is not separating the craft type by time sheet. Any idea on how I would be able to have a pivot set up so that the employee name is linked with the craft and that specific time sheet?

[orange]

Please tell us in plain English what this means? Perhaps an example would help.

employee craft changes over a period of time

Seems you have

Employees who build crafts. Employees complete time sheets. Crafts are associated with a CraftType.

[Blankman]

Please tell us in plain English what this means? Perhaps an example would help.


Seems you have

Employees who build crafts. Employees complete time sheets. Crafts are associated with a CraftType.

The craft is the employee's description such as welder or foreman.
I have uploads that are sent in by inspectors which give me the employee's time as well as what they are classified as. I am wanting to find out if any of the employees have had their crafts changed since the beginning of the year. (There are over 3000 employees BTW)

[orange]

So, Inspectors send information about Employee's and their Time sheets. Included in the information is the Employee's current craft (position/occupation??). The Time sheets include relevant Dates. You want to have a query to identify those Employees who have changed crafts since the beginning of this year.

I see the following set up (first approximation)

tblEmployee
EmpId
EmpFname
EmpLName

tblTimesheet
TimesheetID
WorkPeriodStart
WorkPeriodEnd
HoursWorked
CraftID
EmpID_FK


tblCraft
CraftID
CraftName

1 employee ----has many Timesheets
tblEmployee--->tblTimeSheet

tblTimesheet has field CraftID_FK which is a foreign key to CraftID in TblCraft to resolve CraftName

You can get those EmpIds with more than 1 craft since the beginning of the year with a query long these lines

select empid_fk as emp, count(craftId) as If_greater_than_1_MeansChanged from
(SELECT tblTimesheet.empid_fk, tblTimesheet.craftId
FROM tblTimesheet
GROUP BY tblTimesheet.empid_fk, tblTimesheet.craftId) 
group by empid_fk

Good luck.