Combine Two Fields Values for Totals Report

[Michael]

Hi Folks - I am tracking criminal cases. Each case has two combo-box
disposition fields: DISP1 and DISP2. Both dispositions "lookup" the same
list of dispositions, such as guilty, not guilty, waived and dismissed. I
need to run a report that counts the COMBINED values of both disposition
fields. So, for 4 cases, if the DISP1 field contained:

Guilty
Guilty
Dismissed
Waived

and the DISP2 field contained:

Guilty
Dismissed
Not Guilty
Waived

then I would need a query/report that counted the total dispositions. So the
final report would be:

Guilty 3
Dismissed 2
Waived 2
Not Guilty 1

Make sense?

Any ideas?

Thanks,

Michael

 

[Marshall Barton]

Hi Folks - I am tracking criminal cases. Each case has two combo-box
disposition fields: DISP1 and DISP2. Both dispositions "lookup" the same
list of dispositions, such as guilty, not guilty, waived and dismissed. I
need to run a report that counts the COMBINED values of both disposition
fields. So, for 4 cases, if the DISP1 field contained:

Guilty
Guilty
Dismissed
Waived

and the DISP2 field contained:

Guilty
Dismissed
Not Guilty
Waived

then I would need a query/report that counted the total dispositions. So the
final report would be:

Guilty 3
Dismissed 2
Waived 2
Not Guilty 1

First, you need to normalize the dispositions in a query:

qry1:
SELECT Case, disp1 As Disp
FROM thetable
UNION ALL
SELECT Case, disp2
FROM thetable
WHERE . . .

Then the totals query would simply be:

SELECT Case, Count(Disp) As DispCount
FROM qry1
GROUP BY Case