Sum Multiple fields in a query

[bartoki]

I have a table "Invoices" that has 20 fields named item1Cost, item2Cost, etc. This table also has 20 other fields named item1Type, item2Type, etc. These types are looking up 1 of 7 values in another table "Costing_Types".

Now I would like to get the total sum of all items per each different costing type. So, add together all items for all records that have costing type 1. And then I'll do a new query for each costing type.

I've tried doing this with functions in my report, but DSum doesn't do it. Even with an IIf statement. (I suppose it could, but it would break the 255 character limit). Is there an SQL way to do this?

Thanks in advance.
-David

 

[lespaul]

If you normalize your tables, this would be very easy to do. Since the tables aren't normalized, first you have to write a query that normalizes the data, then use that query as the source for the query to actually get the information you need.

So, you are going to want something similar to this:

SELECT ItemType1 As ItemType, ItemCost1 As ItemCost, "Ones" As Something FROM tableName
UNION
SELECT ItemType2, ItemCost2, "Twos" From TableName
UNION
SELECT ItemType3, ItemCost3, "Threes" From TableName

(use some kind of identifier as the last field so you know which fields need to be summed together)

You will end up with:

ItemType ItemCost Something
1 $15.00 Ones
6 $24.00 Twos

Now you can sum and group by the item type.

Look into normalization: 'The Fundamentals of Relational Database Design'



Leslie

 

[PHV]

First, normalize your Invoices table by creating an union query, say qryNormalizedInvoices, like this:
SELECT item1Cost As itemCost, item1Type As itemType FROM Invoices
UNION SELECT item2Cost, item2Type FROM Invoices
...
UNION SELECT item20Cost, item20Type FROM Invoices
;
You may add some other fields of the Invoices table to each of the select statements above.
Now, your total sum query may look like this:
SELECT T.[costing type], Sum(I.itemCost) As TotalCost
FROM qryNormalizedInvoices I INNER JOIN Costing_Types T
ON I.itemCost = T.CostingID
GROUP BY T.[costing type];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Your table is not normalized so you cannot benefit from many of SQL's strengths. The SQL in a normalized database would be:

  Select Invoice,
         ItemType,
         Sum(Cost) as Cost
  From Invoices
  Group By Invoice, 
        ItemType

A normalized table setup would be like this:

"InvoiceItems"

InvoiceID
ItemID

"Items"

ItemID
Type
Cost (if cost varies by invoice, this would be in the InvoiceItems table)

"Invoices"

InvoiceID
CustomerID
...


Check out the Northwind sample database if you want more detail.

 

[bartoki]

Thanks for the help! Learn a new best practice everyday