473,405 Members | 2,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Summing adjacent cells in Access

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
1 1736
Jim Doherty
897 Expert 512MB
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

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

Similar topics

9
by: Yaroslav Bulatov | last post by:
I made an array of 10 million floats timed how long it takes to sum the elements, here's what I got (millis): gcc -O2: 21 Python with numarray: 104 Python with Numeric: 302...
2
by: Targa | last post by:
<input NAME="TAXRATE" onBlur="this.form.TAX.value = (this.form.TAXRATE.value - 0) * (this.form.ITEM1TOTAL.value - 0) + (this.form.ITEM2TOTAL.value - 0) " Size="4"> In my TAX field I get...
7
by: Hank | last post by:
I have a report-summing problem using Access 2000. When a section runs over the end of the page, sometimes a detail gets picked up twice. Example: Customer Header XYZ Company Detail Section...
5
by: Richard Dixson | last post by:
I created a new C# web application. Basically all I am trying to do is create a table that consists of a few rows with two columns in each. And then to set those columns to text values from my...
7
by: Rich_C | last post by:
I'm able to clone a table row using this: function insert(btn) {var cell, newRow, row, sect; if((cell = btn.parentNode) && (row = cell.parentNode) && row.cloneNode && (sect = row.parentNode) &&...
2
by: OceanBreeze | last post by:
Border drawn in C# Table programmatically even if several adjacent horizontal & vertical cells are empty in the table I want to programmatically have border on each and every row and column in the...
4
by: mimasci | last post by:
Hi, I have tried to write a code that allowed me to modify the value of the cells selected in a table, with excel has been easy. How one approaches selected cells? (To modify the value) How...
7
by: lethek39 | last post by:
Hey I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the...
1
by: tbakas2 | last post by:
Thank you in advance for the help. I'm using Microsoft XP, I'm in the VBA editor in Excel. I'm trying to write code that will assign a positive or negative value based upon what the adjacent cell...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.