Wrong totals on a Select Query 1

[mraetrudeaujr]

I have been trying to work this out on my own, to no avail. What I need are TOTALS. It should be pretty straightforward, but the totals aren't coming out right. It looks like it is counting all of the record entries for a particular station, and then using that for the totals on each of the three categories. If I can get this query to work, I think that I can use it's design on another database that uses similar data. Here is the SQL and the results;

SELECT [tbl_reinstatement].[STATION], Count([tbl_reinstatement].[WA_NTA]) AS [WA/NTA], Count([tbl_reinstatement].[VOLUNTARY_RETURN]) AS [VOLUNTARY RETURNS], Count([tbl_reinstatement].[TURNED_OVER_TO]) AS [TURNED OVER TO OTHER AGENCY]
FROM tbl_reinstatement
GROUP BY [tbl_reinstatement].[STATION]
HAVING (((tbl_reinstatement.STATION) Is Not Null) AND ((Count(tbl_reinstatement.WA_NTA)) Is Not Null) AND ((Count(tbl_reinstatement.VOLUNTARY_RETURN)) Is Not Null) AND ((Count(tbl_reinstatement.TURNED_OVER_TO)) Is Not Null))

STATION	WA/NTA	VOLUNTARY RETURNS	TURNED OVER TO OTHER AGENCY
BLV	85	85	85
BRF	68	68	68
CAO	70	70	70
CHU	56	56	56
ECJ	80	80	80
I8 CHKPT	1	1	1
I94 CHKPT	65	65	65
IMB	94	94	94
SCM	63	63	63
TEM	66	66	66

As you can see from the resultant 'table' these figures aren't/cannot be right. Please help! Thanks in advance.

Also, how can I get my query results to post just like when you run it and it comes back like 'Excel'?

 

[PHV]

What I need are TOTALS
SELECT [STATION], Sum([WA_NTA]) AS [WA/NTA], Sum([VOLUNTARY_RETURN]) AS [VOLUNTARY RETURNS], Sum([TURNED_OVER_TO]) AS [TURNED OVER TO OTHER AGENCY]
FROM tbl_reinstatement
WHERE STATION Is Not Null
GROUP BY [STATION]

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

 

[mraetrudeaujr]

I am sorry PHV. I really need to give you something more challenging! This worked like a well-oiled machine, however---

My next step is to create a report that shows this information 'totaled up' at the bottom of each column (excluding the 'Station' column of course). So armed with this information how would I modify this query to show the totals at the bottom of each column? Sure, I could manually tabulate these figures and then put that number on the report, but then I know that Access knows how to do this, right?

 

[PHV]

The report can handle itself the column's total, anyway a pure sql solution:
SELECT [STATION], Sum([WA_NTA]) AS [WA/NTA], Sum([VOLUNTARY_RETURN]) AS [VOLUNTARY RETURNS], Sum([TURNED_OVER_TO]) AS [TURNED OVER TO OTHER AGENCY]
FROM tbl_reinstatement
WHERE STATION Is Not Null
GROUP BY [STATION]
UNION ALL
SELECT "Total", Sum([WA_NTA]), Sum([VOLUNTARY_RETURN]), Sum([TURNED_OVER_TO])
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]

PHV you are a genius!

This is exactly what I needed. It looks like SQL is a better way to create queries, right? If so, then what can you suggest to get me running in that direction (SQL)?

Every time I submit something to "Tek-Tips" you guys/gals always have a solution. I hope to eventually soak up this knowledge so that I can get from the 'crawl' stage to the 'walk' stage. Thanks for your help.

Al