This works:
SELECT ABCName, '' ,Count(win) as num
from tblWinners
where win = 'abc'
group by ABCName
UNION
SELECT '' , NOPName, Count(win) as num
from tblWinners
where win = 'nop'
group by NOPName
Note that caty - o will not appear in the above union. To get caty - 0 in
the you will need to add some more queries. Finally getting this query:
/* This query returns records for ABCName which have atleast one win='abc'*/
SELECT ABCName, '' ,Count(win) as num
from tblWinners
where win = 'abc'
group by ABCName
UNION
/* This query returns records for NOPName which have atleast one win='nop'*/
SELECT '' , NOPName, Count(win) as num
from tblWinners
where win = 'nop'
group by NOPName
UNION
/* This query returns records for ABCName which have zero win='abc' i.e
caty - 0 since caty is the ABCName with only win=nop */
SELECT ABCName, '' , 0
from tblWinners
where ABCName NOT IN (SELECT ABCName from tblWinnersA where A.win = 'abc')
group by abcname
UNION
/* This query returns records for NOPName which have zero win='nop' i.e
nothing in this case as there are no records where a NOPName has only abc
win*/
SELECT ABCName, '' , 0
from tblWinners
where ABCName NOT IN (SELECT ABCName from tblWinnersA where A.win = 'nop')
group by abcname
I do not know how efficiently this query can be written.
Kedar Desai
Differential Technologies
Fairfax, VA
-----Original Message-----
From: Ben Forta [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 03, 2000 8:51 AM
To: CF-Talk
Subject: RE: Help building simply sql query expression
Try this:
SELECT ABCName, COUNT(*) AS num
FROM tblWinners
WHERE Win = 'abc'
GROUP BY ABCName
-----Original Message-----
From: Paul Sinclair [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 03, 2000 8:42 AM
To: CF-Talk
Subject: Help building simply sql query expression
I've been struggling with this off and on over the last couple days trying
to figure out how to do some calculations on an MSAccess db. I've looked
through all my sql books but haven't hit on the answer. It is very simple,
but my knowledge of sql is just too limited I'm afraid.
Basic table structure involved is:
tblWinners
ABCName..NOPName..Win
Adam.....Nick.....abc
Bart.....Otto.....abc
Caty.....Pete.....nop
Adam.....Nick.....abc
Bart.....Otto.....nop
Caty.....Pete.....nop
Adam.....Nick.....nop
I need to figure out the sql query that will run through all records in the
table for each "ABCName" and count the number of times "abc" shows up in the
"Win" column. For the above sample, the query results would be:
Adam - 2
Bart - 1
Caty - 0
Ideally I would like a query that would figure it out for both ABCName and
NOPName (count "abcname" with abc under "win" and "nopname" with nop under
"win") but if this is not easily done, I can run two different queries, one
for each. But if possible I'd like to get query results like this:
Adam - 2
Bart - 1
Caty - 0
Nick - 1
Otto - 1
Pete - 2
Thanks for any help.
Paul Sinclair
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
