Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    48

    Unanswered: Combining Two Queries

    Hi,

    Sorry if this is posted somewhere but I can't find it.

    I have two queries that run off of the same table. One query pulls all the renewal premiums :

    SELECT Bulk_id_mapping.BulkID, Sum(import.[Premium Standard]+import.[Premium SubStandard]+import.[Flat Extra Premium]-import.[policy fee]) AS [Renewal premium], Sum(import.[Allowance Standard]+import.[Allowance SubStandard]+import.[Flat Extra Allowance]-import.[policy fee Allowance]) AS [Renewal Allowance], sum(import.[Premium Tax]) AS [Premium Tax], sum(import.FET) AS FET, sum(import.[Administration Fee Amount]) AS [Administration Fee]
    FROM Bulk_id_mapping INNER JOIN import ON Bulk_id_mapping.[Sage Treaty]=import.Treaty
    WHERE (((import.[Policy Duration])<>1))
    GROUP BY Bulk_id_mapping.BulkID;


    and the other pulls all of the first year premiums:

    SELECT Bulk_id_mapping.BulkID, Sum(import.[Premium Standard]+import.[Premium SubStandard]+import.[Flat Extra Premium]-import.[policy fee]) AS [first year premium], Sum(import.[Allowance Standard]+import.[Allowance SubStandard]+import.[Flat Extra Allowance]-import.[policy fee Allowance]+import.[Administration Fee Amount]) AS [first year Allowance]
    FROM Bulk_id_mapping INNER JOIN import ON Bulk_id_mapping.[Sage Treaty]=import.Treaty
    WHERE (((import.[Policy Duration])=1))
    GROUP BY Bulk_id_mapping.BulkID;



    I can't use a union query as not every bulkid has first year premiums.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I can't use a union query as not every bulkid has first year premiums.
    That being the case, I think you should run the renewal query first, not as a SELECT query, but a MakeTable query. Make sure you add zero-value fields to make room for the next step. You might name the new table tblTotPremium.

    Unfortunately, you can't run the second query as an Update query, with an
    Code:
    INNER JOIN tblTotPremium ON Bulk_id_mapping.BulkID = tblTotPremium.BulkID
    because Updates don't work with Totals queries. You have to run the second query as another MakeTable query. You can name the new table, perhaps, tblFrstPremium.

    Now you can run an Update query on tblTotPremium with an
    Code:
    INNER JOIN tblFrstPremium ON tblTotPremium.BulkID = tblFrstPremium.BulkID
    You can also automate the process with a few lines of code in VBA.

    Have fun,

    Sam

  3. #3
    Join Date
    Sep 2011
    Posts
    48
    ok I made queries so it updates the fields, but it still doesn't work!

    INSERT INTO renewalstore ( bulkid, [renewal premium], [renewal allowance], [premium tax], fet, [administration fee], fyp, fya )
    SELECT Bulk_id_mapping.BulkID, Sum(import.[Premium Standard]+import.[Premium SubStandard]+import.[Flat Extra Premium]-import.[policy fee]), Sum(import.[Allowance Standard]+import.[Allowance SubStandard]+import.[Flat Extra Allowance]-import.[policy fee Allowance]), sum(import.[Premium Tax]), sum(import.FET), sum(import.[Administration Fee Amount]), 0, 0
    FROM Bulk_id_mapping INNER JOIN import ON Bulk_id_mapping.[Sage Treaty]=import.Treaty
    WHERE (((import.[Policy Duration])<>1))
    GROUP BY Bulk_id_mapping.BulkID;

    INSERT INTO firstyear ( bulkid, fyp, fya )
    SELECT Bulk_id_mapping.BulkID, Sum(import.[Premium Standard]+import.[Premium SubStandard]+import.[Flat Extra Premium]-import.[policy fee]), Sum(import.[Allowance Standard]+import.[Allowance SubStandard]+import.[Flat Extra Allowance]-import.[policy fee Allowance]+import.[Administration Fee Amount])
    FROM Bulk_id_mapping INNER JOIN import ON Bulk_id_mapping.[Sage Treaty]=import.Treaty
    WHERE (((import.[Policy Duration])=1))
    GROUP BY Bulk_id_mapping.BulkID;

    So I try using this code to update it:

    UPDATE renewalstore INNER JOIN firstyear ON renewalstore.bulkid=firstyear.bulkid SET renewal.fyp = firstyear.fyp;

    But when i try, it asks me to type in a number, so I guess it doesn't find it? and then it gives me the error "cannot update "fyp"; field not updateable"

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    So I try using this code to update it:

    UPDATE renewalstore INNER JOIN firstyear ON renewalstore.bulkid=firstyear.bulkid SET renewal.fyp = firstyear.fyp;

    But when i try, it asks me to type in a number, so I guess it doesn't find it? and then it gives me the error "cannot update "fyp"; field not updateable"
    I assume that you quoted your UPDATE SQL exactly. In that case, your SET code should be
    Code:
    SET renewalSTORE.fyp = firstyear.fyp
    (you don't need caps, of course, I'm only showing what looks like a discrepancy). Also, you should have a complete clause:
    Code:
    SET renewalstore.fyp = firstyear.fyp, renewalstore.fya = firstyear.fya
    Sam

  5. #5
    Join Date
    Sep 2011
    Posts
    48
    wow thank you so much! Just pure stupidity on my part, i was fooling around with this forever, and the whole time I just had my table name wrong .

    Again thanks for the help!

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're very welcome.

    If you had used Access' query designer, you would not have made such an error. The designer takes care of the finessing of names, etc., in the background.

    Sam

Posting Permissions

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