asked on

Not counting but not excluding fields in a query

I have a table (named "RosterStatus" for simplicity) that looks like the following:

UserID RosterX RosterY Case
5 10 20 1
5 10 20 1
5 15 20 2
5 15 0 2
17 20 0 1
17 20 0 1
17 20 0 1
17 25 1 2

I'm trying to set up a query that has five fields - the sum of RosterX, the sum of RosterY, and the number of non-zero elements for each of RosterX and RosterY, and case.

In this case, the result should look like:
UserID SumOfRosterX SumOfRosterY CountOfRosterX CountOfRosterY Case
5 20 40 2 2 1
5 30 20 2 1 2
17 60 0 3 0 1
17 25 1 1 1 2

It seems pretty straightforward - however, when I try to set up the criteria field in Access, it doesn't seem to have any effect. I tried adding "<> 0" under the criterion for "CountOfRosterX" and "CounterOfRosterY", but it still includes the zero entries in the count.

Anybody have any ideas?

Raynard7

I just recreated your table using Access 2k3

SELECT RosterStatus.UserID, Sum(RosterStatus.RosterX) AS SumOfRosterX, Sum(RosterStatus.RosterY) AS SumOfRosterY, Count(RosterStatus.RosterX) AS CountOfRosterX, Count(RosterStatus.RosterY) AS CountOfRosterY, RosterStatus.Case
FROM RosterStatus
GROUP BY RosterStatus.UserID, RosterStatus.Case
HAVING (((Sum(RosterStatus.RosterX))<>0) AND ((Sum(RosterStatus.RosterY))<>0) AND ((Count(RosterStatus.RosterX))<>0) AND ((Count(RosterStatus.RosterY))<>0));

was the SQL and this excluded any where the count or sum was 0

esotericlete

ASKER

This is what I had before - the problem is that the <> condition excludes the entire row. You SQL produces:

UserID SumOfRosterX SumOfRosterY CountOfRosterX CountOfRosterY Case
5 20 40 2 2 1
5 30 20 2 2 2
17 25 1 1 1 2

I don't want to include any "zeroes" in the count of any roster, but I don't want it to wipe out the entire row of data, since there is usually a non-zero value for RosterX in the same row that needs to be summed.

Raynard7

I am confused;

Can you give an example of what you actually want to see?

If you exclude on one field then the whole row will not be displayed - do you just want nothing to appear in stead of 0?

esotericlete

ASKER

There's an example in my original question.

I should rephrase - is it possible to "ignore" certain fields for the purposes of sums and counts, without ignoring the entire row of data?

Raynard7

In ignoring this you want to not include these values at all; ie
5 15 0 2
17 20 0 1
17 20 0 1
17 20 0 1

so they will not be included in the sum?

If so then you need to do this with a sub query or reference two queries.

If you require the SQL option ask.

But what I would do is create one query - qRosterStatus which selects all the values from RosterStatus where RosterX and / or RosterY is not 0

I would then reference qRosterStatus instead of the RosterStatus table in the query you have used above.

This I think would acheive what you want

esotericlete

ASKER

If a zero is present, that value should not be included in the count (the sum doesn't matter, since it wouldn't add to the total anyhow). If it's not posible to set this up without excluding the rest of the row, what method would you recommend?

If you have a query, or a criteria method for Access, that would also be great.

Raynard7

what you could do is a sum rather than a count;

you could have sum(IIF(RosterY = 0, 0, 1)) which would prevent it from being counted.

This says if it is 0 do not count otherwise add all the values (1 for each) that are present.

esotericlete

ASKER

I see - where do I put the condition in Access? Does it belong in Field, Criteria, or elsewhere?

ASKER CERTIFIED SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

esotericlete

ASKER

Great, thanks for your help and sticking around to have things cleared up.