Connect multiple info in a field with a corresponding #

[NDNobbs]

I have a report. It has a list of employees. Each employee has an identifier
that is 2 letters (i.e., YB, YE, GS, WG, etc.). Each can be used multiple
times. Each employee also has a grade that is 2 numbers (i.e., 04, 05, 10,
13, etc.). A combination of these (i.e., YB 03) corresponds to a payband. For
example...YB 03 connects with band 1, YE 12 connects with band 4, YB 06
connects with band 4, etc. Multiple combinations of 2-letter identifiers and
2-digit grade numbers are possible. In a report, how can I pull the number of
employees based on their identifiers and connect it to the correct band to
get a total?

 

[Ken Sheridan]

You need another table with three columns the first two having unique
combinations of the 2-letter identifiers and 2-digit grade numbers, so you
should create a unique index on the two columns (in combination, not
individually, which you can do by making them the composite primary key), the
third column containing the pay band which corresponds to each combination of
values in the other columns.

Create relationships from this table to Employees on the first two columns,
and to PayBands on the third, Payband, column, enforcing referential
integrity in each case.

Base your report on a query which joins Employees to the new table on the
two columns. You can then group the report by pay band and count the
employees per band, sum/avg/min/max the salaries per band etc in the group
footer.

Ken Sheridan
Stafford, England

 

[NDNobbs]

Great! Working on it now. Thanks!
--
NDNobbs

You need another table with three columns the first two having unique
combinations of the 2-letter identifiers and 2-digit grade numbers, so you
should create a unique index on the two columns (in combination, not
individually, which you can do by making them the composite primary key), the
third column containing the pay band which corresponds to each combination of
values in the other columns.

Create relationships from this table to Employees on the first two columns,
and to PayBands on the third, Payband, column, enforcing referential
integrity in each case.

Base your report on a query which joins Employees to the new table on the
two columns. You can then group the report by pay band and count the
employees per band, sum/avg/min/max the salaries per band etc in the group
footer.

Ken Sheridan
Stafford, England