Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Unanswered: Combining queries

    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
    FROM OutPutTable
    GROUP BY OutPutTable.ParentID;

    -- PopulateTable: ProductionCost
    INSERT INTO ProductionCost ( ParentID, ProductionCost )
    SELECT qryProductionCost.ParentID, qryProductionCost.ProductionCost
    FROM qryProductionCost;

    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?

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Why not try:


    INSERT INTO ProductionCost ( ParentID, ProductionCost )
    SELECT OutPutTable.ParentID, Sum(OutPutTable.Cost)
    FROM OutPutTable
    GROUP BY OutPutTable.ParentID;

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •