REPORT SUMMARY

[linda sciallo]

I WANT TO CREATE A REPORT THAT LOOKS LIKE THE
FOLLOWING:

CONTRACTOR TYPE OPEN, MISSING CLOSED
NAME CONTR #APPS KWH KW #APPS KWH KW
ABC CO HVAC 23 234 2 22 456 5

THE TABLE CONTAINS ONE STATUS FIELD THAT HAS
A VALUE OF O FOR OPEN, M FOR MISSING, AND
C FOR CLOSED. ONE CONTRACTOR CAN HAVE
MULTIPLE APPLICATIONS (ROWS) IN THE DATABASE WITH DIFFERENT STATUSES. I WANT TO SUMMARIZE THE DATA SO THAT I ONLY DISPLAY ONE LINE FOR EACH CONTRACTOR.

MY QUESTION IS:

IF MY QUERY RETURNS MULTIPLE ROWS FOR ONE
CONTRACTOR, HOW DO I TOTAL THE ROWS SO
THAT THE COUNTS FOR OPEN, MISSING AND
CLOSED ARE SUMMARIZED CORRECTLY FOR THE
ONE CONTRACTOR ON THE REPORT??

 

[Liv Manto]

Use the crosstab query.

COntractor___ Open___Missing ___Closed

GroupBy________Count__Count _____Count

 

[linda sciallo]

Not sure that I totally understand the solution. The row on the table contains one
column for the status field. The value in that field can be a 'O' for Open, 'M' for Missing or 'C' for closed. In the crosstab query, how do differentiate between each status?? Do I use IIF in the Count statement?? Can you give me a sample of what the SQL should look like (I have not used the crosstab query very much)??