By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

Summing adjacent cells in Access

P: 1
Hi -- I'm using MS Access 2003 on Windows XP. I'm a fairly advanced Excel user, but very new to Access. In fact, I'm only using Access for this project because the data file is so big.

What I'd like to do is -- I have a table that looks something like this:

Company / Date / Q1 EPS / Q2 EPS / Q3 EPS / Q4 EPS
IBM / 1-05 / .25 / .40 / .35 / .02
IBM / 1-06 / .42 / .55 / .28 / .07
IBM / 1-07 / .15 / .18 / .21 / -.04
MSFT / 1-05 / .96 / .76 / .26 / .82
MSFT / 1-06 / .75 / 1.24 / .35 / .62
.
.

All I need is to put an additional column on the end and have it sum the Q1-Q4 EPS if data exists for all. In Excel this would be very easy using "if(count(C2:F2)=4,sum(C2:F2),"N/A")" and then pasting down the column, but I can't figure it out in Access. Could someone please help me with this?

Thanks in advance.
Sep 7 '07 #1
Share this Question
Share on Google+
1 Reply


Jim Doherty
Expert 100+
P: 897
Hi -- I'm using MS Access 2003 on Windows XP. I'm a fairly advanced Excel user, but very new to Access. In fact, I'm only using Access for this project because the data file is so big.

What I'd like to do is -- I have a table that looks something like this:

Company / Date / Q1 EPS / Q2 EPS / Q3 EPS / Q4 EPS
IBM / 1-05 / .25 / .40 / .35 / .02
IBM / 1-06 / .42 / .55 / .28 / .07
IBM / 1-07 / .15 / .18 / .21 / -.04
MSFT / 1-05 / .96 / .76 / .26 / .82
MSFT / 1-06 / .75 / 1.24 / .35 / .62
.
.

All I need is to put an additional column on the end and have it sum the Q1-Q4 EPS if data exists for all. In Excel this would be very easy using "if(count(C2:F2)=4,sum(C2:F2),"N/A")" and then pasting down the column, but I can't figure it out in Access. Could someone please help me with this?

Thanks in advance.

Create a select query and drop the fields into the grid then in the end column type this

Subtotal: Sum(NZ([Q1 EPS]))+Sum(NZ([Q2 EPS]))+Sum(NZ([Q3 EPS]))+Sum(NZ([Q4 EPS]))

and then select EXPRESSION from the 'totals' dropdown

and then run the query

(for your info NZ wrapping round the field name like that means convert any null value to zero.)

Regards

Jim
Sep 7 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.