Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question 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.

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

  2. #2
    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. #3
    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. #4
    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. #5
    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. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    typo somewhere

    see me example attached...
    Attached Files Attached Files

  7. #7
    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
  •