By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Match first 3 characters of a field from two different tables

P: 5
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,
May 8 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
11 Replies


Expert 100+
P: 634
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
Expand|Select|Wrap|Line Numbers
  1. WHERE Left(tblOne.Forename,3) = Left(tblTwo.Forename,3) AND tblOne.Surname = tblTwo.Surname AND tblOne.DOB = tblTwo.DOB
??

HTH


MTB
May 8 '17 #2

NeoPa
Expert Mod 15k+
P: 31,419
I would suggest something on similar lines :
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblOne].*
  2.      , [tblTwo].*
  3. FROM   [tblOne]
  4.        INNER JOIN
  5.        [tblTwo]
  6.   ON   [tblOne].[Surname]=[tblTwo].[Surname]
  7.  AND   [tblOne].[DoB]=[tblTwo].[DoB]
  8. WHERE  (Left([tblOne].[Forename],3)=Left([tblTwo].[Forename],3))
May 8 '17 #3

P: 5
Expand|Select|Wrap|Line Numbers
  1. SELECT [Missing UPNs].*
  2.      , [PupilOnRoll]
  3.      , *
  4. FROM   [Missing UPNs]
  5.        INNER JOIN
  6.        [PupilOnRoll]
  7.   ON   [Missing UPNs].[Surname]=[PupilOnRoll].[Surname]
  8.  AND   [Missing UPNs].[DateofBirth]=[PupilOnRoll].[DoB]
  9. 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
May 9 '17 #4

Expert 100+
P: 634
Hi

You do no say which parameter is requested, but I thing this
Expand|Select|Wrap|Line Numbers
  1. Left([PupilOnRoll],3)
should be like this
Expand|Select|Wrap|Line Numbers
  1. Left([PupilOnRoll].[Forename],3)
??

MTB
May 9 '17 #5

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

Just got this returned..
May 9 '17 #6

Expert 100+
P: 634
Without seeing the full query difficult to be sure, but you may be missing a final closing bracket, ie
Expand|Select|Wrap|Line Numbers
  1.  WHERE (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3))
??

MTB
May 9 '17 #7

P: 5
Expand|Select|Wrap|Line Numbers
  1. SELECT [Missing UPNs].*
  2.      , [PupilOnRoll]
  3.      , *
  4. FROM   [Missing UPNs]
  5.        INNER JOIN
  6.        [PupilOnRoll]
  7.   ON   [Missing UPNs].[Surname]=[PupilOnRoll].[Surname]
  8.  AND   [Missing UPNs].[DateofBirth]=[PupilOnRoll].[DoB]
  9. 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
May 9 '17 #8

Expert 100+
P: 634
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
Expand|Select|Wrap|Line Numbers
  1. WHERE (Left([MissingUPNs].[Forename],3)=Left([PupilOnRoll].[Forename],3))
should be this
Expand|Select|Wrap|Line Numbers
  1. 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
May 9 '17 #9

P: 5
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
May 9 '17 #10

Expert 100+
P: 634
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
May 9 '17 #11

NeoPa
Expert Mod 15k+
P: 31,419
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.
May 9 '17 #12

Post your reply

Sign in to post your reply or Sign up for a free account.