If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
create new expression?field? in a query based on two fields
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 |
#2
|
|||
|
|||
create new expression?field? in a query based on two fields
On Sat, 21 Jan 2006 15:33:01 -0800, "Abby"
wrote: 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] |
#3
|
|||
|
|||
create new expression?field? in a query based on two fields
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]) |
#4
|
|||
|
|||
create new expression?field? in a query based on two fields
Thank you for your help. The cross-tab query worked!!!
"John Spencer" wrote: 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]) |
#5
|
|||
|
|||
create new expression?field? in a query based on two fields
Thank you for your help. The cross-tab query worked!!!
"John Vinson" wrote: On Sat, 21 Jan 2006 15:33:01 -0800, "Abby" wrote: 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] |
#6
|
|||
|
|||
create new expression?field? in a query based on two fields
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. -- Duane Hookom MS Access MVP -- "Abby" wrote in message news 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form based multi-parameter query & null fields | Estevan | Running & Setting Up Queries | 2 | November 30th, 2005 02:27 PM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Top 5 Query based on 3 fields, not 1 | PZ Straube | Running & Setting Up Queries | 3 | March 8th, 2005 10:05 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |