Question about Tables and updating

[Guest]

If i have one table "template" and copy and past to create 6 other tables
from "template", and add some columns to "template". Is there a way to have
the other 6 tables update with the new columns?

Thanks

Brad

 

[John W. Vinson]

If i have one table "template" and copy and past to create 6 other tables
from "template", and add some columns to "template". Is there a way to have
the other 6 tables update with the new columns?

WHOA.

Take one large step back.

Why do you feel that you need to do this? In a typical Access database, you'll
create a normalized set of tables at the time you design the database, and
never need to create another table again.

WHY are you doing this? What do you do with seven newly created columns? Why
would you use copy and paste (rather than a MakeTable query say) to do so?

I really think there must be a better way to accomplish the business need for
what you're doing!

John W. Vinson [MVP]

 

[Guest]

John,
This is what I am doing;
I would like to create a database that would contain Medical CPT codes with
there payable amounts, and other info... We are par with many insurance
compnaies. My thought process was to create one table per insurance company/
or ins company plan, then populate those tables with the annual reimbursement
and rvu information. This information changes all the time, i was thinking
that one table per ins company was the way to go. My "template" table has
all our CPT codes with there information including RVU data, I created 5 or 6
other tables with like Medicare Aetna etc from that "template" and will enter
the reimbursemnts, is this the way to go?

Hope this explains

Brad

 

[John W. Vinson]

John,
This is what I am doing;
I would like to create a database that would contain Medical CPT codes with
there payable amounts, and other info... We are par with many insurance
compnaies. My thought process was to create one table per insurance company/
or ins company plan, then populate those tables with the annual reimbursement
and rvu information. This information changes all the time, i was thinking
that one table per ins company was the way to go. My "template" table has
all our CPT codes with there information including RVU data, I created 5 or 6
other tables with like Medicare Aetna etc from that "template" and will enter
the reimbursemnts, is this the way to go?

Emphatically NOT.

Storing data - company names - in the names of Tables is simply WRONG.
Storing data - CPT codes - in fieldnames, as you seem to be doing, is even
*worse*.

I'm not sure I understand everything about health insurance (does anyone!?)
but I'd suggest that you have a many to many relationship between Companies
and CPT Codes. A proper data structure would use (at least) three tables:

Companies
CompanyID <sutonumber or industry standard code if there is one>
CompanyName
<other info about the company itself, contact info, etc.>

CPTCodes
CPTCode <Primary Key> <don't let Access use an autonumber, the CPT is
already a good, stable, short, unique primary key>
Condition <what this code means>
<other info about the CPT code as an entity, if any>

ReimbursementRates
CompanyID <link to Companies>
CPTCode <link to CPTCodes>
Rate <*this* company's reimbursement rate for *this* code>
<any other info about this company's handling of this code>

This will let you easily add new companies, new CPT codes, and add or edit
reimbursement rates without needing to create new tables, or alter the design
of your tables.

John W. Vinson [MVP]