Selecting REMAINING records by excluding others

[Guest]

I have donors and their individual donations in separate tables (1 to many relationship). I would like to create a query that includes only those donors who have not given after Nov. 1, 2003. I have tried several different ways and I'm not getting the results that I want. There are some donors who have given before and after that date and I want to exclude them from my list. The key in the donor table is donor-id. The key in the individual donations table is donor-id and trans-date

I have tried creating 2 queries - one that summarizes all donors and one that summarizes the donors after nov. 1, 2003. Then I tried to exclude the overlapping records by selecting all the donors who do not match the donors in the second query. Doesn't work

I tried updating a field in the donor record with an update query based on the results of the donors after nov. 1 2003 query results, but I get an error that says I can't use an update query on a record on the 1 side of a 1 to many relationship

Any ideas? This is driving me nuts

Thx.

 

[Arvi Laanemets]

Hi

Something like
SELECT a.* FROM YourTable AS a WHERE a.keyfield NOT IN (SELECT b.keyfield
FROM YourTable WHERE given>=yourdate)


--
Arvi Laanemets
(When sending e-mail, use address (e-mail address removed))

I have donors and their individual donations in separate tables (1 to many

relationship). I would like to create a query that includes only those
donors who have not given after Nov. 1, 2003. I have tried several
different ways and I'm not getting the results that I want. There are some
donors who have given before and after that date and I want to exclude them
from my list. The key in the donor table is donor-id. The key in the
individual donations table is donor-id and trans-date.

I have tried creating 2 queries - one that summarizes all donors and one

that summarizes the donors after nov. 1, 2003. Then I tried to exclude the
overlapping records by selecting all the donors who do not match the donors
in the second query. Doesn't work.

I tried updating a field in the donor record with an update query based on

the results of the donors after nov. 1 2003 query results, but I get an
error that says I can't use an update query on a record on the 1 side of a 1
to many relationship.

 

[John Viescas]

PMJI.

You are missing a FROM clause right after the SELECT. Try this:

SELECT [Donor-Registration].[First Name(s)], [Donor-Registration].[Last
Name], Sum([Individual Donations].Amount), [Donor-Registration].[Donor-Reg
Id] AS [Donor-Registration]
FROM ([Donor-Registration] INNER JOIN [Individual Donations]
ON [Donor-Registration].[Donor-Reg ID] = [Individual
Donations].[Donor-Registration])
LEFT JOIN (SELECT [Donor-Registration] FROM [Individual Donations]
WHERE [Trans-Check Date] > #1 Nov 2003#) As NewD
ON [Donor-Registration].[Donor-Reg ID] = NewD.[Donor-Registration]
WHERE NewD.[Donor-Registration] Is Null
GROUP BY [Donor-Registration].[First Name(s)], [Donor-Registration].[Last
Name],
[Donor-Registration].[Donor-Reg Id];

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

This is the code I am using and it's telling me I'm using a reserved word

or argument name which is mispelled or missing or the punctuation is
incorrect. What am I doing wrong here? Thx.

SELECT DISTINCT [Donor-Registration].[First Name(s)],

[Donor-Registration].[Last Name], Sum([Individual Donations].Amount),
[Donor-Registration].[Donor-Reg Id] AS [Donor-Registration]

WHERE [Donor-Registration].[Donor-Reg Id] NOT IN (SELECT [Individual

Donations].[Donor Id] FROM [Donor-Registration] WHERE [Individual
Donations].[Trans-Check Date]>#11/1/2003#)

FROM [Donor-Registration] LEFT JOIN [Individual Donations] ON

[Donor-Registration].[Donor-Reg Id] = [Individual Donations].[Donor Id]

GROUP BY [Donor-Registration].[First Name(s)], [Donor-Registration].[Last

Name], [Donor-Registration].[Donor-Reg Id]

HAVING (((Sum([Individual Donations].Amount))>0))
ORDER BY [Donor-Registration].[Last Name];