yet another query question re: updating a table

[Kyosa]

basicall i have a large table containing medical codes the codes can have modifiers (separate field) and so they may be listed more than once. Each code also has a relative unit value assigned based on modifier. like so:<br><br>cpt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;mod&nbsp;&nbsp;1999value<br>10050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.25<br>74000&nbsp;&nbsp;&nbsp;&nbsp;tc&nbsp;&nbsp;&nbsp;&nbsp;.25<br>74000&nbsp;&nbsp;&nbsp;&nbsp;26&nbsp;&nbsp;&nbsp;&nbsp;.15<br>74000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.40&nbsp;&nbsp;<br><br>my problem is as the calendar year changes so does the relative value. what is the best way to update my table to add the new year's relative values.? i want to keep the old data and just athe new values. <br><br>the other wrinkle is that as the year changes some of the medical codes may have been deleted so there may not be a value listing for that code. <br><br>currently i have separate tables listing my 99,00,& 01 values but want to combine it all into one master list just adding any new codes and values to the code listing. <br><br>i've run queries and created tables that show me the differences bwtween the three values tables but how can i then do an update based on anther table. ie. if the code, modifier, etc in my 1999 tables equals the code and modifier combination in my 2000 tables then add the rvu from my 2000 table into my 1999 table. everything i've tried has given me all kinds of dup values..it turned an 11,000 record table into a 30-some thousand one by the time i was done ...i should only end up with a hundred or so new records .<br><br>ok..i know i'm not explaining that very well..but if anyone has any ideas on this i would be EXTREMELY greatful.&nbsp;&nbsp;&nbsp;<br>can you say AAARRRGGGHHH!!! (ok timne for my margarita!)<br><br> <p>Kyosa<br><a href=mailto: > </a><br><a href= > </a><br>"The key to flexibility is indecision!"<br>
- unknown<br>
<br>

 

[Akrogh]

I'm not sure if I understand you correctly but ...<br><br>Try adding a field to each of the 3 tables to hold the year. I.e. The table that holds 1999 data gets a field with the value 1999 in it, etc.&nbsp;&nbsp;Then you just union the 3 tables to get your master table.<br><br>The master table keeps growing, at year change you append all records from the previous year and change the 'year' field accordingly.