create new expression?field? in a query based on two fields

A

Abby

HI-
I have two tables 1. Donor information (with fields: ID, last name, first
name) and 2. Donations (with fields: ID, gift date, gift amount). I have set
up a one to many relationship between the two, where there can only be one
donor but that donor can make several donations. I am trying to generate a
report that list all the donors down the left hand column, and then their
donations by year across. To do this I think I need to create new fields
(i.e. gift98, gift99, gift00, etc) and these fields would be calculated based
on the two fields Gift Date and Gift Amount. For example, if John Smith made
a gift of $300 on 5/5/2003 his gift03 value would be $300. I think it would
be something like this:
Cash98: (SELECT [Gift Amount] FROM [Donations] WHERE [Donations].[Gift Date]
Between #1/1/1998# And #1/1/1999#)
BUT this doesn't work...any help would be greatly appreciated!!!!
Thanks
 
J

John Vinson

I have two tables 1. Donor information (with fields: ID, last name, first
name) and 2. Donations (with fields: ID, gift date, gift amount). I have set
up a one to many relationship between the two, where there can only be one
donor but that donor can make several donations. I am trying to generate a
report that list all the donors down the left hand column, and then their
donations by year across. To do this I think I need to create new fields
(i.e. gift98, gift99, gift00, etc) and these fields would be calculated based
on the two fields Gift Date and Gift Amount. For example, if John Smith made
a gift of $300 on 5/5/2003 his gift03 value would be $300. I think it would
be something like this:

A Crosstab query is designed to do exactly what you're asking. Try the
Crosstab Query wizard; if it doesn't work for you, post back and
explain what you're getting vs. what you want.

John W. Vinson[MVP]
 
J

John Spencer

SELECT DI.[Last Name], DI.[First Name]
, Sum(D.[Gift Amount]), Year(D.[Gift Date])
FROM [Donor Information] as DI INNER JOIN Donations as D
On DI.ID=D.[ID]
GROUP BY DI.ID, [Last Name], [First Name], Year(D.[Gift Date]

IF you want to turn that into a crosstab query, then it would probably look
something like:

TRANSFORM Sum(D.[Gift Amount]) AS TotalGift
SELECT DI.ID, DI.[Last Name], DI.[First Name]
FROM [Donor Information] as DI INNER JOIN Donations as D
On DI.ID=D.[ID]
GROUP BY DI.ID, [Last Name], [First Name]
PIVOT Year(D.[Gift Date])
 
D

Duane Hookom

Another option is to create a query of the last X years.
SELECT ID,
Sum(Abs(DateDiff("yyyy", [gift date], Date())=0) *[Gift Amount]) as
ThisYear,
Sum(Abs(DateDiff("yyyy", [gift date], Date()))=1) *[Gift Amount]) as
LastYear,
Sum(Abs(DateDiff("yyyy", [gift date], Date()))=2) *[Gift Amount]) as
TwoYearsAgo,
Sum(Abs(DateDiff("yyyy", [gift date], Date()))=3) *[Gift Amount]) as
ThreeYearsAgo
From Donations
GROUP BY ID;

This method create standard column headings that can be used in reports.

A crosstab method with static column headings would be:
TRANSFORM Sum(D.[Gift Amount]) AS TotalGift
SELECT DI.ID, DI.[Last Name], DI.[First Name]
FROM [Donor Information] as DI INNER JOIN Donations as D
On DI.ID=D.[ID]
GROUP BY DI.ID, [Last Name], [First Name]
PIVOT "Yr" & DateDiff("yyyy",D.[Gift Date], Date()) IN
("Yr0","Yr1",Yr2","Yr3",....);

Both methods create column headings that can be easily bound in reports.
 

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