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

[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

 

[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]

 

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

 

[Abby]

Thank you for your help. The cross-tab query worked!!!

 

[Abby]

Thank you for your help. The cross-tab query worked!!!

 

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