How to do subtract in Query

[NoOoN]

I have a queastion that : how much credit dose each customer still have available?



I think the table I have to chose are : ( customet table wich have the credit limit) and ( Sales table which have the Amount of each customer spent)

when do this formula : Credit Available: sum([Customer]![Credit limit]-[Sales]![Amount])

It dosn't give the right answer

help me please

here is the file

http://www.mediafire.com/?grwhmsyzu2a04qp

[kennejd]

First create a field to sum the credit limit...call it CreditLimit (no spaces in field names is preferred). Next create a field to sum the sales amount....you can call it salesAmount or whatever you prefer. Now add a new field called CreditAvail. It will look like CreditAvail: [CreditLimit] - [salesAmount]. Use the group on expression for this field.

[NoOoN]

Thank you kennejd

look to what I do







and when I do run, it gives me this message



I keep it blank

and this is the ruslt


what is the wrong in my steps?

[kennejd]

I guess my explanation was confusing...

You need to have separate columns that sum your credit amount and sales amounts. Then a 3rd column to do you calculation. Unfortunately I can't send you a screen shot...but if you pasted the following in the SQL view of your query, I think you'll be very close. For future reference, using spaces in table and field names is bad practice...

SELECT
[Customer Number]
,sum(Credit Limit) as CreditLimit
,sum(Amount) as SalesAmt
,[CreditLimit] – [SalesAmt] as CreditAvail
FROM Customer c
INNER JOIN Sales s
ON c.[Customer number] = s.[Customer number]
GROUP BY
[Customer Number]

[NoOoN]

Thanx kennejd

I do it by design veiw and it works


The subtract is done

but I don't know if there is a mistake?!

because the customer number 1000, his credit limit is 2500 and he do three sales which it cost him 3129.7

here the query sum the three times for the limit!

It must be shown as zero or in minus!

and thank you so much for your help =)

[kennejd]

I see the problem....instead of a sum you need to use max or min for the credit limit column. Be sure to rename your columns or you will get 'SumOfCredit'...'SumOfAmount'. Rename by putting Credit: in front of the field name.

[NoOoN]

Thank you so much

it is work

=)