dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
4063
share rss forum feed


natedj
Elected
Premium
join:2001-06-06
Columbia, SC

[Access] I need help creating a "Grand Total" column

I'm a noob at access and have this database on donors that is very basic.
I ran a query that gave me the Donor's first and last name and a sum of each category they donated to, for a total of (5) columns.




How do I add a 6th column that would give a grand total of John Doe's donation? I'm using Access 2007.

TIA
--
Good judgement comes with experience...Experience comes after bad judgements


HolmanGT
HolmanGT
Premium
join:2001-11-20
Washington, UT

Re: [Access] I need help creating a "Grand Total" colu

Click for full size
Select the cells you want to sum plus one additional cell for the sum and click on the summation button... viola !!!

My example is Excel 2010 but they are all pretty much the same unless you really meant Access in which case I have no idea except to import the data base into Excell.

Good Luck


natedj
Elected
Premium
join:2001-06-06
Columbia, SC
This has to be done in Access.
I've been exporting to excel and creating my totals that way but it is getting tedious and I know I can eliminate a lot of steps If i can can get Access to display the totals for me.
--
Good judgement comes with experience...Experience comes after bad judgements


HolmanGT
HolmanGT
Premium
join:2001-11-20
Washington, UT
Oh-well nothing is ever easy.

I have played with Access years ago and I seem to remember that almost anything you can do in Excel can be done in Access.

Try doing a search for some templates that claim to do something close to what you need and reverse engineer it.

Nothing like a little plagiary or why reinvent the wheel.

PS - IMHO Access was never designed for mere mortals.


mjhouser
Premium
join:2001-10-06
reply to natedj
Have you looked at this yet? It should get you going in the right direction. »groups.google.com/forum/?fromgro···PKNYdw2k

Laramar

join:2002-08-23
Minneapolis, MN
reply to natedj
View the query in SQL mode, then add this in the SELECT line:
Food+Clothing+Shelter AS Total

it would look something like:
SELECT Firstname, Lastname, Food, Clothing, Shelter, Food+Clothing+Shelter AS Total
FROM table;


mbernste
Boosted
Premium,MVM
join:2001-06-30
Piscataway, NJ

1 recommendation

reply to natedj
Or in design mode for the query you can put the following in the field box:

Total:[food]+[clothing]+[shelter]

Then add the Total field to your report. It won't store the value in the database, but it will display it in your report. If you need the value to be stored you can either create a make table query or do it in VBA.


natedj
Elected
Premium
join:2001-06-06
Columbia, SC
reply to natedj
Actually, the totals shown under the different categories are pulled from a number of entries made by John Doe. In other words, John made about 200 different donations at different times during the year and the query that I ran summed up the total $ for each category.
I tried the suggestions above and I'm getting errors.

Here are screenshots of the different views.


Query in Datasheet View



Query in Design View


The Query in SQL mode:
SELECT DISTINCTROW Donations.FirstName, Donations.LastName, Sum(Donations.Food) AS [Sum Of Food], Sum(Donations.Clothing) AS [Sum Of Clothing], Sum(Donations.Shelter) AS [Sum Of Shelter]
FROM Donations
WHERE (((Donations.Date) Between #1/1/2012# And #12/31/2012#))
GROUP BY Donations.FirstName, Donations.LastName;


--
Good judgement comes with experience...Experience comes after bad judgements

Laramar

join:2002-08-23
Minneapolis, MN

1 recommendation

Add:
[Sum Of Food]+[Sum Of Clothing]+[Sum Of Shelter] AS GrandTotal

The query will look like:

SELECT DISTINCTROW Donations.FirstName, Donations.LastName, Sum(Donations.Food) AS [Sum Of Food], Sum(Donations.Clothing) AS [Sum Of Clothing], Sum(Donations.Shelter) AS [Sum Of Shelter], [Sum Of Food]+[Sum Of Clothing]+[Sum Of Shelter] AS GrandTotal
FROM Donations
WHERE (((Donations.Date) Between #1/1/2012# And #12/31/2012#))
GROUP BY Donations.FirstName, Donations.LastName;


natedj
Elected
Premium
join:2001-06-06
Columbia, SC

1 edit
said by Laramar:

Add:
[Sum Of Food]+[Sum Of Clothing]+[Sum Of Shelter] AS GrandTotal

The query will look like:

SELECT DISTINCTROW Donations.FirstName, Donations.LastName, Sum(Donations.Food) AS [Sum Of Food], Sum(Donations.Clothing) AS [Sum Of Clothing], Sum(Donations.Shelter) AS [Sum Of Shelter], [Sum Of Food]+[Sum Of Clothing]+[Sum Of Shelter] AS GrandTotal
FROM Donations
WHERE (((Donations.Date) Between #1/1/2012# And #12/31/2012#))
GROUP BY Donations.FirstName, Donations.LastName;

I'm elated !!!! this works.
Thanks a million. Now my only issue is that the Grant total is only calculating fields where the donor gave in each category. If someone donated in Food and clothing only, the Grant Total column is not showing a total.
--
Good judgement comes with experience...Experience comes after bad judgements


natedj
Elected
Premium
join:2001-06-06
Columbia, SC
reply to natedj
Okay I tried the NZ function and got MS Access to calculate the blank fields as well, but the resulting format is not the same.
Instead to showing $1234.00 it would show the total as 1234
Any ideas on how to fix this one?
--
Good judgement comes with experience...Experience comes after bad judgements

Laramar

join:2002-08-23
Minneapolis, MN
Formats in queries are not important. Create a Report of the query and then format the columns that way.


natedj
Elected
Premium
join:2001-06-06
Columbia, SC
reply to natedj
I'm doing a Mail Merge from the query in to MS Word to print out some letters, not creating a report in Access. Can a Mail Merge work with a report too?
--
Good judgement comes with experience...Experience comes after bad judgements

Laramar

join:2002-08-23
Minneapolis, MN
I don't the mail merge works with reports. You could create a Make Table query (Design->Make Table) that will store the query in a new table. Then use mail merge with the new table.

Copy the query you made into a new query and use the make table function. In SQL, it would simply add 'INTO newtable'. It would look something like:

SELECT DISTINCTROW Donations.FirstName, Donations.LastName, Sum(Nz(Donations.Food,0)) AS [Sum Of Food], Sum(Nz(Donations.Clothing,0)) AS [Sum Of Clothing], Sum(Nz(Donations.Shelter,0)) AS [Sum Of Shelter], [Sum Of Food]+[Sum Of Clothing]+[Sum Of Shelter] AS GrandTotal
INTO newtable
FROM Donations
WHERE (((Donations.Date) Between #1/1/2012# And #12/31/2012#))
GROUP BY Donations.FirstName, Donations.LastName;