Criteria for a Select Query

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

your criteria seems good to me. do you care to post the SQL

Chris wrote:
I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

--
spread the WORD

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200910/1

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

That is the SQL view of my query.

Again, I'm trying to match up the first three letters of the Last and First
names from the [Potential Members] table with the Last and First names from
the [834F] table.

"Duane Hookom" wrote:

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

Check my second post...it is in there...

In case you can't see it, here it is again:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

Thanks.

"NuBie via AccessMonster.com" wrote:

your criteria seems good to me. do you care to post the SQL

Chris wrote:
I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

--
spread the WORD

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200910/1

.

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

If the full name matched then your Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) should match.

also if your data look like these,

John MCPearson
John MCPeterson
John MCPerson

i believe it should too. I have nothing else



Chris wrote:
Check my second post...it is in there...

In case you can't see it, here it is again:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

Thanks.

your criteria seems good to me. do you care to post the SQL

[quoted text clipped - 15 lines]
I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200910/1

How about providing some sample records from the result. This all looks
appropriate to me.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

That is the SQL view of my query.

Again, I'm trying to match up the first three letters of the Last and First
names from the [Potential Members] table with the Last and First names from
the [834F] table.

"Duane Hookom" wrote:

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

Duane,

Obviously I can't give you my exact query results because I have a crap ton
of fields, but as an example I tested this:

In the [Potential Members] Table, I had this for a record:

[Last Name] = Clark-Brown
[First Name] = James
[Birthdate] = 11/13/1977

The [834F] Table had this as the record:

[MDCD_LAST_NAME] = Clark
[MDCD_FIRST_NAME] = James
[MDCD_DOB] = 11/13/1977


This record never came out on the query results which it should have because
the first three letters of both first and last names match and the Date of
Birth matches too. Even though the Last Names are not the same, it still
should pull that record because the first three letters of each name matches,
plus the full birthdate matches.

If you didn't know already, here is how my Table Joins are set up in the
query:

Join 1 - [Potential Members]![Last Name] to [834F]![MDCD_LAST_NAME]...option 1
Join 2 - [Potential Members]![First Name] to
[834F]![MDCD_FIRST_NAME]...option 1
Join 3 - [Potential Members]![Birthdate] to [834F]![MDCD_DOB]...option 1

I have the primary key set in the [Potential Members] Table to all 3 of
those fields.

Thanks.

"Duane Hookom" wrote:

How about providing some sample records from the result. This all looks
appropriate to me.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

That is the SQL view of my query.

Again, I'm trying to match up the first three letters of the Last and First
names from the [Potential Members] table with the Last and First names from
the [834F] table.

"Duane Hookom" wrote:

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

Duane,

Nevermind...I figured it out. I took out my two joins from the First Name
and Last Name fields and that took care of the problem. When I was doing the
joins, my SQL statement was being trumped by the join where the join was
matching the full field.

Thanks for your time helping me.

"Chris" wrote:

Duane,

Obviously I can't give you my exact query results because I have a crap ton
of fields, but as an example I tested this:

In the [Potential Members] Table, I had this for a record:

[Last Name] = Clark-Brown
[First Name] = James
[Birthdate] = 11/13/1977

The [834F] Table had this as the record:

[MDCD_LAST_NAME] = Clark
[MDCD_FIRST_NAME] = James
[MDCD_DOB] = 11/13/1977


This record never came out on the query results which it should have because
the first three letters of both first and last names match and the Date of
Birth matches too. Even though the Last Names are not the same, it still
should pull that record because the first three letters of each name matches,
plus the full birthdate matches.

If you didn't know already, here is how my Table Joins are set up in the
query:

Join 1 - [Potential Members]![Last Name] to [834F]![MDCD_LAST_NAME]...option 1
Join 2 - [Potential Members]![First Name] to
[834F]![MDCD_FIRST_NAME]...option 1
Join 3 - [Potential Members]![Birthdate] to [834F]![MDCD_DOB]...option 1

I have the primary key set in the [Potential Members] Table to all 3 of
those fields.

Thanks.

"Duane Hookom" wrote:

How about providing some sample records from the result. This all looks
appropriate to me.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

That is the SQL view of my query.

Again, I'm trying to match up the first three letters of the Last and First
names from the [Potential Members] table with the Last and First names from
the [834F] table.

"Duane Hookom" wrote:

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.

Maybe you didn't read my full first post:
"I expect you might still have the joins."

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

Duane,

Nevermind...I figured it out. I took out my two joins from the First Name
and Last Name fields and that took care of the problem. When I was doing the
joins, my SQL statement was being trumped by the join where the join was
matching the full field.

Thanks for your time helping me.

"Chris" wrote:

Duane,

Obviously I can't give you my exact query results because I have a crap ton
of fields, but as an example I tested this:

In the [Potential Members] Table, I had this for a record:

[Last Name] = Clark-Brown
[First Name] = James
[Birthdate] = 11/13/1977

The [834F] Table had this as the record:

[MDCD_LAST_NAME] = Clark
[MDCD_FIRST_NAME] = James
[MDCD_DOB] = 11/13/1977


This record never came out on the query results which it should have because
the first three letters of both first and last names match and the Date of
Birth matches too. Even though the Last Names are not the same, it still
should pull that record because the first three letters of each name matches,
plus the full birthdate matches.

If you didn't know already, here is how my Table Joins are set up in the
query:

Join 1 - [Potential Members]![Last Name] to [834F]![MDCD_LAST_NAME]...option 1
Join 2 - [Potential Members]![First Name] to
[834F]![MDCD_FIRST_NAME]...option 1
Join 3 - [Potential Members]![Birthdate] to [834F]![MDCD_DOB]...option 1

I have the primary key set in the [Potential Members] Table to all 3 of
those fields.

Thanks.

"Duane Hookom" wrote:

How about providing some sample records from the result. This all looks
appropriate to me.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

SELECT [Potential Members].[Last Name], [Potential Members].[First Name],
[Potential Members].Birthdate, [Potential Members].[Phone #], [Potential
Members].Region, [Potential Members].[Current Coverage], [Potential
Members].[Contact Date], [Potential Members].[Rep Initials],
[834F].MDCD_RECIPIENT_ID, [834F].MDCD_ELIG_EFF_DT, [834F].MDCD_FAM_LINK_ID,
[834F].FILE_EFF_DT
FROM 834F, [Potential Members]
WHERE (((Left([Potential Members]![Last
Name],3))=Left([834F]![MDCD_LAST_NAME],3)) AND ((Left([Potential
Members]![First Name],3))=Left([834F]![MDCD_FIRST_NAME],3)));

That is the SQL view of my query.

Again, I'm trying to match up the first three letters of the Last and First
names from the [Potential Members] table with the Last and First names from
the [834F] table.

"Duane Hookom" wrote:

Please copy and paste your expression. Clearly you are missing the (
following the first Left.

It would help if you provided the full SQL view from your query. I expect
you might still have the joins.

--
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

I have a table [Potential Members] that I am trying to match up the Last
Name, First Name, and Date of Birth to another table [834F]. I have created
join properties for those three fields to show only records where the joined
fields from both tables are equal.

I want to take the first name and last name fields and match up the first
three letters of both fields and then match the whole birthdate field. This
would give me a better chance for a match due to the [Potential Members]
table gets names entered in manually.

Here is what I have currently in my criteria which does not seem to be
working correctly:

LEFT[Potential Members]![Last Name],3) = LEFT([834F]![MDCD_LAST_NAME],3)

I thought this was working, but tested it was only pulling the names that
matched the full name and not just the first three letters of the field.