Excluding information from queries

[scott5]

Hello,
I am trying to run a query using two different fields which both contain
dates. I am running the query to determine the total number of individuals
which appear in either of the two fields in between two dates. The problem is
that I am not sure how to set up the query so that I am able to obtain this
information without having those individuals with dates in both fields
counted twice. (Just to make sure that this is clear: I have been collecting
information concerning individuals who will be participating in a study.
Depending on the circumstances, the individual themself might consent to
participate or their legal guardian might consent for them - or the
circumstances might require that they each consent. Hence, when I run the
query to find out the total # of consents that we have for an individual ...
I run into the problem of having the cases where we have consent from both
the individual and their guardian counted twice.) To further complicate the
issue, I obviously also require that other criteria is included in the query
(ie. only dates in between date 'a' and 'b' should be counted ... and also
the fact that I am asking the query to add something at all.) I am not great
at access, and help would be extremely appreciated.

Thank you very much!

 

[KARL DEWEY]

Use your table name instead of [Change Requests] and your field names.

SELECT [Change Requests].x, Count([Change Requests].x) AS CountOfx
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between [Enter start date] And
[Enter end date])) OR ((([Change Requests].[Date close]) Between [Enter low
date] And [Enter high date]))
GROUP BY [Change Requests].x
HAVING ((([Change Requests].x)="Consent")) OR ((([Change
Requests].x)="Consent"));