# Thread: subtraction between non-equal query outputs

1. Registered User
Join Date
Nov 2003
Location
Europe
Posts
369

## Unanswered: subtraction between non-equal query outputs

I have the following query A:
(3 colunms, client, stock (RIC) and amount of purchases per client)

Client number, RIC, SumOfamount_of_shares_bought
10231 1 699
10232 2 789
10233 2 1023
10234 9 2233
10235 2 2201
10236 9 5678
10239 2 342
10239 9 787
10240 7 458

And the not-always (...) corresponding Sales: Query B:

Client number, RIC, SumOfamount_of_shares_sold
10231 1 567
10233 2 678
10234 9 1490
10235 2 1701
10236 9 867
10239 9 577

I am not sure how to combine these two so that a third query can calculate and return the saldo/balance, ie, the client/stock (RIC) in query A: minus the same Client/stock (RIC) in query B.
The problem is that it is not always corresponding records in query B: Sometimes a client has bought a stock, but not sold it yet, so there is no corresponding cell/record to subtract.

Notice for example that these have no sales in B:
10232 2 789
10239 2 342
10240 7 458

How can I make a third query that subtracts B from A, and leaves the value in A whenever there is nothing to subtract for that client/stock in B?

I think this has to to with the (almost) same problem I have in combining two tables in a query where I want some cells/columns to be left blank.

Last edited by kedaniel; 01-17-04 at 18:16.

2. Registered User
Join Date
Oct 2001
Location
Chicago
Posts
440
Use an outer join to return all values from QueryA and either the Nz or IIf functions to take care of the null values in the subtraction.

(IIF function)
SELECT QueryA.[Client number], QueryA.RIC, IIf([QueryB]![SumOfamount_of_shares_sold] Is Null,[QueryA]![SumOfamount_of_shares_sold],[QueryA]![SumOfamount_of_shares_sold]-[QueryB]![SumOfamount_of_shares_sold]) AS Balance
FROM QueryA LEFT JOIN QueryB ON (QueryA.RIC = QueryB.RIC) AND (QueryA.[Client number] = QueryB.[Client number]);

(Nz function)
[QueryA]![SumOfamount_of_shares_sold]-Nz([QueryB]![SumOfamount_of_shares_sold])

-Matt
Last edited by Rockey; 01-17-04 at 21:06.

3. Registered User
Join Date
Nov 2003
Location
Europe
Posts
369
Originally posted by Rockey
Use an outer join to return all values from QueryA and either the Nz or IIf functions to take care of the null values in the subtraction.
(IIF function)
Thanks very much for specific and useful example! :-)
I get dialogs asking for parametre values, though:

Test with iif function - :
SELECT qry_stocksum_purchases_per_cidric.[client_number], qry_stocksum_purchases_per_cidric.RIC,
IIf([qry_stocksum_sales_per_cidric]![SumOfamount_of_shares_sold] Is
Null,[qry_stocksum_purchases_per_cidric]![SumOfamount_of_shares_sold],[qry_stocksum_purchases_per_cidric
]![SumOfamount_of_shares_sold]-[qry_stocksum_sales_per_cidric]![SumOfamount_of_shares_sold]) AS
Balance FROM qry_stocksum_purchases_per_cidric LEFT JOIN qry_stocksum_sales_per_cidric ON (qry_stocksum_purchases_per_cidric.RIC = qry_stocksum_sales_per_cidric.RIC) AND
(qry_stocksum_purchases_per_cidric.[client_number] = qry_stocksum_sales_per_cidric.[client_number]);

Result: lists all relevant CID/RIC's but asks for parametres, returns no balances if not entering parametres. But the list is exactly right according to what is in the test tables, only lacks the balance values :

Client number RIC Balance
10231 1
10232 2
10233 2
10234 9
10235 2
10236 9
10239 2
10239 9
10240 7

Parametre question1:
[qry_stocksum_purchases_per_cidric]![SumOfamount_of_shares_sold]

Parametre question2:
[qry_stocksum_purchases_per_cidric]

****************

Test with nz function :

SELECT qry_stocksum_purchases_per_cidric.[client_number],
qry_stocksum_purchases_per_cidric.RIC,
[qry_stocksum_purchases_per_cidric]![SumOfamount_of_shares_sold]
-Nz([qry_stocksum_sales_per_cidric]![SumOfamount_of_shares_sold]) AS
Balance
FROM qry_stocksum_purchases_per_cidric LEFT JOIN
qry_stocksum_sales_per_cidric ON
(qry_stocksum_purchases_per_cidric.[client_number] =
qry_stocksum_sales_per_cidric.[client_number]) AND
(qry_stocksum_purchases_per_cidric.RIC =
qry_stocksum_sales_per_cidric.RIC);

Same result without values in the Balance column,
with one in stead of two parametre questions, same as the first one :
[qry_stocksum_purchases_per_cidric]![SumOfamount_of_shares_sold]

Any further suggestions?

4. Registered User
Join Date
Oct 2001
Location
Chicago
Posts
440
Does running qry_stocksum_purchases_per_cidric bring up a parameter request?

Double check the spelling field names - i.e. [SumOfamount_of_shares_sold]

Matt

5. Registered User
Join Date
Nov 2003
Location
Europe
Posts
369
Originally posted by Rockey
Does running qry_stocksum_purchases_per_cidric bring up a parameter request?

Double check the spelling field names - i.e. [SumOfamount_of_shares_sold]

Matt
No parametres on the underlying queries.
I removed SumOf from those references, as that is autogenerated in the result window of that query, the field itself is without the SumOf, but I still get the same results, asking for parametre on those fields, now referencing the fields without the SumOf as part of the field name of course.

6. Registered User
Join Date
Oct 2001
Location
Chicago
Posts
440
typo somewhere

see me example attached...

7. Registered User
Join Date
Nov 2003
Location
Europe
Posts
369
Originally posted by Rockey
typo somewhere
ah, yes, [qry_stocksum_purchases_per_cidric]![SumOfamount_o
f_shares_sold]
should be
[qry_stocksum_PURCHASES_per_cidric]![SumOfamount_o
f_shares_BOUGHT] ...

Now it works , thanks !

kedaniel

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•