Summing by Year. 1

[CoolFactor]

In the design view of my query I have field named contributions and I want to sum this by year in the Group by column. I don't know what the expression would be for this.

 

[dhookom]

Do you have a year or date field? Please share your table structure. I expect your sql might look like

SELECT Year([ContributionDate]) as Yr, Sum([Contribution]) As SumContribution
FROM tblContributions
GROUP BY Year([ContributionDate]);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PWise]

how about

year(DateOfContributions )

 

[CoolFactor]

Here is the what the SQL code looks like:

SELECT [FirstName] & " " & [LastName] AS Expr1, [Party] & "s" AS Expr2, tbl_Candidates.State, tbl_Candidates.District, tbl_Candidates.Office, [tbl_Candidates Committees].Committee, [tbl_Candidates Funds].Fund, [FundType] & "s" AS Expr3, [tbl_Candidates Funds].Year, tbl_Ledger.Date, tbl_Ledger.[Contributions/Disbursements]
FROM (tbl_Candidates INNER JOIN [tbl_Candidates Committees] ON tbl_Candidates.CandidateID = [tbl_Candidates Committees].CandidateID) INNER JOIN ([tbl_Candidates Funds] INNER JOIN tbl_Ledger ON [tbl_Candidates Funds].FundID = tbl_Ledger.CandidateFund) ON tbl_Candidates.CandidateID = [tbl_Candidates Funds].CandidateID
ORDER BY [tbl_Candidates Funds].Year;

 

[dhookom]

What are you asking based on your SQL? Do you want annual totals by Committee or State or Fund or Office or Person or what?

I didn't see your "field named contributions".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CoolFactor]

Actually the field's name is Contributions/Disbursements. What I want to do is sum the Contributions/Disbursements given to each candidate by year. For example:

Let's just say I gave Jesse Smith $2000 on 5/19/2005 and then I gave her $5000 on 7/12/2005. In the query I want to sum both of these contributions I made to Jesse Smith by Year.

Let me know if this makes a little more sense.

 

[dhookom]

SELECT [FirstName] & " " & [LastName] AS Candidate, [tbl_Candidates Funds].Year, Sum(tbl_Ledger.[Contributions/Disbursements])
FROM (tbl_Candidates INNER JOIN [tbl_Candidates Committees] ON tbl_Candidates.CandidateID = [tbl_Candidates Committees].CandidateID) INNER JOIN ([tbl_Candidates Funds] INNER JOIN tbl_Ledger ON [tbl_Candidates Funds].FundID = tbl_Ledger.CandidateFund) ON tbl_Candidates.CandidateID = [tbl_Candidates Funds].CandidateID
GROUP BY [FirstName] & " " & [LastName], [tbl_Candidates Funds].Year
ORDER BY [tbl_Candidates Funds].Year;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CoolFactor]

Thank You Duane. I really appreciate it.