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,
开发者_StackOverflow中文版 (SELECT MAX(s2.begindatum)
FROM tblskuapex s2
WHERE s1.sku = s2.sku) q2
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
精彩评论