table of claims

[Guest]

Hello,
I have four category tables of claims with various procedure codes that fall
into those four categories. Some people receive two categories, some people
receive three, etc. I want to make a list of people (ssn) with their claim
start and claim end dates that shows which combination of categories each ssn
received. Help!

 

[Guest]

The four category tables should be twotables:
tblCategory
CATEGORY_ID - Autonumber primary key
CATEGORY_DESCRIPTON - Text

tblProcedures
PROC_ID - Autonumber primary key
CATEGORY_ID - Long Integer - foreign key to tblCategory (what category does
this procedure belong to)
PROC_DESCRIPTION - Text

and to put the table you are asking for together:

tblCategoryAssignments
SSN - Text primary key
CATEGORY_ID - Long Integer foreign key to tblCategory
PROC_ID - Long Integer foreign key to tblProcedures
ASSIGN_START_DATE - Date
ASSIGN_END_DATE - Date

 

[Guest]

Thank you Klatuu. There are sixteen possible service combinations of the four
categories of services received. Do you 1.make four fields for all four
service types for one individual, or 2. allow sixteen codes for the
tblCategory.

 

[Guest]

You would allow the sixteen codes for the individual. Or stated a little
differently, there should be one record in tblCategoryAssignments for each
SSN/Category/Procedure combination.

 

[Guest]

There are 53 procedures that fit into the 4 categories. There are 16 possible
combinations of the 4 categories. When I choose a possible combination of
categories 1 + 2 as services received for a ssn, how can I be sure that ssn
didn't get categories 3 + 4. Ah, the joys of Friday afternoon with a
difficult database.
Thanks!
mary

 

[Guest]

Can one Procedure be associated with more than one Category?
If not, then the model I proposed is the normal way to approach this in a
relational database.

What do you mean by 16 possible combinations of the 4 categories? If there
are only 4 categories, then one SSN can not have more than 4 categories.
They may have the same category more than once if they have multiple
procedures for the same category. The model I provided addresses that
problem. So, if an SSN had every procedure in the database, then that SSN
would have 53 records in the tblCategoryAssignments table.

 

[Guest]

Combination are 1 (not 2,3,4) 1+2 (not 3,4), 1+4 (not 2,3). Sorry to be so
thick. I still dont understand the sequence of the queries in order to show
which of the sixteen categories each ssn received. Thanks anyway.

 

[Guest]

Sorry, Mary, I don't understand what this means:
Combination are 1 (not 2,3,4) 1+2 (not 3,4), 1+4 (not 2,3).