Match first 3 characters of a field from two different tables

[crossls28]

Hi,

I have two different tables with forenames, surnames and DOB. I have done a straight forward match but wanted to see a match where Surname and DOB Matched exactly but match the forename from the first 3 characters (some are spelt slightly differently)

How would I go about this?

Thanks,

[MikeTheBike]

Hi

Without seeing you query it is a bit difficult to suggest a definitive answer, but I imagine somewhere you will need something like this

WHERE Left(tblOne.Forename,3) = Left(tblTwo.Forename,3) AND tblOne.Surname = tblTwo.Surname AND tblOne.DOB = tblTwo.DOB

??

HTH


MTB

[NeoPa]

I would suggest something on similar lines :

SELECT [tblOne].*
     , [tblTwo].*
FROM   [tblOne]
       INNER JOIN
       [tblTwo]
  ON   [tblOne].[Surname]=[tblTwo].[Surname]
 AND   [tblOne].[DoB]=[tblTwo].[DoB]
WHERE  (Left([tblOne].[Forename],3)=Left([tblTwo].[Forename],3))

[crossls28]

SELECT [Missing UPNs].*
     , [PupilOnRoll]
     , *
FROM   [Missing UPNs]
       INNER JOIN
       [PupilOnRoll]
  ON   [Missing UPNs].[Surname]=[PupilOnRoll].[Surname]
 AND   [Missing UPNs].[DateofBirth]=[PupilOnRoll].[DoB]
WHERE  (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll],3))

I gave this a go but it's asking me to enter a parameter. I just need it to list all those that match. I'm not searching for ones in particular i.e. all that start with ste

Thanks,
Sam

[MikeTheBike]

Hi

You do no say which parameter is requested, but I thing this

Left([PupilOnRoll],3)

should be like this

Left([PupilOnRoll].[Forename],3)

??

MTB

[crossls28]

Syntax error in query expression '(Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3);'

Just got this returned..

[MikeTheBike]

Without seeing the full query difficult to be sure, but you may be missing a final closing bracket, ie

 WHERE (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3))

??

MTB

[crossls28]

SELECT [Missing UPNs].*
     , [PupilOnRoll]
     , *
FROM   [Missing UPNs]
       INNER JOIN
       [PupilOnRoll]
  ON   [Missing UPNs].[Surname]=[PupilOnRoll].[Surname]
 AND   [Missing UPNs].[DateofBirth]=[PupilOnRoll].[DoB]
WHERE  (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3));

This is the full query, I put in the extra bracket. Upon pressing run it prompted for parameters

[MikeTheBike]

AS I said before you do not say which parameter it request, but I think you have missed the space from the table name.
This

WHERE (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3))

should be this

WHERE (Left([Missing UPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3))

??

It is not considered good practice to have spaces in table or field names!

MTB

[crossls28]

It's not my Table i'm just having to make do with what i've been given unfortunately. At present when i press run it's asking for a parameter for the following

• Missing UPNs
• PupilOnRoll
• Missing UPNs.DateofBirth

Thanks in advance

[MikeTheBike]

If anything inside square brackets is not recognised then you will be asked to provide a value manually.

However this can get confusing if there are other syntax errors. For instance in your early post I have just noticed this
[PupilOnRoll],*
has a comma when a period is required.

If that does not progress it any, then I suggest you (temporarily) simplify the whole thing to and just include the field/tables it does not recognise in the SELECT statement (without the WHERE clause).

The other thing we do not know is how/where this query is being used, which may or may not have a bearing on it!


MTB

[NeoPa]

Hi Sam.

I've changed your posts #4 & #8 to show the SQL in a tidy format and you can easily see the problem. Tidiness and formatting are not simply nice to have pretty features. Without them you will easily miss obvious errors. How much time could have been saved?

Let us know if you are still having problems with your SQL once the obvious typo has been sorted out. If so, we'd prefer properly formatted and displayed SQL to work with.

It's easy to see such mistakes as wasting our time, but frankly, until you understand the importance of these things, how would you know? So, no worries about past errors. Just see how it goes forward from here.

BTW that tip is for general work and isn't restricted to when you post your questions in here.

Good luck.

NB. Mike is an experienced Access expert so when he gives advice, such as generally avoiding embedded spaces in table ( or other object) names, it's worth paying attention. Most here would give the same advice.