Getting a total for specific information in a field.

[SG1CSIfan]

I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild. And if so how?

 

[Mr Smin]

Do a query on the history table something like
Select EmpID, TypeofAction, Count (*) AS cnt from History
Group By EmpID, TypeofAction

the cnt column will show the number of times Bob has been late etc.
In the query builder, right click the grid and choose Totals to use Group By.

 

[SG1CSIfan]

I am sry can you break it down for me more,

If I were to build the Quarry I would take the EmployeeT Table and being in the employee ID, and then from the EmployeeHistoryT, I would bring in type of action, so it would look like this......

Feild Name: EmployeeID TypeOfAction
Table: EmployeeT EmployeeHistoryT
Total: Group By Group By
Sort:
Etc
etc.

Where would I put the count funcation, in a new colum or........
Sry need things describe more simple for me as im still learning access

 

[Mihail]

In TypeOfAction field.

 

[SG1CSIfan]

Ok I got that to work, now my only problem I see is that, for example, Employee 1 has cancelled 5 times. In the description part, I have him Cancelled 3 times for being sick and 2 times for other reasons. When I get the totals, I get Cancelled total 2 and then Cancelled total 3. Anyway to fix this, so it does not take the description into affect?

 

[Mihail]

Better is to do this in a report that show you the count grouped for reasons and the total count (for any reason grouped by employe).

 

[Mr Smin]

Agree with Mihail. Since you are reporting on an HR issue, you do care about the reasons a person cancelled and you don't want to have just a grand total.

Going back to the query grid, did you make it look like the following? This will show you counts separately from TypeOfAction

Feild Name: EmployeeID | TypeOfAction | tally: Count(*)
Table: EmployeeT | EmployeeHistoryT | (leave blank)
Total: Group By | Group By | (leave blank)
Sort:
Etc