Sum query

[sabavno]

I have one table

RecID Transit USRoaming
1 1 yes
2 1 no
3 4 no

Another table

Regular Charges USRoaming Charges
100 120

I need to rollup all the transits
so I would get

Transit Charges
1 220
4 100


Please suggest the SQL

Thanks

 

[AutumnBlues]

Sabavno

Here's the SQL, you just need to change the table name (assuming you have a Yes/No field type rather than text that says 'yes' or 'no')...
-------------------
SELECT Transit, Sum(IIf([USRoaming]=True,120,100)) AS Charges
FROM tblExample
GROUP BY Transit;
-------------------
I'll just add that you might find it easier to modify your tables, so that instead of a 'US Roaming' field in the first table, you have a 'ChargeFrID'. You then have a Charges table that would have fields: ChargeID, Charge Amount, Charge Description. If it's a relational database, you can link the tables together on ChargeID(One) = ChargeFrID(Many). If it's not a relational database, you still have a valid link for query building.

AutumnBlues