How to do a sum query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to total seven columns by column and by row. I also need to omit any
fields without data. Can anyone help?
 
I need to total seven columns by column and by row. I also need to omit any
fields without data. Can anyone help?
Click to expand...

It sounds like you're thinking in spreadsheet terms. (You *are*
talking about Microsoft Access tables, which have fields and records,
rather than an Excel spreadsheet which has columns and rows... right?)

Assuming that you do want to do this in Access - which, by the way,
suggests that you need work on your table design! - create a new
Query. Select your table and select the seven fields.

In a vacant Field cell type

RowSum: NZ([Field1]) + NZ([Field2]) + NZ([Field3]) + NZ([Field4]) +
NZ([Field5]) + NZ([Field6]) + NZ([Field7])

all on one line, using your own fieldnames of course.

In order to see each line and also see the totals, you will want to
create either a Form (for onscreen viewing) or Report (for printing).
Base it on this Query. In the Form (or Report) Footer put seven
textboxes with Control Source property

=Sum([Field1])

for the seven fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
if you are doing this with a query, you will need 2 queries
that are indentical except for the following.
First query - in design view, in the 8th column in the
field row enter

TotalbyRow:[columnName1]+[columnName2]+[columnName3]+
[columnName4]+[columnName5]+[columnName6]+[columnName7]

the column names are the fields you are bringing into the
query.

Second Query - in disign view, click the totals icon.
(looks like the greek letter sigam) a new row will appear
entitled Totals. in the totals row for each column enter

sum

there is a drop down arrow with a number of options.
choose sum.

this can be done with one query if you use a report.
set the query up like query 1 above. use it as the reports
record source. in the report, bring in the totals column
like all the other columns. add a text box for each total
under detail text box for each column. in the totals text
boxs enter
=sum([yourquery].[field1] for totals text box 1
=sum([yourquery].[field2] for totals text box 2
=sum([yourquery].[field3] for totals text box 3
=sum([yourquery].[field4] for totals text box 4
=sum([yourquery].[field5] for totals text box 5
=sum([yourquery].[field6] for totals text box 6
=sum([yourquery].[field7] for totals text box 7
good luck
 
Back
Top