Totals showing across bottom of Datasheet view

[Laurie B.]

I have three fields [2011 DIRECT$] + [2011 GPOS$] + [2011 SCT$] that I need to see the total of each records going across, not down on the bottom for all records.

I am using the Totals function on the design tab while I am in the Datasheet view which is giving me totals on the bottom of the results.

I've also tried creating a new field called Total 2011: [2011 DIRECT$] + [2011 GPOS$] + [2011 SCT$] and get the same results totalling down and not across.

Thank you in advanced for any suggestions you may have
LaurieB.

[Robeen]

Your New Field 'Total 2011' - it's in a query - right?

Can you post your SQL statement here?

[apr pillai]

To show the Total of field values across, add a Textbox in the Detail Section and write the expression: = [2011 DIRECT$] + [2011 GPOS$] + [2011 SCT$]

You can display all records total by doing the following trick:

• Create a Textbox in the Form Footer or Form Header area.
• Write the expression =Sum([2011 DIRECT$]) in the Control Source property. You may add Textboxes for other fields also.
• Change the Name Property value of the Textbox to TOTDIRECT
• Display the Property Sheet of the Form.
• Find and select the On Click Event property and select [EventProcedure] from the drop drown list.
• Click on the Build (...) button to open the Module with the empty Form_Click() Event Procedure.
• Write the following Code (or Copy and Paste) within the Event Procedure:

Dim strDisplay as string

Me.Refresh

strDisplay = "DIRECT Total: " & Format([TOTDIRECT],"0.00")
strDisplay = strDisplay & " GPOS Total: " & Format([TOTGPOS],"0.00")
strDisplay = strDisplay & " SCT Total: " & Format([TOTSCT],"0.00")

Me.Caption = strDisplay

• Save the Form with the code.
• Open the Form in Datasheet View.
• Click at the left border of any record, the Total values of all the three fields will be display on the Caption area (normaly where the Form name is shown) of the Form, replacing the form name.
• Make some change in the [2011 Direct$] field of any record and click on the border of the Form to show the change of total in the form Caption area.

If you want to learn more tricks with Datasheet, check the following Link:

Event Trapping Summary on Datasheet

[Laurie B.]

Yes. I am using a MSAccess Query.

[Robeen]

Hi Laurie,

I stuck a field just like what you have

Total 2011: [2011 DIRECT$] + [2011 GPOS$] + [2011 SCT$]

in a query and Totalled it just like you did - and I get a Field called Total 2011 - and each row of my resultset has the total of the three amount fields.

That's why I asked for your SQL.

I think you 'Grouped' your Query when you clicked the 'Totals' Button on your query in Design View.

Look at the fields in your Query and see if you have 'Group By' in the 'Total:' row of each field.

If there is then you have unwittingly told Access to Group your Query results.

[Laurie B.]

Thank you. I also noticed that the reason why they are not adding across is that if there isn't a $ value in the cell it doen't put a total in the "Total 2011" column.

Example

2011 Direct 2011 GPOS 2011 SCT T Total 2011

$1,000


I only get a total if there's $ in each column. If I add a 0 in the black fields the total of $1,000 will appear in the "Total 2011" column.

Thanks Again for your assistance with this.

[Robeen]

If you want to make sure that the totalling is done even if one or more of the Amounts is blank, you could use this:

 
Total 2011: nz([2011 DIRECT$],0) + nz([2011 GPOS$],0) + nz([2011 SCT$],0)

This will force zeroes into the calculation so it can be performed.

Access won't give you a result if one or more of your values is a Null.

Let me know if this solves your problem.

[Laurie B.]

That is so cool. It worked!

Thank you so much for solving this problem and for such a quick response.

Laurie B.

[Robeen]

I'm happy that I could help.