Different criteria for same field in same query

[teirrah1995]

I have a field "Data". I want to count the number of records where "Data" is 1, and count the number of records where "Data" is 2. I can do each of these fine separately, but I don't know how to do them in the same query.

I would like:

Data
2
1
2
1
2

To come up as:

Count of 1|Count of 2
2|3

[boblarson]

In the same query (in the QBE Grid you can use something like this):

Count Of 1: Sum(IIf([Data]=1, 1, 0))


and then for the next field

Count Of 2: Sum(IIf([Data]=2, 1, 0))

[teirrah1995]

Thanks for the reply! I hadn't heard of Sum(Iif()) but it sounds like what I want.
What you have suggested doesn't work, but I think that might be because the criterion isn't just a value. In my post I said "1" instead of "Between FirstDay(Date()) And LastDay(Date())" to (try to) keep it simple. When I enter:
Count Of 1: Sum(IIf([Data]=Between FirstDay(Date()) And LastDay(Date()), 1, 0))
it tells me off for a syntax error.
Thanks again for the reply.

[boblarson]

You can't do it like that. Is Data actually a date field? If so, you would need to include the data field as a separate field in your query BESIDES what I gave you and then use your Between ... as the criteria on that field.

[teirrah1995]

That's what I have at the moment, but then I have a second criterion, similar to 'Between FirstDay(Date()) And LastDay(Date())' If I try to make two query fields with the same data field, "Data", they get put into one field when I close and re-open.

[boblarson]

Why do you have two criteria for the field? Is it an OR type of situation where you want it if it is between this date and this other date OR between a completely other set of dates? So can you post the SQL that is coming out of it the way you have it set up so we can get an idea of what you have actually put in there?

[teirrah1995]

I thought making it into numbers would make it simpler. Guess not! ^^

SELECT Count(Patient.[Test Date]) AS Tested
FROM Patient
WHERE (((Patient.[Test Date]) Between FirstDay(Date()) And LastDay(Date())));

SELECT Count(Patient.[Test Date]) AS Tested Last Month
FROM Patient
WHERE (((Patient.[Test Date]) Between DateAdd("m",-1,FirstDay(Date())) AND DateAdd("m",-1,LastDay(Date())));

Is what I have at the moment, but as two queries.

[boblarson]

So do each of those queries work on their own? And you want to bring them together into a single query? Are there any other fields you want included to or just the counts?

[rpeare]

SELECT Sum(IIf([itemdate] Between #1/1/2011# And #1/7/2011# And [data]=1,1,0)) AS Item1Count1, Sum(IIf([itemdate] Between #1/1/2011# And #1/31/2011# And [data]=1,1,0)) AS Item1Count2, Sum(IIf([itemdate] Between #1/1/2011# And #1/7/2011# And [data]=2,1,0)) AS Item2Count1, Sum(IIf([itemdate] Between #1/1/2011# And #1/31/2011# And [data]=2,1,0)) AS Item2Count2
FROM Tbl_Test;

This is the query I used with this as my dataset:

Tbl_Test
ItemDate Data
1/1/2011 2
1/2/2011 1
1/15/2011 2
1/21/2011 1
1/30/2011 1

give it a try and see if it gives you want you want

[teirrah1995]

You can't do it like that.

Yes I can- that works perfectly! My problem was using Select.Count instead of Sum(IIf()). Thank you both for your help- my deadline's been shifted a few days closer (tomorrow!) so it's great that this is working now.

[teirrah1995]

... can you post the SQL that is coming out of it the way you have it set up so we can get an idea of what you have actually put in there?

I've also learnt a valuable lesson about trying to simplify a request