Edit query in MS-Access with a subquery containg a SUM() field
When I open this query in Access (which is just a collection of 2 linked tables) I'm able to edit the data with no problems whatsoever.
SELECT O.*, PP.skuapexid
FROM tblSkuBestellingen AS O
INNER JOIN tblSkuApex AS PP
ON (PP.begindatum <= O.besteldatum) AND (PP.sku = O.sku)
WHERE NOT EXISTS
(
SELECT * FROM tb开发者_JS百科lSkuApex PP2
WHERE PP2.sku = PP.sku AND
PP2.begindatum <= O.besteldatum AND
PP2.begindatum > PP.begindatum
);
However, when I add a little field to show me the total of delivered products it suddenly won't allow me to edit this any more. Is there a way around this?
SELECT O.*, PP.skuapexid,
(
SELECT Sum(Nz(tblLeveringProduct.geleverd,0)) AS TotaalGeleverd
FROM tblSkuBestellingen LEFT JOIN tblLeveringProduct ON tblSkuBestellingen.SkuBestelId=tblLeveringProduct.SkuBestelId
WHERE tblSkuBestellingen.sku = PP.sku
) AS TotaalGeleverd
FROM tblSkuBestellingen AS O
INNER JOIN tblSkuApex AS PP
ON (PP.begindatum <= O.besteldatum) AND (PP.sku = O.sku)
WHERE NOT EXISTS
(
SELECT * FROM tblSkuApex PP2
WHERE PP2.sku = PP.sku AND
PP2.begindatum <= O.besteldatum AND
PP2.begindatum > PP.begindatum
);
If any Aggregation (Grouy by, Sum, Count, ...) in the Query or any joined query you can't edit the data.
You can write an function the sum for every row in the table.
精彩评论