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

VBA in SQL -- finding near duplicities

P: n/a
Hi,

I am somehow experienced working with databases (mainly PostgreSQL on
Linux) and with scripting (bash, Python, Basic), but total newbie when
it comes to Access. However, in this temp job I need to find
duplicities in 120,000 records table and Access comes handy. I found in
this newsgroup some VBA functions for find near similarities in VBA
(http://groups.google.com/group/comp....092c980746e13),
but now I have hard time to integrate this function into SQL Query
created by Find Duplicates wizard.

This is what I've got from the wizard:

SELECT title, address1, address2, city, state, company, FirstName,
LastName,
zipcode, country, telephone, fax, [E-mail/URL], Source
FROM allThreeCombined
WHERE (((allThreeCombined.title) In
(SELECT [title] FROM [allThreeCombined] As Tmp GROUP BY
[title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And
[address1] = [allThreeCombined].[address1]
And
[address2] = [allThreeCombined].[address2]
And
[city] = [allThreeCombined].[city]
And
[state] = [allThreeCombined].[state]
And
[company] = [allThreeCombined].[company])))
ORDER BY title, address1, address2, city, state, company;

and followinig (as much as I can) advice of
http://office.microsoft.com/en-us/as...345581033.aspx I have
created this:

SELECT title, address1, address2, city, state, company, FirstName,
LastName, zipcode, country, telephone, fax, [E-mail/URL], Source
FROM bigTesting
WHERE (((bigTesting.title) In
(SELECT [title] FROM [bigTesting] As Tmp
GROUP BY [title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And SimilCmp([address1],[bigTesting].[address1]) = 1
And SimilCmp([address2],[bigTesting].[address2]) = 1
And SimilCmp([city],[bigTesting].[city]) = 1
And SimilCmp([state],[bigTesting].[state]) = 1
And SimilCmp([company],[bigTesting].[company]) = 1
)
))
ORDER BY title, address1, address2, city, state, company;

However, Access doesn't seem to like it (compile error). Any idea, what
I am doing wrong?

Thanks for any hint,

Matej

Jan 17 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
And why this query fails to compile?

SELECT title, company, address1, address2, city, state, FirstName,
LastName, zipcode, country, telephone, fax, inetAddress, Source
FROM bigTesting
WHERE (((bigTesting.title) In
(SELECT title FROM bigTesting As Tmp
GROUP BY title,company,address1,address2,city,state
HAVING Count(*)>1
And SimilCmp(company,bigTesting!company) = 1
And address1 = bigTesting.address1
And address2 = bigTesting.address2
And city = bigTesting.city
And state = bigTesting.state)))
ORDER BY title, company, address1, address2, city, state;

Any help would be really appreciated,

Matej

Jan 17 '06 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you have to assign an alias to the main query's table name and
use that alias in the subquery.

Main query:

FROM allThreeCombined AS M
Sub query:

[address1] = M.[address1]

.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ81vGYechKqOuFEgEQLSEgCfaNHhAP5KSAOkAviqzJla8U 1juQAAnRCy
S7YyZQeFnFJ6jnppP4ZWmS3r
=v7t5
-----END PGP SIGNATURE-----

Matej Cepl wrote:
Hi,

I am somehow experienced working with databases (mainly PostgreSQL on
Linux) and with scripting (bash, Python, Basic), but total newbie when
it comes to Access. However, in this temp job I need to find
duplicities in 120,000 records table and Access comes handy. I found in
this newsgroup some VBA functions for find near similarities in VBA
(http://groups.google.com/group/comp....092c980746e13),
but now I have hard time to integrate this function into SQL Query
created by Find Duplicates wizard.

This is what I've got from the wizard:

SELECT title, address1, address2, city, state, company, FirstName,
LastName,
zipcode, country, telephone, fax, [E-mail/URL], Source
FROM allThreeCombined
WHERE (((allThreeCombined.title) In
(SELECT [title] FROM [allThreeCombined] As Tmp GROUP BY
[title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And
[address1] = [allThreeCombined].[address1]
And
[address2] = [allThreeCombined].[address2]
And
[city] = [allThreeCombined].[city]
And
[state] = [allThreeCombined].[state]
And
[company] = [allThreeCombined].[company])))
ORDER BY title, address1, address2, city, state, company;

and followinig (as much as I can) advice of
http://office.microsoft.com/en-us/as...345581033.aspx I have
created this:

SELECT title, address1, address2, city, state, company, FirstName,
LastName, zipcode, country, telephone, fax, [E-mail/URL], Source
FROM bigTesting
WHERE (((bigTesting.title) In
(SELECT [title] FROM [bigTesting] As Tmp
GROUP BY [title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And SimilCmp([address1],[bigTesting].[address1]) = 1
And SimilCmp([address2],[bigTesting].[address2]) = 1
And SimilCmp([city],[bigTesting].[city]) = 1
And SimilCmp([state],[bigTesting].[state]) = 1
And SimilCmp([company],[bigTesting].[company]) = 1
)
))
ORDER BY title, address1, address2, city, state, company;

However, Access doesn't seem to like it (compile error). Any idea, what
I am doing wrong?

Thanks for any hint,

Matej

Jan 17 '06 #3

P: n/a
No, it wasn't it -- fortunately, I found that with better selection of
fields to make comparison I can do as well with straight test for
equality, so I didn't need it after all. However, I still don't know,
how to use custom VBA functions in WHERE clause of Access query. Oh
well -- life is full of mysteries :-).

Matej

Jan 20 '06 #4

P: n/a
Matej Cepl wrote:
Hi,

I am somehow experienced working with databases (mainly PostgreSQL on
Linux) and with scripting (bash, Python, Basic), but total newbie when
it comes to Access. However, in this temp job I need to find
duplicities in 120,000 records table and Access comes handy. I found in
this newsgroup some VBA functions for find near similarities in VBA
(http://groups.google.com/group/comp....092c980746e13),
but now I have hard time to integrate this function into SQL Query
created by Find Duplicates wizard.

This is what I've got from the wizard:

SELECT title, address1, address2, city, state, company, FirstName,
LastName,
zipcode, country, telephone, fax, [E-mail/URL], Source
FROM allThreeCombined
WHERE (((allThreeCombined.title) In
(SELECT [title] FROM [allThreeCombined] As Tmp GROUP BY
[title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And
[address1] = [allThreeCombined].[address1]
And
[address2] = [allThreeCombined].[address2]
And
[city] = [allThreeCombined].[city]
And
[state] = [allThreeCombined].[state]
And
[company] = [allThreeCombined].[company])))
ORDER BY title, address1, address2, city, state, company;

and followinig (as much as I can) advice of
http://office.microsoft.com/en-us/as...345581033.aspx I have
created this:

SELECT title, address1, address2, city, state, company, FirstName,
LastName, zipcode, country, telephone, fax, [E-mail/URL], Source
FROM bigTesting
WHERE (((bigTesting.title) In
(SELECT [title] FROM [bigTesting] As Tmp
GROUP BY [title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And SimilCmp([address1],[bigTesting].[address1]) = 1
And SimilCmp([address2],[bigTesting].[address2]) = 1
And SimilCmp([city],[bigTesting].[city]) = 1
And SimilCmp([state],[bigTesting].[state]) = 1
And SimilCmp([company],[bigTesting].[company]) = 1
)
))
ORDER BY title, address1, address2, city, state, company;

However, Access doesn't seem to like it (compile error). Any idea, what
I am doing wrong?

Thanks for any hint,

Matej


It seems that your custom function "SimilCmp()" is the cause. What does it return?

Maybe post the code for the function??

--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Jan 21 '06 #5

P: n/a
Having tried running your revised Query (the last one you posted) and
getting good results, in which my Query displayed duplicate records and
no others, my guess is that you have a syntax error in your function

SimilCmp()

and you need to correct that. To begin with, you might redefine it (as
I did) by renaming it and then adding the following code to your Module:
Public Function similCmp( _
str1 As String, _
str2 As String) _
As Integer

similCmp = 0

If str1 = str2 Then similCmp = 1

End Function 'similCmp
I understand that this is probably not how you want to define the
function, but it may allow you to determine what is going wrong. After
you have determined that the Query is working properly, you can perhaps
revisit this function and correct it.

-- Vincent Johns <vj****@alumni.caltech.edu>
Please feel free to quote anything I say here.

Matej Cepl wrote:
Hi,

I am somehow experienced working with databases (mainly PostgreSQL on
Linux) and with scripting (bash, Python, Basic), but total newbie when
it comes to Access. However, in this temp job I need to find
duplicities in 120,000 records table and Access comes handy. I found in
this newsgroup some VBA functions for find near similarities in VBA
(http://groups.google.com/group/comp....092c980746e13),
but now I have hard time to integrate this function into SQL Query
created by Find Duplicates wizard.

This is what I've got from the wizard:

SELECT title, address1, address2, city, state, company, FirstName,
LastName,
zipcode, country, telephone, fax, [E-mail/URL], Source
FROM allThreeCombined
WHERE (((allThreeCombined.title) In
(SELECT [title] FROM [allThreeCombined] As Tmp GROUP BY
[title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And
[address1] = [allThreeCombined].[address1]
And
[address2] = [allThreeCombined].[address2]
And
[city] = [allThreeCombined].[city]
And
[state] = [allThreeCombined].[state]
And
[company] = [allThreeCombined].[company])))
ORDER BY title, address1, address2, city, state, company;

and followinig (as much as I can) advice of
http://office.microsoft.com/en-us/as...345581033.aspx I have
created this:

SELECT title, address1, address2, city, state, company, FirstName,
LastName, zipcode, country, telephone, fax, [E-mail/URL], Source
FROM bigTesting
WHERE (((bigTesting.title) In
(SELECT [title] FROM [bigTesting] As Tmp
GROUP BY [title],[address1],[address2],[city],[state],[company]
HAVING Count(*)>1
And SimilCmp([address1],[bigTesting].[address1]) = 1
And SimilCmp([address2],[bigTesting].[address2]) = 1
And SimilCmp([city],[bigTesting].[city]) = 1
And SimilCmp([state],[bigTesting].[state]) = 1
And SimilCmp([company],[bigTesting].[company]) = 1
)
))
ORDER BY title, address1, address2, city, state, company;

However, Access doesn't seem to like it (compile error). Any idea, what
I am doing wrong?

Thanks for any hint,

Matej

Jan 21 '06 #6

P: n/a
Yeah,

that is probably the method how to solve it. However, the job has been
already finished (I found a way how to complete my job without near
similarities) and I do not have the access neither to M$ Access (no pun
inteneded) or the original data.

Thanks for the ideas anyway,

Matej

Feb 7 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.