Results 1 to 3 of 3

Thread: Subquerying

  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43

    Unanswered: Subquerying

    Ok, I've been trying to form a big query in Access, from a group of small queries which produce tables so another query can work on them.. The database in question is very poorly design (not by me) so please dont get lost in this:

    Original queries:

    1: This query gets the IMEI's which are "talkplan changes" and cost more than or equal to 166 (its in negative because we pay out)

    Code:
    SELECT CLAWBACKS.IMEI
    FROM CLAWBACKS
    WHERE (((CLAWBACKS.REASON)="talkplan change") AND ((CLAWBACKS.CLAWBACK)<-166 Or (CLAWBACKS.CLAWBACK)=-166))
    ORDER BY CLAWBACKS.CLAWBACK DESC;
    2 This gets all the IMEI after the date 31/8/03 and cost between 98 AND 126

    Code:
    SELECT [CONNECTED STOCK].IMEI
    FROM [CONNECTED STOCK]
    WHERE ((([CONNECTED STOCK].[DATE CONNECTED])>#8/31/2003#) AND (([CONNECTED STOCK].PAYMENT) Between 98 And 126));
    3 Because the you can have more than one IMEI per clawback this strips out the duplicates from query 1

    Code:
    SELECT DISTINCT [75 bonus clawback].IMEI
    FROM [75 bonus clawback];
    4 Finally this gives us the IMEI's which are in both query 2 AND 3 AND which dealers those IMEI's were sold.

    Code:
    SELECT [Distinct 75 bonus clawback].IMEI, [STOCK OUT].DEALER
    FROM ([Distinct 75 bonus clawback] INNER JOIN [connected no bonus filter] ON [Distinct 75 bonus clawback].IMEI = [connected no bonus filter].IMEI) INNER JOIN [STOCK OUT] ON [Distinct 75 bonus clawback].IMEI = [STOCK OUT].IMEI;

    Now this is my attemt at putting them all into one query

    Code:
    SELECT CLAWBACKS.IMEI, STOCK_OUT.DEALER
    FROM CLAWBACKS INNER JOIN STOCK_OUT ON CLAWBACKS.IMEI = STOCK_OUT.IMEI
    WHERE CLAWBACKS.IMEI = 
    (SELECT [CONNECTED STOCK].IMEI
    FROM [CONNECTED STOCK]
    WHERE [CONNECTED STOCK].[DATE CONNECTED]>#8/31/2003# AND [CONNECTED STOCK].PAYMENT Between 98 And 126) AND REASON = "talkplan change" AND CLAWBACK <= -166;
    Of course is doesnt work, if anyone can decifer this mess please have a go at helping me. I'll try answer any questions.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    Code:
    Select V1.IMEI, ST.DEALER
    from
    (
      (SELECT distinct cl.IMEI
      FROM CLAWBACKS cl
      WHERE (((cl.REASON)="talkplan change") AND ((cl.CLAWBACK)<-166 Or (cl.CLAWBACK)=-166))) V1
      INNER JOIN
      (SELECT cs.IMEI
      FROM [CONNECTED STOCK] cs
      WHERE (((cs.[DATE CONNECTED])>#8/31/2003#) AND ((cs.PAYMENT) Between 98 And 126))) V2 ON
      V1.IMEI = V2.IMEI
    )
    INNER JOIN
    [STOCK OUT] ST ON
    ST.IMEI = V1.IMEI
    Thank You.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Quote Originally Posted by r123456
    Thank You.
    No, Thank YOU! Very nice. Now i'm going to figure out how it works, and it does work.. although DISTINCT was req on calling

    Code:
    (SELECT cs.IMEI...
    Didnt see duplicates in the connected stock table (told you it was badly designed)

    Thanks again. Wooo hoo

Posting Permissions

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