Subquery question in MS Access

Question

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

Answer 1

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