Sum Queries

[Lilsug]

OK, I'm creating a simple set of financial tables that will calculate Outstanding AR in different ways and perform some other things. I have a table that is calculating the Outstanding AR right now on a departmental basis for each month. Notice the 1st three Characters in Cust_code identify the customer, and the rest identifies the department. I added a field called Cust_Abr to the end that is just those first 3 characters. This is the table named 2010 Outstanding Revenue.

2010 Outstanding Revenue
Cust_Code January February
BEEJ10301 $50.00 $50.00
BEEJ20101 $50.00 $50.00
BROCA $50.00 $50.00
BROJ10101 $50.00 $50.00
BROJ20201 $50.00 $50.00


BROJ30301 $50.00 $50.00
BROJ40401 $50.00 $50.00

There is another table Called Customer Abbreviations that has the first 3 characters (BEE) and the actual account name.

Customer Abreviations
CUST_ABR CUSTOMER_Name
BEE BEE Enterprises
BRO BROWN TECH
CAL CALDWELL CONSTR
CAM CAMPBELL SOUP
CAS CAST PRODUCT


I am building a query that will sum the BEE figures into one January and Feb and March etc... As well as BRO and every other Customer. This way I have a detailed Outstanding AR by month for each department, and one for the customer as a whole. I am currently trying to query this into a table, but can't seem to get it.

I want the result table to look like this:

2010 OAR BY ACCOUNT
Customer January February
BEE Enterprises $100.00 $100.00
BROWN TECH $250.00 $250.00

Please Help! I've tried dsum, sum and I can't seem to get it to matchup on the customer codes properly.

Thanks

[weekend00]

select b.CUSTOMER_Name as CUSTOMER, sum(a.january) as january, sum(a.february) as february
from [2010 Outstanding Revenue] as a inner join [Customer Abreviations] as b on b.CUST_ABR =left(a.cust_code,3) group by b.CUSTOMER_Name

[Lilsug]

Thanks for the reply!

This query is asking me for a Cust_Code parameter and then populating every Customer with that code's OAR. Not really sure what to do.

[weekend00]

check the field name in [2010 Outstanding Revenue], if it's not cust_code, modify it.

[Lilsug]

Works!

Thanks alot!