Zip Code Match Query - match on first five digits only

[matt]

i'm trying to create an access 2000 query against a (linked) SQL 7.0 table
that will locate duplicate records based on first name, last name and the
first five digits of the zip. if somebody is listed twice, with a five
digit zip AND a zip+4, i want to be able to retrieve both records. i
obtained the following SQL statement using the duplicate query wizard but it
will only select records that match exactly on all three fields. how can i
modify it to only look at the first five digits of the zip or is it even
possible using SQL statements only?


In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY
[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And [ZipCode] = [dbo_tblData].[ZipCode])


sorry for the rookie question. please reply to the newsgroup. thank you
very much for your time.

matt

 

[Ken Snell]

Here's one way:

In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY

[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And Left([ZipCode], 5) =

Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

i'm trying to create an access 2000 query against a (linked) SQL 7.0 table
that will locate duplicate records based on first name, last name and the
first five digits of the zip. if somebody is listed twice, with a five
digit zip AND a zip+4, i want to be able to retrieve both records. i
obtained the following SQL statement using the duplicate query wizard but it
will only select records that match exactly on all three fields. how can i
modify it to only look at the first five digits of the zip or is it even
possible using SQL statements only?


In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY
[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And [ZipCode] = [dbo_tblData].[ZipCode])


sorry for the rookie question. please reply to the newsgroup. thank you
very much for your time.

matt

 

[matt]

ken, thank you very much for the quick reply!!

i tried this code and it's now prompting me to enter the zip code parameter
i want to search. is there any way to have it check on all matching zips
instead of a particular leading five? i tried entering ***** and !!!!!...to
no avail.

anyway, i sincerely thank you for your help!

matt

Here's one way:

In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY

[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And Left([ZipCode], 5) =

Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

i'm trying to create an access 2000 query against a (linked) SQL 7.0 table
that will locate duplicate records based on first name, last name and the
first five digits of the zip. if somebody is listed twice, with a five
digit zip AND a zip+4, i want to be able to retrieve both records. i
obtained the following SQL statement using the duplicate query wizard

but

it
will only select records that match exactly on all three fields. how

can

i
modify it to only look at the first five digits of the zip or is it even
possible using SQL statements only?


In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY
[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And [ZipCode] = [dbo_tblData].[ZipCode])


sorry for the rookie question. please reply to the newsgroup. thank you
very much for your time.

matt

 

[matt]

nevermind...user error!

thanks again, ken!

matt

ken, thank you very much for the quick reply!!

i tried this code and it's now prompting me to enter the zip code parameter
i want to search. is there any way to have it check on all matching zips
instead of a particular leading five? i tried entering ***** and !!!!!...to
no avail.

anyway, i sincerely thank you for your help!

matt

Here's one way:

In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY

[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And Left([ZipCode], 5) =

Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

i'm trying to create an access 2000 query against a (linked) SQL 7.0 table
that will locate duplicate records based on first name, last name and the
first five digits of the zip. if somebody is listed twice, with a five
digit zip AND a zip+4, i want to be able to retrieve both records. i
obtained the following SQL statement using the duplicate query wizard

but

it
will only select records that match exactly on all three fields. how

can

i
modify it to only look at the first five digits of the zip or is it even
possible using SQL statements only?


In (SELECT [FirstName] FROM [dbo_tblData] As Tmp GROUP BY
[FirstName],[LastName],[ZipCode] HAVING Count(*)>1 And [LastName] =
[dbo_tblData].[LastName] And [ZipCode] = [dbo_tblData].[ZipCode])


sorry for the rookie question. please reply to the newsgroup. thank you
very much for your time.

matt