Subquerying

jwab · #1 (**permalink**) 08-10-04, 10:32

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.

r123456 · #2 (**permalink**) 08-10-04, 23:16

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.