Summing columns in MS Access

[Sashi]

Hi

I have MS Access 2000. I have a database with 5 columns of
data.

Col 1 Machine ID
Col 2 Month
Col 3 Usage of Machine in 1st shift
Col 4 Usage of Machine in 2nd shift
Col 5 Usage of Machine in 3rd shift

I would like to write a query which sums up the usage of
the machines in all the three shifts, by the month and
machine.

How do you sum up the columns??

Thanks in Advance

Sashi

 

[Kevin]

Col 1 Machine ID

Col 2 Month
Col 3 Usage of Machine in 1st shift
Col 4 Usage of Machine in 2nd shift
Col 5 Usage of Machine in 3rd shift

I would like to write a query which sums up the usage of
the machines in all the three shifts, by the month and
machine.

How do you sum up the columns??

Sashi,

Drag the five fields to the query grid. Select View,
Totals from the menu. In the Total row, each will be
assigned by default "Group By". For the 1st, 2nd, and 3rd
shift fields, change this to "Sum". This will give you
sums for each machine by month. Your report could then
show each month's usage, plus a summary total, e.g.,

Machine ID # 20324

Shift
Month 1 2 3
Machine 423 Jan 45 55 70
Feb ...
...
Dec 50 60 50

Total 600 650 700
HTH.

Best regards.
Kevin Sprinkel
Becker & Frondorf

 

[Van T. Dinh]

SELECT T.[MachineID], T.[Month],
Nz(T.Col3, 0) + Nz(T.Col4, 0), Nz(T.Col5, 0) As MonthTotal
FROM YourTable As T

 

[Rolls]

A better way is to normalize your table layout:

Field1: Machine
Field2: Date
Field3: Shift
Field4: Usage

The first 3 fields are criteria fields, the 4th is an aggregated field.
Then you'll be able to filter on any criteria and sum the numeric Field4 in
all possible combinations. If you want to view the data in multiple columns
use an IIF statement to select the criteria field(s) to be displayed in your
query or report.
IIF([Field3]=1,Field4,0) returns the shift 1 machine hours
IIF([Field3]=2,Field4,0) returns the shift 2 machine hours
etc.