Guessing this is a fairly easy one if you know what you're actually doing. I'd like to combine these two queries into a single query.
-- Calculate ProductionCost (query is named qryProductionCost)
SELECT OutPutTable.ParentID, Sum(OutPutTable.Cost) AS ProductionCost
GROUP BY OutPutTable.ParentID;
-- PopulateTable: ProductionCost
INSERT INTO ProductionCost ( ParentID, ProductionCost )
SELECT qryProductionCost.ParentID, qryProductionCost.ProductionCost
It seemed like I should be able to just convert the first query to a subquery used in the INSERT query's SELECT statement but not having much luck so far.
Also, is there a way to change either the second query, or the combined version, to an UPDATE statement if there's a record in the ProductionCost table whose ParentID matches the ParentID of what it's inserting? I.e., insert it if it doesn't exist, update it if it does?
Ended up solving my own problem using a statement similar to your's Matthew. For some reason I used DSUM instead of SUM but same result. I think previously I ran into problems with the GROUP BY portion and abandoned that approach.
Now off to find a way to do a conditional insert or update! Thanks for the help.