Access WITH subquery

[johnseito]

Does access have WITH subquery,

for example creating a variable that stores a whole subquery such as:

WITH variableTEST (a, b, c, d, e) as 
       (SELECT a1, a2, a3, max(ab), count(*) as nbids
        FROM A x natural join B y
        WHERE x.something > y.something
        GROUP by a1, a2, a3)

WITH variableTWO (f, g, h, i, j, k, j) as 
      (SELECT A.bidder, W.a, W.b, W.c, W.d, W.e, A.something
      FROM variableTEST W natural join B A 
      WHERE W.sprice = B.bidprice)

SELECT h FROM variableTWO WHERE now - j <7

Can it do something like this, if not what alternative do they have to do something similar?
Using WITH and subquery and variable, and storing variables and then later use it again in another subquery, can
it do something like this in access SQL?

[June7]

I have never seen that. Did you try typing into the SQL view of query builder? What happened?

Queries can be nested. I don't really know what you are trying to do. Maybe:

SELECT h FROM (SELECT A.bidder, W.a, W.b, W.c, W.d, W.e, A.something
FROM (SELECT a1, a2, a3, max(ab), count(*) as nbids
FROM A x natural join B y
WHERE x.something > y.something
GROUP by a1, a2, a3) AS variableTEST W natural join B A
WHERE W.sprice = B.bidprice) AS variableTWO WHERE now - j <7

[johnseito]

The error I get after putting in the query builder is:

invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

this is the over all code that I believe should work on SQL server,

 
WITH WIN (suid, iid, stime, price, nbids) as 


    (SELECT bid.seller, bid.iid, bid.starttime, max(bid.bidprice), count(*) as nBIDs
     FROM auction INNER JOIN bid  ON (auction.seller = bid.seller) and (auction.iid = bid.iid) and (auction.startdate = bid.starttime)
     WHERE bid.bidtime < auction.enddate AND bid.bidprice >= auction.minbid AND now >=auction.enddate
     GROUP BY bid.seller, bid.iid, bid.starttime)


WITH WINdetails (buid, suid, iid, stime, sprice, nbids, btime ) as 
    (SELECT b.bidder, w.suid, w.iid, w.stime, w.sprice, w.nbids, b.bidtime
    FROM WIN W INNER JOIN BID B WHERE w.sprice = b.bidprice)
   
   SELECT iid from WINDETAILS WHERE now - enddate < 7

What I am trying to do is find the person who won the bid within the last week.
This code below finds the first part ("all the person who won the bid") but it doesn't set the criteria within the last week.

 
SELECT bid.seller, bid.iid, bid.starttime, max(bid.bidprice), count(*) as nBIDs
              FROM auction INNER JOIN bid  ON (auction.seller = bid.seller) and (auction.iid = bid.iid) and (auction.startdate = bid.starttime)
              
              WHERE bid.bidtime < auction.enddate AND bid.bidprice >= auction.minbid AND now >=auction.enddate
              GROUP BY bid.seller, bid.iid, bid.starttime

this code below is suppose to limit them to the items that were sold last week.

 
SELECT iid from WINdetails WHERE now - enddate < 7

The code you provided is actually not similar to the first one I put up, because the one I put up is using variable to represent all the fields,

example:
WITH WIN (suid, iid, stime, price, nbids) as

bid.seller = suid
bid.iid = iid
bid.starttime = stime
max(bid.bidprice) = price
and count(*) = nbids

Here is the sample data, highlighted in yellow, the bottom row is the winning bid because it's the highest bid price.

[johnseito]

I think I figured it out with your help on nesting SELECT statements.
This is the code I think should do it.

 


SELECT bid.seller, bid.iid, bid.starttime


FROM (


SELECT bid.seller, bid.iid, bid.starttime, auction.enddate, max(bid.bidprice), count(*) as nBIDs
              FROM auction INNER JOIN bid  ON (auction.seller = bid.seller) and (auction.iid = bid.iid) and (auction.startdate = bid.starttime)
              
              WHERE bid.bidtime < auction.enddate AND bid.bidprice >= auction.minbid AND now >=auction.enddate
              GROUP BY bid.seller, bid.iid, bid.starttime, auction.enddate


) 




WHERE now - ENDdate < 7

But I still wonder if the above code that I have doable in access.

What if I wanted, the name of the buyer and seller, and the item then how do I get those to show in the query since it will be difficult because I
am doing a GROUP BY and with the GROUP BY I have to not include certain fields otherwise the GROUP BY won't work.

[June7]

Actually, I don't understand aggregating with BidID. Are there multiple records for each BidID?

[johnseito]

yes bidid has multiple records, bid.iid is the item and many people can bet on the same item. :-)