Totalling Selected Sums

[Confused87]

I’m working on a charity database. Their members hold events. These events
raise projected amounts of money and they give it to us in stages.

At the moment there is a record per amount of money they give in. This is so
I can run a calculation through a query which tells me who has money
outstanding. The table feeds on to the event hosts member form(the overdue
funds query show on a separate form) and is linked via their New ID number.
It looks something like this:

Donation ID, Donation Amount, Event ID, Event Name, New ID, Host Name… et
cetera
1 100 1 Car Wash 56 J Smith
2 100 1 Car Wash 56 J Smith
3 100 1 Car Wash 56 J Smith
4 100 1 Car Wash 56 J Smith
5 100 1 Car Wash 56 J Smith
6 1500 2 Dinner 56 J Smith
7 2000 2 Dinner 56 J Smith
8 1000 2 Dinner 56 J Smith


This shows 2 events run by one person as it shows up on their profile
screen. In the table view it would have all event holders, not just member
56, J Smith. I want this view the table to show all the donations(as it does
currently), but for the profile view I need to have one row so, for example,
in 3 years time when they have held 10 events, there are 10 records not 50 or
so.

It would look something like this:

Total Amount, Event ID, Event Name, New ID, Host Name… et cetera
500 1 Car Wash 56 J Smith
4500 2 Dinner 56 J Smith


Many Thanks

 

[KC-Mass]

Something like:
SELECT [Donation ID], [Event ID], [New ID],
[Host Name], Sum([Donation Amount)) as TotalDonations
GROUP BY [Donation ID], [Event ID], [New ID],
[Host Name]

Regards
Kevin

 

[KC-Mass]

It needs a "FROM" clause


Something like:
SELECT [Donation ID], [Event ID], [New ID],
[Host Name], Sum([Donation Amount)) as TotalDonations
FROM YourTableName
GROUP BY [Donation ID], [Event ID], [New ID],
[Host Name]

Regards

Kevin

 

[Confused87]

Where would I type this? In the table?

It needs a "FROM" clause


Something like:
SELECT [Donation ID], [Event ID], [New ID],
[Host Name], Sum([Donation Amount)) as TotalDonations
FROM YourTableName
GROUP BY [Donation ID], [Event ID], [New ID],
[Host Name]

Regards

Kevin

I'm working on a charity database. Their members hold events. These events
raise projected amounts of money and they give it to us in stages.

At the moment there is a record per amount of money they give in. This is
so
I can run a calculation through a query which tells me who has money
outstanding. The table feeds on to the event hosts member form(the overdue
funds query show on a separate form) and is linked via their New ID
number.
It looks something like this:

Donation ID, Donation Amount, Event ID, Event Name, New ID, Host Name. et
cetera
1 100 1 Car Wash 56 J Smith
2 100 1 Car Wash 56 J Smith
3 100 1 Car Wash 56 J Smith
4 100 1 Car Wash 56 J Smith
5 100 1 Car Wash 56 J Smith
6 1500 2 Dinner 56 J Smith
7 2000 2 Dinner 56 J Smith
8 1000 2 Dinner 56 J Smith


This shows 2 events run by one person as it shows up on their profile
screen. In the table view it would have all event holders, not just member
56, J Smith. I want this view the table to show all the donations(as it
does
currently), but for the profile view I need to have one row so, for
example,
in 3 years time when they have held 10 events, there are 10 records not 50
or
so.

It would look something like this:

Total Amount, Event ID, Event Name, New ID, Host Name. et cetera
500 1 Car Wash 56 J Smith
4500 2 Dinner 56 J Smith


Many Thanks

 

[John Spencer]

That is a query. You would enter it into the SQL view of a query.

If you only know how to use the design view (query grid) for
constructing a query.

-- Create a new query
-- Add your table
-- add the fields
-- select View: Totals from the menu
-- Change GROUP BY to SUM under the Donation Amount field


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Where would I type this? In the table?

It needs a "FROM" clause


Something like:
SELECT [Donation ID], [Event ID], [New ID],
[Host Name], Sum([Donation Amount)) as TotalDonations
FROM YourTableName
GROUP BY [Donation ID], [Event ID], [New ID],
[Host Name]

Regards

Kevin

I'm working on a charity database. Their members hold events. These events
raise projected amounts of money and they give it to us in stages.

At the moment there is a record per amount of money they give in. This is
so
I can run a calculation through a query which tells me who has money
outstanding. The table feeds on to the event hosts member form(the overdue
funds query show on a separate form) and is linked via their New ID
number.
It looks something like this:

Donation ID, Donation Amount, Event ID, Event Name, New ID, Host Name. et
cetera
1 100 1 Car Wash 56 J Smith
2 100 1 Car Wash 56 J Smith
3 100 1 Car Wash 56 J Smith
4 100 1 Car Wash 56 J Smith
5 100 1 Car Wash 56 J Smith
6 1500 2 Dinner 56 J Smith
7 2000 2 Dinner 56 J Smith
8 1000 2 Dinner 56 J Smith


This shows 2 events run by one person as it shows up on their profile
screen. In the table view it would have all event holders, not just member
56, J Smith. I want this view the table to show all the donations(as it
does
currently), but for the profile view I need to have one row so, for
example,
in 3 years time when they have held 10 events, there are 10 records not 50
or
so.

It would look something like this:

Total Amount, Event ID, Event Name, New ID, Host Name. et cetera
500 1 Car Wash 56 J Smith
4500 2 Dinner 56 J Smith


Many Thanks