Help

[Rea]

I have a table called job sheet in which I have fields such as employee, wrkdate and chemicals used.
In one contract (job reference no 274) I had one employee working for one day but has been entered five times in the table due to having used five different chemicals on that day.

I need a calculation that will work out the number of days as well as the number of employees who have worked for each contract i.e. job reference number.

I have tried several queries to no avail.
Any suggestions as I am totally lost
Thanks

 

[catt]

Job # date employee# chemical#

Employee# employee name

Chemical# chemical name


In the query link the employee# and the chemical# click on the TOTALS icon.

Use Group by for job#, count for date, and count for employee name

Three tables per above
One query

Is this what you need?

 

[IfButOnly]

Rea, didn't have time to figure out the syntax but what you need is a union query - check it up in help.

One query will bring back each employee working on a job -

SELECT jobid, jobdate, first(employee) AS FirstOfEmployee FROM TableName GROUP BY jobid, jobdate

the other query will summarise

SELECT jobID, jobdate, count(employee) as CountOf Employee....etc

These need to be put into a UNION QUERY. Don't rely on my SQL above but hopefully give you the generally direction.

 

[Rea]

Hi,
Thank you everyone. I have tried Pat suggestion first and it works, I cannot thank you all enough.
Happy new year to you all.
Bye for now Rea