Minus figures??

[COMP]

Hi, i am trying to minus to figures in a query but it is asking me to enter in the figures when they are allready available. Here is and example: in the query section added in fields X and Y tht have Number values in. so i have done the following

Total grant Sum of total claims 1 2
2 3
5 etc
4
5
4


in another field i have named it "balance remianing section" i have entered in

Balanceremaining:[total grant]-[sum of actual claim]

now when i run this it asks to enter in the values, however the values are available. any ideas??

thanks


when i run this it asks me for

 

[Atomic Shrimp]

Are you sure you've got the field names correct? Your example data says the field is called 'Sum of total claims' and your query expression is 'sum of actual claim'

 

[COMP]

Hi, yer i did enter in [Total grant]-[Sum of total claims] and it is picking up the total grant but not the claims. DO you think this happeneing because i have had to enter in some sql code to add up the claims to display the "Sum of Total claims?? If someone can try creating a query and minus one figure from the other and if it does not ask you to enter in a value fiist and does the calcuation automatically, provide me a step by step guide to how to do it i would appreciate that.

thanks

 

[Atomic Shrimp]

What field names are available in the table or query you're basing this query on?

Anyway... here's an example*:

I created a table called 'fruit' with Long Integer columns 'bananas' and 'oranges' and populated this data:

bananas	oranges
1	15
2	25
3	35
4	12
5	11
6	1

I opened a new, blank query, added the fruit table, then added the oranges and bananas fields to the grid - in the third grid column, I typed:
diff:[oranges]-[bananas]

- the SQL for this query looks like this:

SELECT fruit.bananas, fruit.oranges, [bananas]-[oranges] AS diff
FROM fruit;

And the results look like this:

oranges	bananas	diff
15	1	14
25	2	23
35	3	32
12	4	8
11	5	6
1	6	-5

*(For those playing along at home, I do realise this example is not representative of a normalized database)

 

[COMP]

Ah excellent thank you Atomic Shrimo, however i think my code is similar to yours but still asks me for a figure. Here is my code:

SELECT Projects.[Project Reference], Sum(Claims.[Actual Claim Amount]) AS [SumOfActual Claim Amount], Projects.[Project Name], Projects.[Total Grant], [Total Grant]-[SumOfActual Claim Amount] AS [Outstanding Balance]
FROM Projects INNER JOIN Claims ON Projects.[Project ID] = Claims.[Project ID]
GROUP BY Projects.[Project Reference], Projects.[Project Name], Projects.[Total Grant], [Total Grant]-[SumOfActual Claim Amount];

I have highlighted the code that is relevant to this.

Any advise??

 

[Atomic Shrimp]

Have you tried something like this:

SELECT Projects.[Project Reference], Sum(Claims.[Actual Claim Amount]) AS [SumOfActual Claim Amount], Projects.[Project Name], Projects.[Total Grant], [Total Grant]-[SumOfActual Claim Amount] AS [Outstanding Balance]
FROM Projects INNER JOIN Claims ON Projects.[Project ID] = Claims.[Project ID]
GROUP BY Projects.[Project Reference], Projects.[Project Name], Projects.[Total Grant], [Total Grant]-(Sum(Claims.[Actual Claim Amount]));