Query help?

[Noozer]

My table:

REP text: 4 characters
One text: 10 characters
Two text: 10 characters
Done boolean: 1 byte

What I would like to return from a query:

For each unique REP I want to return:
REP
Count of rows where ONE and TWO are both blank.
Count of rows where ONE is not blank and TWO is blank.
Count of rows where ONE and TWO are both not blank.
Coung of rows where Done is TRUE.

I'll be the first to accept that I suck when it comes to SQL... Can someone
help me out here?

Thanks!

 

[GPO]

This might be getting close to what you want:

SELECT
Rep,
IIf(IsNull([One]),"Yes","No") AS [Is One Blank?],
IIf(IsNull([Two]),"Yes","No") AS [Is Two Blank?],
Count(*) AS [Count]
FROM
Codes
GROUP BY
Rep,
IIf(IsNull([One]),"Yes","No"),
IIf(IsNull([Two]),"Yes","No");

GPO

 

[John Spencer (MVP)]

SELECT REP,
Abs(Sum([One Text] Is Null and [Two Text] is Null)) as BothBlank,
Abs(Sum([One Text] Is Not Null and [Two Text] is Null)) as TwoBlank,
Abs(Sum([One Text] Is Null and [Two Text] is Not Null)) as OneBlank,
Abs(Sum([One Text] Is Not Null and [Two Text] is Not Null)) as Both,
Abs(Sum(Done) As NumDone
FROM [Your Table Name]
GROUP BY REP

If you are doing this in the grid and cannot translate the above, post back

 

[Dale Fye]

Noozer,

How about

Select REP,
COUNT(IIF(LEN([ONE] & "") = 0 AND LEN([Two] & "") = 0, 1, 0)) as
BothBlank,
COUNT(IIF((LEN([ONE] & "") > 0 AND LEN([Two] & "") = 0), 1, 0) as
OneNotTwo,
COUNT(IIF((LEN([ONE] & "") > 0 AND LEN([Two] & "") >0), 1, 0) as
NeitherBlank,
SUM(ABS([Done])) as Done
FROM yourTable
GROUP BY REP

You indicated you wanted to count blanks, I take that to mean that you also
want to count NULL values. In that case, the best way to do this is to
check to see if the length of the field is 0, but if you pass the LEN()
function a NULL, it will return an error, so you have to concatenate (&) an
empty string ("") to the field.

HTH
Dale