C
CAM
Hello,
I need an expert on this one who has encounter a similar situtation. I have
two tables that I am trying to join, the problem is that none of the tables
have a unique key making it hard to join properly. The first tables contain
the following fields:
Audit Table
PolicyNumber - (Text field)
Received Date - (Date field)
The other table is called - Invoice
Policy_Number - (Text field)
Invoice Date - (Date field)
Here is an example of what I am trying to match.
In the Audit Table I trying to match PolicyNumber with the Policy_Number in
the Invoice table. In this case I am using the same policy number, but they
have different Received Date in the Audit Table:
PolicyNumber Received Date
XX12 4/5/2006
XX12 4/18/2006
XX12 4/26/2006
In the Invoice table I have the following:
Policy_Number Invoice Date
XX12 4/8/2006
XX12 4/21/2006
XX12 4/30/2006
I not getting the correct result. The policies are matching correctly.:
Below is what it should look like
PolicyNumber Received Date Invoice Date
XX12 4/5/2006 4/8/2006
XX12 4/18/2006 4/21/2006
XX12 4/26/2006 4/30/2006
The key here is that the Invoice Date will be before the next same policy
Received Date on the Audit table: For example PolicyNumber XX12 Received
4/5/2006 must match with the Invoice table dated 4/8/2006. Likewise the
Received Date on the same policy number at 4/18/2006 should match with
4/21/2006 Invoice table and Received date 4/26/2006 must match with invoice
date 4/30/2006. It is difficult to match, especially when there is no
primary key. The policy numbers are not unique at all the only thing that
is unique is the received date and the invoice date. If anyone has an idea
I would really appreciate the help. This is a major problem for the
database I am creating. Anybody with has any tips or suggest to visit a
website I will appreciate the help. Thank you in advance
I need an expert on this one who has encounter a similar situtation. I have
two tables that I am trying to join, the problem is that none of the tables
have a unique key making it hard to join properly. The first tables contain
the following fields:
Audit Table
PolicyNumber - (Text field)
Received Date - (Date field)
The other table is called - Invoice
Policy_Number - (Text field)
Invoice Date - (Date field)
Here is an example of what I am trying to match.
In the Audit Table I trying to match PolicyNumber with the Policy_Number in
the Invoice table. In this case I am using the same policy number, but they
have different Received Date in the Audit Table:
PolicyNumber Received Date
XX12 4/5/2006
XX12 4/18/2006
XX12 4/26/2006
In the Invoice table I have the following:
Policy_Number Invoice Date
XX12 4/8/2006
XX12 4/21/2006
XX12 4/30/2006
I not getting the correct result. The policies are matching correctly.:
Below is what it should look like
PolicyNumber Received Date Invoice Date
XX12 4/5/2006 4/8/2006
XX12 4/18/2006 4/21/2006
XX12 4/26/2006 4/30/2006
The key here is that the Invoice Date will be before the next same policy
Received Date on the Audit table: For example PolicyNumber XX12 Received
4/5/2006 must match with the Invoice table dated 4/8/2006. Likewise the
Received Date on the same policy number at 4/18/2006 should match with
4/21/2006 Invoice table and Received date 4/26/2006 must match with invoice
date 4/30/2006. It is difficult to match, especially when there is no
primary key. The policy numbers are not unique at all the only thing that
is unique is the received date and the invoice date. If anyone has an idea
I would really appreciate the help. This is a major problem for the
database I am creating. Anybody with has any tips or suggest to visit a
website I will appreciate the help. Thank you in advance
