Originally Posted by PracticalProgram
How are the records in the TRValue table related to the records in any of the other tables you are using?
Whenever I write an UPDATE query, I first write it as a SELECT query, so I can validate that it is filtering for the records I'm interested in. Only after I am satisfied that it is filtering correctly, do I move on to converting it to an UPDATE query.
Let me start with the general queries and then move to the specifics.
This is the query that gets all possible parent records:
V.Year, V.Parcel, V.Acres,
V.CurrentMarket, V.CurrentTaxable, V.CurrentTaxAmt
FROM TRValue V
WHERE V.Year = 2012 and V.Code = 'LG01'
And this is the query that returns all the child records with summations on the appropriate fields:
SELECT Year, Parcel,
SUM(G.Acres) as SumAcres,
SUM(G.Market) as SumMarket,
SUM(G.Taxable) as SumTaxable
FROM TRGreen G
WHERE G.Year = 2012 and G.Status = 0
GROUP BY G.Year, G.Parcel
The general idea is to update the parent record with the summation of its child records.
The *extras* that make it more difficult include:
1. The CurrentTaxAmt in the parent record is a calculation based on the last two inner joins of the original post.
INNER JOIN TRMaster M on M.Year = V and M.Parcel = V.Parcel
INNER JOIN TRDistrict D on D.Year = M.Year and D.Code = M.District
. . .
(part of the update statement)
V.CurrentTaxAmt = V.CurrentTaxable * D.CertifiedRate
2. It would be nice if there was a way to have the query use a table that holds a list of accounts that should be updated. Rather than update every parent record, just do the ones in the "other" table.
The linking fields, as you may have gathered by now, include Year and Parcel.
Thanks for your suggestions.