Let me answer your question about why qryAnimals1 query won't also return a
count of animals for you. To get a count, you must use a "totals" query,
which aggregates records based on the GROUP BY clause in the query. If you
wanted to use qryAnimals1 as a "counting" query, you would have to
"sacrifice" the details about each animal in order to count how many animals
belong to a single contactID. That of course would defeat the purpose of the
query, which is to give you a list of the individual animals.
It's possible to get the count in the query as part of each record, but it
would be somewhat inefficient because it would require running a subquery
for each AnimalID, and the subquery would return the same value for all
animals belonging to a single ContactID... in other words, if 10 animals
belong to a ContactID, the subquery would run 10 times just to return the
same number. So I still think using a DCount query will be better for you.
Now, let's see which query is the best to use for this.
I've studied your qryAnimals1 and qryCountOfAnimals queries to see how the
WHRE clauses work.
To make it easier for me to "see", I've split out the WHERE clause for the
qryAnimals1 query this way:
(
((Animals.AnimalsID)=[natlcertresults].[animalsid])
AND
((Contacts.ContactID)=[natlcertresults].[contactid])
)
OR
(
((Animals.PrimaryOwner)=[natlcertresults].[contactid])
)
OR
(
((Contacts.ContactID)=[forms]![individuals]![contactid])
);
This shows that you're using three separate "WHERE" subclauses, all
separated by OR, with the first subclause having an AND for the first two
items. It appears that qryCountOfAnimals uses the same WHERE structure.
In this query, you're using a reference to the "Individuals" form to get a
ContactID value. If you use a subform's LinkChildFields and LinkMasterFields
properties, you can forgo a reference to the form and let the form/subform
setup handle it for you. However, in this case I'm not sure that letting the
form/subform setup do this for you will be good, because that "WHERE"
subclause is just one of three ways to get records returned to the subform,
and using the "Links" properties may exclude some records that you want to
include.
So, let me see if I'm understanding... you would want to return the count of
animals if the "ContactID" value on the form "Individuals" matches the
Animals.PrimaryOwner or the Contacts.ContactID in the query qryAnimals1 --
is this correct?
Try this as the DCount expression (all one line, so watch for
line-wrapping):
=DCount("*", "qryAnimals1", "[Animals].[PrimaryOwner]=" &
[Parent].[ContactID] & " OR [Contacts].[ContactID]=" & [Parent].[contactid])
The above DCount will return the count of records where the PrimaryOwner or
the ContactID in the query matches the value of the ContactID on the parent
form (Individuals).
You shouldn't need to use the qryCountOfAnimals query at all.
--
Ken Snell
<MS ACCESS MVP>
Stephanie said:
Ken, thanks! DCount didn't work, no doubt due to my set up. So here's
the
whole thing:
frmIndividuals is based on tblContacts (ContactID as PK).
sfrmAnimals is linked to frmIndividuals on ContactID and is based on a
qryAnimals1:
SELECT Animals.AnimalName, AnimalType.AnimalType, Animals.AnimalBreed,
Animals.DateOfBirth, Animals.Retired, Animals.Deceased, Animals.AnimalsID,
Nz([Contacts].[NickName],[Contacts].[FirstName]) & " " &
[Contacts].[LastName] AS [Member Name], Contacts.ContactID,
Animals.PrimaryOwner
FROM (AnimalType RIGHT JOIN Animals ON AnimalType.AnimalTypeID =
Animals.AnimalTypeID) INNER JOIN (Contacts INNER JOIN NatlCertResults ON
Contacts.ContactID = NatlCertResults.ContactID) ON Animals.AnimalsID =
NatlCertResults.AnimalsID
WHERE (((Animals.AnimalsID)=[natlcertresults].[animalsid]) AND
((Contacts.ContactID)=[natlcertresults].[contactid])) OR
(((Animals.PrimaryOwner)=[natlcertresults].[contactid])) OR
(((Contacts.ContactID)=[forms]![individuals]![contactid]));
this gives me all animals that have certified with the contact- when I'm
on
frmIndividuals sfrmAnimals displays the correct animals without neede to
enter ContactID. And for reasons that escape me, this query is unable to
also give me the animal count.
So I wrote another query (and have streamlined the whole process so
qryAnimals1 is the basis for sfrmAnimals and the qryCountOfAnimals):
SELECT Count(Animals1.AnimalsID) AS CountOfAnimalsID,
Count(Animals1.Retired) AS CountOfRetired, Count(Animals1.Deceased) AS
CountOfDeceased, [CountOfAnimalsID]-[CountOfRetired]-[CountOfDeceased] AS
Active, Animals1.ContactID
FROM Animals1
WHERE ((([Animals].[AnimalsID])=[natlcertresults].[animalsid]) AND
(([Contacts].[ContactID])=[natlcertresults].[contactid])) OR
((([Animals].[PrimaryOwner])=[natlcertresults].[contactid])) OR
((([Contacts].[ContactID])=[forms]![individuals]![contactid]))
GROUP BY Animals1.ContactID;
All of the underlying queries and the forms themselves give me the correct
information- contacts and animals. Now I'd like to be able to state how
many
animals each contact has certified with, displayed on Individuals form.
But I think you are correct, a subform for animal count gives heartburn- I
have to enter the ContactID over and over for each record. Perhaps you
can
better direct me to use DCount now that I have (I hope!) explained my set
up.
Thanks for your time.
< snipped >
