sql - Using the result of an Access query in another query. -

i've searched internet solution , racked brains trying figure out, can help. here basic situation.

i have list of transactions in table. each transaction has fields client id, store id, product bought, , billing code (which either 2, 3, 4, 5, 6, 7)

some stores aren't using billing code 2 @ , want investigate further.

i created list of stores don't use billing code 2. there 20 stores.

i created query gave me list of clients associated 20 stores.

-------------------this now-----------

basically want see if people if go store x , buy product y treated differently (billing code) in 2 different stores. there more money store if don't use billing code 2 incentive not use code.

is there way query , tell access give me clients using these 20 stores , store (not of 20) , purchase same product elsewhere?

any appreciated. have feeling need simplify , take in multiple steps can't seem work out path point point b on this. thanks.

okay, sounds you've got query find stores don't use billingcode=2

select distinct storeid tblpurchases storeid not in (select storeid tblpurchases billingcode=2); 

i've saved query in access [qrystoresnotusing2] can use below.

it sounds you've leveraged first query give list of clients shop @ stores, done like

select distinct clientid tblpurchases storeid in (select storeid qrystoresnotusing2); 

now sounds want detail on clients buy product @ 1 of "non-2" stores , buy same product elsewhere.

we can start generating list of purchases same client buys same product @ different stores:

select distinct p1.clientid, p1.productid,          p1.storeid store1, p1.billingcode billingcode1,          p2.storeid store2, p2.billingcode billingcode2 tblpurchases p1 inner join tblpurchases p2      on p1.clientid=p2.clientid , p1.productid=p2.productid , p1.storeid<>p2.storeid 

note first 2 conditions in on clause ensure client , product same, , third condition ensures store different.

also note "not equals" self-join produce multiple symmetrical results: you'll row "store_x, store_y" , row "store_y, store_x". okay, because want restrict results purchases one of stores in "non-2" list, we'll take ones there's match on 1 side of join (p1) adding where clause query above:

select distinct p1.clientid, p1.productid,          p1.storeid store1, p1.billingcode billingcode1,          p2.storeid store2, p2.billingcode billingcode2 tblpurchases p1 inner join tblpurchases p2      on p1.clientid=p2.clientid , p1.productid=p2.productid , p1.storeid<>p2.storeid p1.storeid in (select storeid qrystoresnotusing2); 

that query list individual purchases. if want just clients can omit productid, storeid, , billingcode columns output ,

select distinct p1.clientid ...