Criteria counting only for one field

[Griffith]

Hi,

I'm using Access 2003 for the following:

I Have the following fields in my table:

Materialnr Vendor Type Value

There are 2 different types (same materialnr can occur with each type): "Repair" and "non-repair". Now I want to make a query that creates the following columns:

Material (group by); vendor (group by); Sum of Value where type is Repair; Sum of value of all types

Is there any way to do this? When I try to put Repair as a criterium to get only "Repair" types it will also filter the values of the last column...

Thanks in advance!

 

[Joe4]

Assuming your table is named Table1, this code should work:

SELECT 
    Table1.Materialnr
    , Table1.Vendor
    , Sum(IIf([Table1]![Type]="Repair",[Table1]![Value],0)) AS RepairValue
    , Sum(Table1.Value) AS TotalValue
FROM 
    Table1
GROUP BY 
    Table1.Materialnr
    , Table1.Vendor;