Joined two tables

[Alex Martinez]

I have a tracking systems for logging in disputed (Insurance) claims. I
contains two different tables. Data gets uploaded into the database to both
tables. The table consist of the following fields:

Claims - Table
Policy_Number - text (field size 5)
Received - date
Policy_Type - text (field size 2)
AssignedTo - text (field size 10)

FinishedClaims - Table
I have another table that is joined
PolicyNumber - text (field size 5)
Completed - date
Assigned_To - text (field size 10)

What I want is to show all Policies in the Claims table and if there is a
match (PolicyNumber) in the FinishedClaims display the Completed date and
Assigned_To field . I have done this without a problem, but my problem is
if there are two exact same policy in the Claims table I run into problems
having the FinishedClaims PolicyNumber to join properly with the
Policy_Number in the Claims table. For example:

Claims - Table
PolicyNumber Received date Policy_Type AssignedTo
T2315 2/10/2006 AT Tim66
T2315 2/15/2006 GL anna98


Finsihed Claims - Table
Policy_Number Completed Assigned_To
T2315 2/12/2006 Tim66
T2315 2/20/2006 john88



Results I want:
PolicyNumber Received date
Policy_Type AssignedTo Policy_Number Completed
Assigned_To
Result 1 T2315 2/10/2006 AT
Tim66 2/12/2006 T2315 2/12/2006
Tim66
Result 2 T2315 2/15/2006 GL
anna98 2/20/2006 T2315 2/20/2006
john88


The problem I get is that Result 1 will have 2/20/2006 Completed date
instead of 2/12/2006. How can I get the result the way I have above? Any
tips or website to go will be appreciated. Thank you in advance.

 

[Tom Ellison]

Dear Alex:

It would seem that PolicyNumber T2315 is NOT a unique policy. There are two
completely different policies with this number, on of Type AT and another of
type GL. Is that true?

If a claim comes in for policy T2315-AT on 2/10/2006 and another claim cones
in for T2315-GL on the same day, how will you know which completion goes
with which? By the Assigned_To? Is the Assigned_To someone withing the
insurance company? Is there a chance both claims might be assigned to the
same person? Ever, just once?

What I suggest is that the PolicyNumber is not unique. Perhaps the
PolciyNumber and Type together are unique. Is that the case? If it is
unique, then you need to start tracking the Policy_Type in the
FinishedClaims table. Otherwise, what you will eventuall get is this:

Finsihed Claims - Table
Policy_Number Completed Assigned_To
T2315 2/20/2006 Tim66
T2315 2/20/2006 Tim66

Now which is which?

Instead, would this be unique:

Finsihed Claims - Table
Policy_Number Policy_Type Completed Assigned_To
T2315 AT 2/20/2006 Tim66
T2315 GL 2/20/2006 Tim66

If that's what it takes to make it unique, then that could be the solution.

Tom Ellison

 

[Alex Martinez]

Thanks Tom - Appreicate the help.

Dear Alex:

It would seem that PolicyNumber T2315 is NOT a unique policy. There are
two completely different policies with this number, on of Type AT and
another of type GL. Is that true?

If a claim comes in for policy T2315-AT on 2/10/2006 and another claim
cones in for T2315-GL on the same day, how will you know which completion
goes with which? By the Assigned_To? Is the Assigned_To someone withing
the insurance company? Is there a chance both claims might be assigned to
the same person? Ever, just once?

What I suggest is that the PolicyNumber is not unique. Perhaps the
PolciyNumber and Type together are unique. Is that the case? If it is
unique, then you need to start tracking the Policy_Type in the
FinishedClaims table. Otherwise, what you will eventuall get is this:

Finsihed Claims - Table
Policy_Number Completed Assigned_To
T2315 2/20/2006 Tim66
T2315 2/20/2006 Tim66

Now which is which?

Instead, would this be unique:

Finsihed Claims - Table
Policy_Number Policy_Type Completed Assigned_To
T2315 AT 2/20/2006 Tim66
T2315 GL 2/20/2006 Tim66

If that's what it takes to make it unique, then that could be the
solution.

Tom Ellison