Selecting REMAINING records by excluding others

M

Meryl

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.
 
A

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))


Meryl said:
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.
 
J

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)
Meryl said:
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];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top