Join Query

[Metcare]

I am trying to create a query from two tables that produces a result if the
(MemberID)field from table 1 matched the MemberID field in Table 2.
The first table came from another system (originally in excel) , and I
imported the file to access.
The memberID field in Table one has this text format H12345678 created
orignally in EXCEL, while the other table has the memberID field that was
populated using input masked in Access as H12345678. Access users in Table 2
only put the digits with the H automatically saved as part of the input
mask. So I believe the H is not really saved although I see it in the record.
My problem is , when I run my JOIN query based on the memberID, it is not
recognizing any matched records, even if we have several records that matched
since I think Table1 has the prefix H while Table2 does not actually have
the prefix H as when I query a memberID without the H , the record shows ,and
does not show when I query with H.
How do create a JOIN query to produce a list of records with matching
MemberID from table 1 and table 2.
Appreciate your help.

 

[Jeff Boyce]

Create a query that either concatenates the "H" or strips the "H" off. Base
your join against THAT query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Or you could do something like:

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE mid(Table1.MemberID, 2) = Table2.MemberID

or:

Select Table1.*, Table2.*
FROM Table1
INNER JOIN Table2
ON mid(Table1.MemberID,2) = Table2.MemberID


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.