Zip Code Match Query - match on first five digits only

  • Thread starter Thread starter matt
  • Start date Start date
M

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
 
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) =
Click to expand...
Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

matt said:
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
Click to expand...
 
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


Ken Snell said:
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) =
Click to expand...
Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

matt said:
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
Click to expand...
but
it
will only select records that match exactly on all three fields. how
Click to expand...
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
Click to expand...
Click to expand...
 
nevermind...user error!

thanks again, ken!

matt


matt said:
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


Ken Snell said:
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) =
Click to expand...
Left([dbo_tblData].[ZipCode],5))



--
Ken Snell
<MS ACCESS MVP>

matt said:
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
Click to expand...
but
it
will only select records that match exactly on all three fields. how
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top