Looking For Simple Query SQL Command

[ac916]

Hi,

I am looking for a Access Query SQL Select command.

I have data that I imported into access (Table1). Basically, the file shows Item cost
with item number, cost amount & cost type.

I need to add the cost amount for "Burden" cost type into Overhead cost type.



For example of what I have:
Table1 :Item Cost
Field 1 – Item Number
Field 2 - Cost
Field 3 - Cost Type

ABC | 10 | Material
ABC | 5 | Labour
ABC | 4 | Burden
ABC | 3 | Overhead
DEF | 6 | Material
DEF | 7 | Labour
DEF | 1 | Burden
DEF | 2 | Overhead


What I am trying to get from the Access Query result:
Table2 : Item Cost or Query
Field 1 – Item Number
Field 2 - Cost
Field 3 - Cost Type

ABC | 10 | Material
ABC | 5 | Labour
ABC | 7 | Overhead
DEF | 6 | Material
DEF | 7 | Labour
DEF | 3 | Overhead

Or

ABC | 10 | Material
ABC | 5 | Labour
ABC | 4 | Burden
ABC | 7 | Overhead
DEF | 6 | Material
DEF | 7 | Labour
DEF | 1 | Burden
DEF | 3 | Overhead


Thanks
Andrew

 

[neileg]

Try this

SELECT tbl1.Item, IIf([CostType]="Burden","Overhead",[CostType]) AS NewType, Sum(tbl1.Cost) AS SumOfCost
FROM tbl1
GROUP BY tbl1.Item, IIf([CostType]="Burden","Overhead",[CostType]);

 

[ac916]

Thank you, it works

Thank you very much, it works fine.