how to join tables with similar values in query

[kdt]

Can someone please help with the following:

I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.

Thanks

 

[Lightwave]

Yes definitely possible..

In the following explanation.
I will assume you have two tables.
The first table1 has names such as "John Wayne" in a field fullname along with any other data.
The second table2 just should have names such as "Wayne" in a separate field along with other data all be it in separate fields.

First off make up a query with the following two calculated expressions

Expr1: InStr(1,Table1!Fullname," ",0)

Expr2: Right(Table1!Fullname,(Len(Table1!Fullname))-[Expr1])

Expr 1 counts where the space within table1 field fullname is.

The second expression counts the number of characters in the full string and then selects the string less the count for Expr 1 - result you should just get "Wayne" appearing in the Expr 2 calculation.

This query which is based on Table 1 should now be able to be matched directly to table 2.

Note if you have triple barreled names you may get inconsistencies and problems. It will be possible to write these out by using code but I haven't laid it out here...

 

[kdt]

Thanks a lot. I have tried this and it works to some extent but I had tried to simplify the problem in an attempt to explain it clearer

The fields I use are as follows:
StudyNumber (full name) which is in the format 06PJ32.512086
TA (first name- I said surname by mistake) =06PJ32

I tried to change your expressions to take this into account by using

Expr1: InStr(1,Table1!StudyNumber,".",0)
Expr2: Left(Table1!StudyNumber,(Len(Table1!StudyNumber))-[Expr])

Some of the results this returns includes the full stop, as I'm only interested in the numbers before the full stop, would you be able to adapt this??

Currently I am getting a "data type mismatch". Thanks again for your help

 

[Lightwave]

Your along the right lines...

Replace Expression 2 with this

Expr2:Left(Table1!StudyNumber,[Expr1]-1)

You no longer require the Len item as you are counting from the left. Rather than counting from the right when taking out the new string (this was why you were getting . in some of the answers.

As for the data type mismatch - check the properties of the two fields you are trying to match they should be the same

 

[kdt]

Used your suggestion and it calculates the expression perfectly

The only problem I have now is I cant get the query that links the two queries to work. I've tried everything and it keeps coming back with the "invalid call procedure" message.

This is the SQL:

SELECT qryUniqueSamples.Sa_StudyNumber, qryConverforTANum.DateCompleted
FROM qryConverforTANum INNER JOIN qryUniqueSamples ON qryConverforTANum.Expr2 = qryUniqueSamples.Sa_TANumber;

Can you use a calculated expression to form the join?

Ahh, so close

 

[Lightwave]

Yes you can use a calculated expression to form the join...

Provided the data types are the same - it ain't going to do it if the data types are different..


The example I was using ended up with the following SQL and seemed to work fine..

SELECT Query1.Expr1, Table2.additional, Table2.money
FROM Table2 INNER JOIN Query1 ON Table2.Surname = Query1.Expr1;

I'm not entirely sure what the invalid call procedure is talking about but feel sure that with a little bit of tweaking you'll be there...

 

[kdt]

Cheers a lot mate, finally got it to work

Turns out the problems was data in the original tables either missing the full stop or was blank, I've made this field compulsary now. Wasn't much fun wading through thousands of records though. This is my first db so I'll make sure I learn from it.

Couldn't have done it without your help Lightwave so thanks a lot.