Calculating a Coloumn

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

Guest

How do I calculate all the entries in a single coloumn as opposed to rows?

For example, I have several class hours that need to be totaled, but the SUM
function is most insistant upon calculating rows, when I really need it in a
coloumn.

Thank you very much in advance for the answers.
 
How do I calculate all the entries in a single coloumn as opposed to rows?

For example, I have several class hours that need to be totaled, but the SUM
function is most insistant upon calculating rows, when I really need it in a
coloumn.

Thank you very much in advance for the answers.
Click to expand...

Don't you have that backwards? A SUM sums across records ("rows"), not
across fields ("columns").

You should step back a bit. What you are describing is a spreadsheet
operation - in Excel you can easily sum horizontally or vertically.
But Access *IS NOT A SPREADSHEET*, and it will be very rare that a
properly designed Access table will need to be summed across fields.

Could you describe the nature of the data, and how your table is
structured? Might it be more appropriate to use Excel for this
operation?

John W. Vinson[MVP]
(no longer chatting for now)
 
Thank you. I do understand the fact that what I'm asking for is really a
spreadsheet problem, but all the other important information is already in an
Access database. As soon as I get this final query to work- it the Database
will be done.

The problem I am having are as follows:

I have a query based on the following fields:

Instructor Course Course Number Number of Hours Count of Course Number
Min of Number of Hours

The reason why the Count of Course number is there is because I only want
and need the course to be viewed by the database once, as opposed to x number
of students that are in it.
For example; Course 754N has seven students and is 40 hours long. The
database, without the Count of Course Number, wants to multiply 40*7. That is
what I do not want.
I want only 40 hours to show for that one course.

In short, I want to see how many hours the instructor taught that adds the
Number of Hours of all courses.

The reason why I asked my original inquiry was when I look at my query in
Datasheet View, I see that all I want to do is add the Number of Hours
column, but as stated before, Access desires to do only calculations of Rows.

If there is a different way to do what I want, I am most ready to change to
it, be it a Crosstab query (where I am running into the same problem) or any
other type of query.

Once again, I thank you in advance.
 
Hi,


I suggest you export your data in a spreadsheet and do the work there...
a database works vertically, not horizontally.

If you cannot export the data, make the sum "manually" :

SELECT Nz(f1, 0) + Nz(f2, 0) + Nz(f3, 0) + Nz(f4, 0) + ......
FROM tableName


where f1, f2, f3, ... are the fields to sum.



If you have to continue to use the database further on, normalize your
data, instead, like:


SELECT instructor, Course1 As TimeSpent, 'Couse1' As Reason FROM
tableName WHERE NOT Course1 IS null
UNION ALL
SELECT instructor, Course2, 'Course2' FROM tableName WHERE not Course2
Is Null
UNION ALL
SELECT instructor, Course3, 'Course3' FROM tableName WHERE not Course3
Is Null
...

UNION ALL
SELECT instructor, CourseN, 'CourseN' FROM tableName WHERE not CourseN
Is Null


which make your data VERTICAL.Save the query, or better, make it a table,
and use that table further on, to do work on your data.

That does not mean you have to use that disposition for presentation! NOT
AT ALL. TABLES are for work, forma and reports are for presentation. Use
Total query or Crosstab to change from the normalize presentation of data
(nice to make work, hard to read) to horizontal disposition (easy to read,
hard to work with it).



Hoping it may help
Vanderghast, Access MVP



"(e-mail address removed)"
 
Thank you very, very much for your assitance. I'm going to input your ideas
into the query and shall reply on the outcome.
 
Back
Top