Microsoft Access 2003 - creating a separate field that totals 5 other fields from the table.

Question

This is what i have so far in the SQL code........

SELECT DISTINCTROW [OEE/A Query].Press, 
Sum([OEE/A Query].[SumOfLabor Hours]) AS [Sum Of SumOfLabor Hours], 
Sum([OEE/A Query].[SumOfGood Pieces]) AS [Sum Of SumOfGood Pieces], 
Sum([OEE/A Query].[Scrap Pieces]) AS [SumOfScrap Pieces], 
Sum([OEE/A Query].[SumOfMachine Hours]) AS [SumOfSumOfMachine Hours], 
Sum([OEE/A Query].[Total Parts Hours Earned]) AS [SumOfTotal Parts Hours Earned], 
Sum([OEE/A Query].[Standard Pcs Expected]) AS [Stand Pcs Expected]
FROM [OEE/A Query]
GROUP BY [OEE/A Query].Press;

How do i add to this code another field that totals 5 separate other fields?

Here's what i think it might look like but I'm not sure.....

SELECT Sum(Sort+Straighten+Shine+Standardize+Sustain)
   SUM(Sort),
   SUM(Straighten),
   SUM(Shine),
   SUM(Standardize),
   SUM(Sustain),
FROM [Shift Report Table];

Answer 1

Yes, that is correct. You can add fields within the sum function, to sum the sum of the fields.

Answer 2

it depends on what you are really asking. I am not sure if your second query is just another example, or, if it is in fact a different data source to be added into your first query.

If you want to add another column to your first query example based on the columns that already exist in your query then yes, simply add them together, as per CK says.

e.g.

select sum(column1) + sum(column2) as sum_c1_c2

or

select sum(column1 + column2) as sum_c1_c2

when doing aggregate functions I have an old habit of handling NULL values to make sure I am getting the results I think I should be getting. e.g.

select sum(nz(column1,0) + nz(column2,0)) as sum_c1_c2   

Now if you are asking how to add a new column from a different data source, then you can either join to that other datasource, or, if returning just a single value, can use an inline select.

Also, a word of warning about distinctrow - I am not sure you want to use that in your query. You are doing a group by, so only the unique values for [press] will be selected with the aggregated columns as per your SUM() function. It is really hand for things like determining if (by way of example) a Product has been ordered e.g.

select DISTINCTROW productname
from products
inner join orders on orders.productid = products.productid

will return just 1 row so there is no chance for any aggregation in the above case.

But you might want to clarify your requirement a bit more. CK might have already given the answer, or, we might need to include a different datasource (such as your second query).