OS and Software > Microsoft Help > [Access] I need help creating a "Grand Total" column

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

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

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

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.

reply to natedj
Have you looked at this yet? It should get you going in the right direction. »groups.google.com/forum/?fromgro···PKNYdw2k

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;

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.

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

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;

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

Formats in queries are not important. Create a Report of the query and then format the columns that way.

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

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;