I have a query that counts the number of animals a person has. I would like
to only display those where count is equal to 5. I tried putting = 5 on the
criteria line of the field that is being counted, but that doesn't do
anything. Tried putting Count = 5 in the criteria line of the field being
counted, but that gets a 'data mismatch' error. How and where would I do
this
in the query?
///////////////////
The following is the SQL..is that what you want?
This doesn't have the 'checking for 5' in it. Just the basic counting which
works. I just don't know how to check for a count of 5 and only display
those
records.
SELECT
DISTINCTROW
Count(tblAnimals.AnimalID) AS CountOfAnimalID,
tblAnimals.OwnerID_AnimalTbl,
tblOwners.OwnerID,
tblOwners.First,
tblOwners.Last
FROM
tblOwners
INNER JOIN
tblAnimals
ON
tblOwners.OwnerID = tblAnimals.OwnerID_AnimalTbl
GROUP BY
tblAnimals.OwnerID_AnimalTbl,
tblOwners.OwnerID,
tblOwners.First,
tblOwners.Last,
tblAnimals.AnimalDeceased,
tblAnimals.AnimalMoved
HAVING
tblAnimals.AnimalDeceased=False
OR
tblAnimals.AnimalMoved=False
////////////////////////////////////
try this:
SELECT
Count(tblAnimals.AnimalID) AS CountOfAnimalID,
tblAnimals.OwnerID_AnimalTbl,
tblOwners.OwnerID,
tblOwners.First,
tblOwners.Last
FROM
tblOwners
INNER JOIN
tblAnimals
ON tblOwners.OwnerID = tblAnimals.OwnerID_AnimalTbl
GROUP BY
tblAnimals.OwnerID_AnimalTbl,
tblOwners.OwnerID,
tblOwners.First,
tblOwners.Last,
tblAnimals.AnimalDeceased,
tblAnimals.AnimalMoved
HAVING
(tblAnimals.AnimalDeceased=False
OR
tblAnimals.AnimalMoved=False)
AND
(Count(*)=5);
///////////////////////////////////////
....that did it....
What is the (*) in the last Count?
And, if you count something in the design grid in a query,
you can then use the word Count as a field to refer back to
in the query like this did?
//////////////////////////////////////////
Hi Randy,
I don't believe your problem had anything
to do with "Count(*) =5" vs "Count(AnimalID)=5"
I suspect you had something like the following
grid when it "didn't do anything."
Field: AnimalID AnimalDeceased AnimalMoved
Table: tblAnimals tblAnimals tblAnimals
Total: Count Group By Group By
Sort:
Show:
Criteria: 5 False
or: False
or:
Setting multiple criteria on the grid is not an easy thing
to grasp early on.
For each Criteria/Or line, all the entries across that line are "ANDed"
"ORing" happens between between the lines
So the above is "saying"
{count = 5 and Deceased=False} or {Moved=False}
If Moved=False, it is going to ignore the count=5 filter!
Is that what you meant by "didn't do anything?"
My guess is that the following would work just as
well as the query with Count(*) in it.
Field: AnimalID AnimalDeceased AnimalMoved
Table: tblAnimals tblAnimals tblAnimals
Total: Count Group By Group By
Sort:
Show:
Criteria: 5 False
or: 5 False
or:
when you save the query, Access will always try to change
{count = 5 and Deceased=False}
or
{count = 5 and Moved=False}
to something it thinks is more efficient:
HAVING
(tblAnimals.AnimalDeceased=False
OR
tblAnimals.AnimalMoved=False)
AND
(Count(tblAnimals.AnimalID)=5);
and Access will be correct in this instance,
but you should always check your SQL View
if you have designed a complicated filter.
Access does not always know better than you
and can sometimes completely change the
logic of the filter.
So...(if I am thinking correctly)
it had nothing to do with Count(*);
it had to with understanding how
the grid works.
BTW, whenever you use "*" in any aggregate
function like Count, it will *not* ignore the Nulls.
My guess is that in your situation, there were
no Null AnimalID's in each group, so the two
"forms" were equivalent.
Count(*) would return the same values as
Count(tblAnimals.AnimalID)
Another thing to think about in group-by-query
grid design is that anything placed in Criteria/or
lines will show up in the HAVING clause.
This filtering happens *after* all the grouping
and aggregates have been processed. On a
large set of data, this can be expensive.
To filter before the grouping, you may need to
introduce a field again in another column of the
grid, and choose "Where" in the Totals row.
My guess is that you could have added two
new columns for Deceased and Moved, set
their Totals row to Where, Criteria row to False,
and removed False under their Group By columns
(so no need for second "5" under AnimalID).
FROM
tblOwners
INNER JOIN
tblAnimals
ON tblOwners.OwnerID = tblAnimals.OwnerID_AnimalTbl
WHERE
(tblAnimals.AnimalDeceased=False
OR
tblAnimals.AnimalMoved=False)
GROUP BY
tblAnimals.OwnerID_AnimalTbl,
tblOwners.OwnerID,
tblOwners.First,
tblOwners.Last,
tblAnimals.AnimalDeceased,
tblAnimals.AnimalMoved
HAVING
Count(tblAnimals.AnimalID)=5;
For other data, the different filters could
produce different counts, so you have to
think about it.
WHERE will set up whats in the groups...
HAVING will set up what groups to return...
