more than just COUNT in query

[ladydazy]

Hmmmm, evasive query this...

I have tables: tblEmployeedetails,tblParttimeEmp, tblFulltimeEmp,tblEmployee and tblClubs

ParttimeEmp and FulltimeEmp are subsets of tblEmployee and thus all have the same PK(empid). TblEmployee is linked to the tblEmployeedetails via the same PK(empid). TblEmployeedetails has 2 PKs: (PK)ClubID and (PK)empid.

I need to write a query that shows all the different Clubs and how many Parttime employees (COUNT) and Fulltime employees (COUNT) they have.

I have several different versions of an attempt, and most versions gave me COUNT = 0 for both types of employees!

 

[stardusty]

You could sum them in a report that groups first by club, then second by their schedule (parttime or fulltime) and then sums the part and full time employees in the schedule groups. Create a text box in the schedule group header; sum with =count(*) as control source.

 

[Len Boorman]

Think that you should have only one table for Employee. An Employee is an Employee.

They may be Full Time or Part Time, that is an attribute of Employee.

So Full Time becomes Yes or No or Part Time is the reverse, whichever suits you.

Then the query and grouping in easy

Len