[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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?


0
esotericlete
Asked:
esotericlete
  • 7
  • 6
1 Solution
 
Raynard7Commented:
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
0
 
esotericleteAuthor Commented:
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.
0
 
Raynard7Commented:
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?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
esotericleteAuthor Commented:
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?
0
 
Raynard7Commented:
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
0
 
esotericleteAuthor Commented:
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.
0
 
Raynard7Commented:
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.
0
 
esotericleteAuthor Commented:
I see - where do I put the condition in Access? Does it belong in Field, Criteria, or elsewhere?
0
 
Raynard7Commented:
In the column for roster Y where you have a count condition "RosterY" with replace the below statement
CntRosterY: IIf([RosterY]=0,0,1)
The above should be in the top row (the field row)

Then change the condition from count - to sum in the "Total" row

Do the same thing for RosterX changing RosterY to RosterX for that column.
0
 
esotericleteAuthor Commented:
Great, thanks for your help and sticking around to have things cleared up.
0
 
esotericleteAuthor Commented:
Actually, a quick final question for an Access newbie - why are the square brackets necessary in this condition?
0
 
Raynard7Commented:
Square brackets mean that you are addressing a field from a table in access - if you do not use them sometimes it gets confused.
0
 
esotericleteAuthor Commented:
Got it, I think I can take it from here now. Thanks again.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now