Need query help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Need query help...

Author

Topic

parallon
Starting Member

25 Posts

Posted - 2009-07-31 : 11:10:31

Hello all. I have a database where each record has a username, and 20 categories (rows). Well, each category can have 1 of 3 possible values (Safe, AR, NO). I need to query the whole table and first query by the specified user, then count how many values are Safe, how many are AR, and how many are NO. In other words, I don't need a count for each row, I need a count for ALL rows (any instance of each value). Is this possible with one query, or do I need to have multiple. I don't need to show the data, just the resulting counts. I hope this makes sense.

Here is a crude example:


       Col1   Col2   Col3  Col4
Row1   Safe    AR     AR   Safe
Row2    NO     AR    Safe   NO
Row3   Safe    Safe   NO    NO

So the results I want are:
Safe = 5
AR = 3
NO = 4

Thanks,

Mike

YevS
Starting Member

3 Posts

Posted - 2009-08-06 : 10:31:51

It won't be pretty...




SELECT DCount("row","test","col1 = 'AR'")+DCount("row","test","col2 = 'AR'")+DCount("row","test","col3 = 'AR'")+DCount("row","test","col4 = 'AR'") AS AR, 
DCount("row","test","col1 = 'no'")+DCount("row","test","col2 = 'no'")+DCount("row","test","col3 = 'no'")+DCount("row","test","col4 = 'no'") AS [NO], 
DCount("row","test","col1 = 'safe'")+DCount("row","test","col2 = 'safe'")+DCount("row","test","col3 = 'safe'")+DCount("row","test","col4 = 'safe'") AS Safe
FROM test
GROUP BY DCount("row","test","col1 = 'AR'")+DCount("row","test","col2 = 'AR'")+DCount("row","test","col3 = 'AR'")+DCount("row","test","col4 = 'AR'"), 
DCount("row","test","col1 = 'no'")+DCount("row","test","col2 = 'no'")+DCount("row","test","col3 = 'no'")+DCount("row","test","col4 = 'no'"),
DCount("row","test","col1 = 'safe'")+DCount("row","test","col2 = 'safe'")+DCount("row","test","col3 = 'safe'")+DCount("row","test","col4 = 'safe'");

parallon
Starting Member

25 Posts

Posted - 2009-08-06 : 17:15:14

Maybe not pretty, but if it works, it will be prettier than my 60 queries (3 possible results for 20 columns). ;o(

I will give this a try...

Thanks for all your time,

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources