Subtraction in Access Queries

[hlebforprimeminister]

Hi sorry if I am being a bit dumb but i am confused.

Currently I have a query which has 3 fields.

One has a product ID number

The next has Quantity purchased

(these both fields from tables)

The next has quantity sold. Now this one is made by adding up all the records with a given "product ID number" to get the quantity sold. Each incidence of the ID number adds 1 to the total.

So far so good. I have product ID's the quantity purchased and quantity sold.

I now what a further field that will substract the quantity sold(which is in the previous query field) from the quantity purchased to obtain quantity remaining.

If anyone could help it would be immensely appreciated.

[Denburt]

From your post I am thinking that you are using a totals query so your results may not be what you expect. I am not real sure I understand but In a standard access query looking at the query in design view you have the fields to be viewed in the lower portion. If you add the following: MyRemQuant:[quantity purchased]-[quantity sold]

I hope this helps if not please post your SQL statement so I can understand a little better.

[hlebforprimeminister]

SELECT SW_INFO.SWI_ID, CST.CST_QTY, Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY
FROM (CST INNER JOIN SW_INFO ON CST.CST_ID = SW_INFO.SWI_CST_ID) INNER JOIN SW_SLS ON SW_INFO.SWI_ID = SW_SLS.SWS_SW_ID
GROUP BY SW_INFO.SWI_ID, CST.CST_QTY;


Thats the SQL i have currently.

What i want to do is have a field where i can substract:
"Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY"

From
"CST.CST_QTY"

[hlebforprimeminister]

" If you add the following: MyRemQuant:[quantity purchased]-[quantity sold]"

Where would i add this? Simply in the field box?

[hlebforprimeminister]

Right I tried Myremquant with
["CST.CST_QTY"]-[Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY]


It didn't work in as it said "invalid bracketing" or some such warning.

[Denburt]

Right I tried Myremquant with
["CST.CST_QTY"]-[Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY]


It didn't work in as it said "invalid bracketing" or some such warning.

Remove the quotes:

[CST.CST_QTY]-[Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY]

[hlebforprimeminister]

Thanks but unfortunately i tried that. It doesn't like the second term. The brackets within brackets apparently

[hlebforprimeminister]

"If you add the following: MyRemQuant:[quantity purchased]-[quantity sold]"

The problem is the "quantity sold" comes from the same query and is denoted by the following SQL. Its the sum of a the number of the field "SWS.QTY" from the "SW_SLS" table.

Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY

Anyone have any ideas how to construct this final field?

[Denburt]

O.K. to show you an example SQL I just created all I used was 1 table however it should work similarly for you as well, you should be able to see how I took the price field grouped it then in another field I added it up, in the third field I then subtracted them:

SELECT PRICEVALUELocal.PRICEINDEX, PRICEVALUELocal.PRICE, Sum(PRICEVALUELocal.PRICE) AS PRICE1,Sum(PRICEVALUELocal.PRICE)-[Price] AS P
FROM PRICEVALUELocal
GROUP BY PRICEVALUELocal.PRICEINDEX, PRICEVALUELocal.PRICE;
 

[CST.CST_QTY]-Sum(SW_SLS.SWS_QTY) AS SumOfSWS_QTY

[hlebforprimeminister]

Thanks!

After some tinkering that worked. I had to pick a different name for the field as SumOfSWS_QTY was used earlier but i have it working now.

Once again thanks that was really helpful!

[Denburt]

Good deal glad I could help.