CODEWITHSUNDEEP
sqlms-accesssubquery
Jelle De Loecker
Jelle De Loecker

Reputation: 21985

Subquery question in MS Access

I'm not quite sure why the following query won't run in Access. It asks me to give the value of s1.sku


SELECT s1.SkuApexId, s1.sku, s1.apex, s1.btw, s1.gebruikernr, q2.*
FROM tblSkuApex AS s1,
              (SELECT MAX(s2.begindatum)
              FROM tblskuapex  s2
              WHERE s1.sku = s2.sku) q2

Upvotes: 2

Views: 317

Answers (1)

daskd
daskd

Reputation: 96

The way you put it, you require a cross join to the q2 table, which is to be changing depending on the s1.sku value of each record of the s1 table, which is unacceptable.

I think you should place the q2 subquery as a column and not as a table. Since you retrieve a single value, it could be easily put like this: 

SELECT s1.SkuApexId, s1.sku, s1.apex, s1.btw, s1.gebruikernr, 
         (SELECT MAX(s2.begindatum)
              FROM tblskuapex  s2
              WHERE s1.sku = s2.sku) as maxbegindatum
FROM tblSkuApex AS s1

or even better, why dont you get it as a normal aggregation, since you request the max from the same table:

SELECT 
    s1.SkuApexId, s1.sku, s1.apex, 
    s1.btw, s1.gebruikernr, MAX(begindatum)
FROM tblSkuApex AS s1
group by 
    s1.SkuApexId, s1.sku, s1.apex, 
    s1.btw, s1.gebruikernr

note: you could ommit the s1 alias everywhere

Upvotes: 4

Related Questions