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.

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 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 You can also automate the process with a few lines of code in VBA.

Have fun,

Sam

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"

I assume that you quoted your UPDATE SQL exactly. In that case, your SET code should be (you don't need caps, of course, I'm only showing what looks like a discrepancy). Also, you should have a complete clause: Sam

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!

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