Count codes by employee

[tinkertoy]

I want to create a subform based on a query that reflects the following:

Employee 1 - Code 1, Name - How many times
Code 2, Name - How many times

The tables are: tblCodes, tblEmployee, tblMasterFile (where the employee is associated with MasterFile and Codes), jtblCodeMasterFile, jtblEmployeeMasterFile (jtbl are the joins in a Many-to-Many relationship).

The Main form is an Employee History

Tab 1 - History of MasterFile - by Employee
Tab 2 - Codes (how many per code per Employee)

So, the employee is on many different MasterFiles, and can have 1 or more codes associated (there also can be 1 or more employees on each MasterFile)

If you are confused,%-) sorry - it really does seem simpler. I know it is a COUNT function in the query. All that needs to show in the subform (Tab 2) is:

Count CodeNumber CodeName Emp#(NotVisible for Parent/Child relationship form/subform)

2 25 CodeName25 P0000
1 5 CodeName5 P0000
1 8 CodeName8 P0000

and so on.....

Thank you for your help! :-{}

Rona
res07pyp@verizon.net

 

[cmmrfrds]

The question is not easy to determine from the explanation. Please simplify, which tables contain the codes and the employee id? What is the relevant primary and foreign key in each table.

 

[dalchri]

Actually, this ought to be pretty simple. You are after 4 fields. Lets knock off 3 of them. CodeNumber and Emp# should be in the same table somewhere (sounds like jtblCodeMasterFile?). The Count field will come from this table. This query gives us 3 of the 4 we're after:
[tt]
SELECT COUNT(*) AS [Count], CodeNumber, [Emp#]
FROM jtblCodeMasterFile
GROUP BY CodeNumber, [Emp#]
[/tt]
To get the 4th field, just use this query as a subquery and join onto the codes table:
[tt]
SELECT [Count], Summary.CodeNumber, CodeName, [Emp#]
FROM (
SELECT COUNT(*) AS [Count], CodeNumber, [Emp#]
FROM jtblCodeMasterFile
GROUP BY CodeNumber, [Emp#]
) AS Summary LEFT JOIN tblCodes
ON Summary.CodeNumber = tblCodes.CodeNumber
[/tt]
I use a LEFT JOIN because I don't know if you've got referential integrity enforced. I use a subquery so that the db engine does not have to group by the text field CodeName.