esotericlete
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?
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?
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.
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.
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?
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?
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?
I should rephrase - is it possible to "ignore" certain fields for the purposes of sums and counts, without ignoring the entire row of data?
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
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
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.
If you have a query, or a criteria method for Access, that would also be great.
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.
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.
ASKER
I see - where do I put the condition in Access? Does it belong in Field, Criteria, or elsewhere?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, thanks for your help and sticking around to have things cleared up.
ASKER
Actually, a quick final question for an Access newbie - why are the square brackets necessary in this condition?
Square brackets mean that you are addressing a field from a table in access - if you do not use them sometimes it gets confused.
ASKER
Got it, I think I can take it from here now. Thanks again.
SELECT RosterStatus.UserID, Sum(RosterStatus.RosterX) AS SumOfRosterX, Sum(RosterStatus.RosterY) AS SumOfRosterY, Count(RosterStatus.RosterX
FROM RosterStatus
GROUP BY RosterStatus.UserID, RosterStatus.Case
HAVING (((Sum(RosterStatus.Roster
was the SQL and this excluded any where the count or sum was 0