Option Box Querying and Totaling 1

[mraetrudeaujr]

I had to change my check boxes to an 'Option Group' to prevent users from checking more than one box. Of course, a problem has arisen. The SQL that worked before will not (due to deleting the previous four fields that were related to the check boxes). My data is intact (I ran an update query and everything is neatly tucked in one field). I went back and tried to modify the SQL but apparently I have syntax errors. Anyways, I cannot get it right. I would appreciate someone taking a look at this to straighten it out.

Briefly, I have one field that is carrying four 'Options'. They are; VR (1), WA/NTA (2), TOT (3), and None (4). The last item (None) doesn't matter as far as the final report is concerned.

SELECT [STATION],
     Sum([DISPOSITION]),1,  AS [VOLUNTARY RETURN], 
     Sum([DISPOSITION]),2,  AS [WA/NTA] 
     Sum([DISPOSITION]),3,  AS [TURNED OVER TO OTHER AGENCY],
     FROM tbl_reinstatement
     WHERE STATION Is Not Null
     GROUP BY [STATION] 
     UNION ALL SELECT "Total"
     Sum([DISPOSITION]),1 
     Sum([DISPOSITION]),2 
     Sum([DISPOSITION]),3
     FROM tbl_reinstatement
     WHERE STATION Is Not Null;

Thanks in advance.

 

[PHV]

SELECT [STATION],
Sum(IIf([DISPOSITION]=1,1,0)) AS [VOLUNTARY RETURN],
Sum(IIf([DISPOSITION]=2,1,0)) AS [WA/NTA],
Sum(IIf([DISPOSITION]=3,1,0)) AS [TURNED OVER TO OTHER AGENCY]
FROM tbl_reinstatement
WHERE STATION Is Not Null
GROUP BY [STATION]
UNION ALL SELECT "Total",
Sum(IIf([DISPOSITION]=1,1,0)),
Sum(IIf([DISPOSITION]=2,1,0)),
Sum(IIf([DISPOSITION]=3,1,0))
FROM tbl_reinstatement
WHERE STATION Is Not Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mraetrudeaujr]

Thanks PHV!

It worked (as usual). After reading (and receiving) many of your responses/solutions to many questions, I am convinced that using SQL is the way to go. My problem is that I don't have any material or lessons that I can use to enable me to do this myself. I did try to modify one of your previous solutions to one of my problems, but I didn't get it right. I'll keep trying though. Thanks again.

Al