Trying again

[Dennis]

I am trying to create a query that calculates the count of
pay grades in a field named [Grade1]. In this field the
values can be and are 1, 2, 3, 4, and 5. I have been able
to count using DCount but, I have 5 cost centers and I
need seperate values for each cost center. Using this
expression DCount("Grade1","Employees","[Grade1] > 3")I am
getting is this result:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11 54 54 54 54 54
36 45 45 45 45 45
61 69 69 69 69 69
64 87 87 87 87 87
92 2 2 2 2 2
-----------------------------------------------------------
When I use this expression Grade1,2: (IIf([Cost Center]
='36',DCount("Grade1","Employees","[Grade1] > 3")))I get
this result:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11
36 45 45 45 45 45
61
64
92
-----------------------------------------------------------


I would like the query to look like this:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11 10 15 8 13 8
36 5 7 10 7 15
61 20 18 11 10 10
64 18 18 17 18 16
92 1 1

-----------------------------------------------------------
If possible, I could use a calculated field in a report
for each of the above results as the report is sorted by
cost center. Hopefully I made this info clear. Thanks,

Dennis

 

[Dale Fye]

Dennis, are your columns (Grade1, Grade2, Grade3, Grade4, Grade5) a
concatenation of the word Grade to the value that is in the field?

Try this:

Create a new query. Add your table to the design grid, and make the
query a crosstab query

Select the [Cost Center] field, set its properties:
Field:Cost Center
Table:yourTable
Total: GroupBy
Crosstab:RowHeading

Add the [Grade1] field to th grid, set its properties
Field:[Grade1]
Table:yourTable
Total:GroupBy
CrossTab:ColumnHeading

Add another field to the grid, can be any numeric field
Field:
Table:yourTable
Total:Count
Crosstab:value

Then run the query. Is this what you are looking for?

--
HTH

Dale Fye


I am trying to create a query that calculates the count of
pay grades in a field named [Grade1]. In this field the
values can be and are 1, 2, 3, 4, and 5. I have been able
to count using DCount but, I have 5 cost centers and I
need seperate values for each cost center. Using this
expression DCount("Grade1","Employees","[Grade1] > 3")I am
getting is this result:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11 54 54 54 54 54
36 45 45 45 45 45
61 69 69 69 69 69
64 87 87 87 87 87
92 2 2 2 2 2
-----------------------------------------------------------
When I use this expression Grade1,2: (IIf([Cost Center]
='36',DCount("Grade1","Employees","[Grade1] > 3")))I get
this result:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11
36 45 45 45 45 45
61
64
92
-----------------------------------------------------------


I would like the query to look like this:

Cost Center Grade1 Grade2 Grade3 Grade4 Grade5
-----------------------------------------------------------
11 10 15 8 13 8
36 5 7 10 7 15
61 20 18 11 10 10
64 18 18 17 18 16
92 1 1

-----------------------------------------------------------
If possible, I could use a calculated field in a report
for each of the above results as the report is sorted by
cost center. Hopefully I made this info clear. Thanks,

Dennis

 

[Sirocco]

Try a crosstab query, where the table values use the count function.