Access queries

[Lingam]

from two different tables i.e Donor Demographic, Donation
Particulars,I am trying to get data of Rh Negative Donors
who have donated blood more than three months ago,I am
getting duplicate data because some of the donors have
donated more than once.The field Property set to Unique-
yes does not solve the problem.

1.how to design so that the query does not print
duplicate values.

2.Only donors who have donated more than three months ago
to be shown.

 

[Michel Walsh]

Hi,


Make three queries, one with the list of donors with negative Rh; use a
DISTINCT if required. The second query is the list of donors having given in
the last three month ( a simple WHERE clause on the date).


The third query? Run the query wizard about finding unmatched records:
those in the first query not in the second query.


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

One method would be SQL that looked something like the following:


SELECT DD.DonorName, DP.DonationDate
FROM [Donor Demographic] as DD INNER JOIN
[Donor Particulars] as DP
On DD.DonorId = DP.DonorID
WHERE DP.DonationDate <= DateAdd("m",-3,Date())
AND Not Exists
(SELECT DP1.DonorID
FROM [Donor Particulars] as DP1
WHERE DP1.DonationDate > DateAdd("m,-3,Date()
AND DP1.DonorID = DD.DonorID)
AND DD.BloodType = "RH-"