Counting records

[Jacques Latoison]

Hello all,
I have a query that searches four different tables.
Employees, EmpAddresses, Payrolls, Contractors

Employees is 1-many to EmpAddress (so that historical data is kept on
addresses).
EmpAddress is 1-many to Payrolls (because paystubs have the current address
on them and for historical as well).
Contractors is a lookup table/pull down for the Payrolls table.

I need a proper count in the query of Employees per grouping.

Every query whether Select or CrossTab gives me a count of Payroll records
instead of Employees.

Example:

5 employees can have 10 paystubs a piece. Instead of counting and showing 5
people, it says there are 50 people because that's the total amount of
Payroll records that exist (meaning that each person shows up 10 times to
the Payrolls table because the Primary Key from Employees shows up 10 times
per person in the Payrolls table - 1-many relationship).

I can't remove the Payrolls table because I need to generate reports with
Payroll records.

I'm thinking its just something I don't know how to do, so any help is
appreciated.

 

[Jacques Latoison]

You know what, I got around it by first using a regular Select query, then
running a CrossTab against the Select query, then running the report against
the CrossTab. I'm using a CrossTab to generate the report in a spreadsheet
format,

but since I'm using a Parameter value in the Month field of the Select
query, the CrossTab says there's an error.

Why is that?


| Hello all,
| I have a query that searches four different tables.
| Employees, EmpAddresses, Payrolls, Contractors
|
| Employees is 1-many to EmpAddress (so that historical data is kept on
| addresses).
| EmpAddress is 1-many to Payrolls (because paystubs have the current
address
| on them and for historical as well).
| Contractors is a lookup table/pull down for the Payrolls table.
|
| I need a proper count in the query of Employees per grouping.
|
| Every query whether Select or CrossTab gives me a count of Payroll
records
| instead of Employees.
|
| Example:
|
| 5 employees can have 10 paystubs a piece. Instead of counting and showing
5
| people, it says there are 50 people because that's the total amount of
| Payroll records that exist (meaning that each person shows up 10 times to
| the Payrolls table because the Primary Key from Employees shows up 10
times
| per person in the Payrolls table - 1-many relationship).
|
| I can't remove the Payrolls table because I need to generate reports with
| Payroll records.
|
| I'm thinking its just something I don't know how to do, so any help is
| appreciated.
|
|

 

[John Spencer (MVP)]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2