Joining fields without using prefix

[Alex Martinez]

Hello,

I have the following tables that I am trying to join, but I am running into
some problems:

Table called "Claims"
Policy_Number - text (field size 16)
PolicyDate - date field

Table called - "Actual"
PolicyNumber - text (field size 16)
ClaimsAmount - currency field

What I want to do is to join the "Claims" table data - Policy_Number with
the "Actual" table PolicyNumber. The problem is that the "Claims"
Policy_Number and the Acutal PolicyNumber uses different prefix for example:


Claims table
Policy_Number PolicyDate
TX72345678901267 2/10/2006


Actual table
Policy_Number ClaimsAmount
RZ72345678901267 500,000
AI34567891057916 600,000



What I want to see in my query is:
Policy_Number PolicyDate Policy_Number
ClaimsAmount
TX72345678901267 2/10/2006 RZ72345678901267 500,000


The problem is I have two letter prefix, which I want to ignore and only
pickup the number. The numbers are unique. If anybody has any tips or
website to visit I will appreicate. Thank you in advance

 

[Guest]

SELECT Claims.Policy_Number AS ClaimsPolicyNumber,
Claims.PolicyDate,
Actual.Policy_Number AS ActualPolicyNumber,
Actual.ClaimsAmount
FROM Actual, Claims
WHERE Mid([Claims].[Policy_Number],3)=Mid([Actual].[Policy_Number],3);

I used the mid function to get rid of the first two characters then joined
them together in the Where clause.. I'm assuming by your example that's the
way it is. If wrong, we'll have to try something else.

 

[Alex Martinez]

Jerry,

Thanks for your help.

Regards

SELECT Claims.Policy_Number AS ClaimsPolicyNumber,
Claims.PolicyDate,
Actual.Policy_Number AS ActualPolicyNumber,
Actual.ClaimsAmount
FROM Actual, Claims
WHERE Mid([Claims].[Policy_Number],3)=Mid([Actual].[Policy_Number],3);

I used the mid function to get rid of the first two characters then joined
them together in the Where clause.. I'm assuming by your example that's
the
way it is. If wrong, we'll have to try something else.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I have the following tables that I am trying to join, but I am running
into
some problems:

Table called "Claims"
Policy_Number - text (field size 16)
PolicyDate - date field

Table called - "Actual"
PolicyNumber - text (field size 16)
ClaimsAmount - currency field

What I want to do is to join the "Claims" table data - Policy_Number with
the "Actual" table PolicyNumber. The problem is that the "Claims"
Policy_Number and the Acutal PolicyNumber uses different prefix for
example:


Claims table
Policy_Number PolicyDate
TX72345678901267 2/10/2006


Actual table
Policy_Number ClaimsAmount
RZ72345678901267 500,000
AI34567891057916 600,000



What I want to see in my query is:
Policy_Number PolicyDate Policy_Number
ClaimsAmount
TX72345678901267 2/10/2006 RZ72345678901267 500,000


The problem is I have two letter prefix, which I want to ignore and only
pickup the number. The numbers are unique. If anybody has any tips or
website to visit I will appreicate. Thank you in advance