Fuzzy query - find almost matching

D

DavPet

I need a query that will show where two fields "almost" match.

Text Field one has data like 1FW 234 where field two has 1F W 234 or
1FW23-4.

Any suggestions?
 
M

Michel Walsh

Hi,

use a criteria like:



LIKE "*" & "1" & "*" & "F" & "*" & .... & "*" & "4" & "*"

ie, have all characters (except those that may not be present, like space,
or - ) in the right order, but interspersed with * (using Jet, % if using
MS SQL Server).


Hoping it may help,
Vanderghast, Access MVP
 
D

DavPet

That worked for my example but I have billions (hundreds) of examples.

any more help ?
 
J

John Vinson

I need a query that will show where two fields "almost" match.

Text Field one has data like 1FW 234 where field two has 1F W 234 or
1FW23-4.

Any suggestions?
Click to expand...

What constitutes a "match"? I'm guessing that you simply want to
ignore all blanks and punctuation characters, and match only if the
same alphanumeric characters occur in the same sequence... right?

One way would be to use a VBA function to strip out all unwanted
characters; if you indeed have millions of records, you'll probably
want to denormalize a bit and store this stripped-down value in an
indexed text field, and join on this field.


John W. Vinson[MVP]
 
T

tamxwell

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the numbers
(say number 8 comes up) in all of the fields? Here's a look at it. You
helped me with the UNION before. I'll list the UNION, then the Count query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
 
J

John Spencer (MVP)

It would really be better to start your own thread. It stands out as being a
question you asked. By the way, what is your question? Did your attempt fail?
Did you get an error message? Did you get the wrong results? Are you trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber
 
T

tamxwell

Yep, I did, but Michel had helped me on another query, so I figured..... My
db has 6 fields all populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the numbers
(say number 8 comes up) in all of the fields? No error message, just does
not return any results.

John Spencer (MVP) said:
It would really be better to start your own thread. It stands out as being a
question you asked. By the way, what is your question? Did your attempt fail?
Did you get an error message? Did you get the wrong results? Are you trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the numbers
(say number 8 comes up) in all of the fields? Here's a look at it. You
helped me with the UNION before. I'll list the UNION, then the Count query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


If the union all query is saved under the name QU, then



TRANSFORM COUNT(*)
SELECT 1
FROM qu
GROUP BY 1
PIVOT TheNumber


should do.


Hoping it may help,
Vanderghast, Access MVP


tamxwell said:
Yep, I did, but Michel had helped me on another query, so I figured.....
My
db has 6 fields all populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? No error message, just does
not return any results.

John Spencer (MVP) said:
It would really be better to start your own thread. It stands out as
being a
question you asked. By the way, what is your question? Did your attempt
fail?
Did you get an error message? Did you get the wrong results? Are you
trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? Here's a look at it. You
helped me with the UNION before. I'll list the UNION, then the Count
query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
Click to expand...
Click to expand...
Click to expand...
 
T

tamxwell

I don't know what I am doing wrong . It should work, but now it's stating
that Jet does not recognize pb[pb1] as valid name or expression

Michel Walsh said:
Hi,


If the union all query is saved under the name QU, then



TRANSFORM COUNT(*)
SELECT 1
FROM qu
GROUP BY 1
PIVOT TheNumber


should do.


Hoping it may help,
Vanderghast, Access MVP


tamxwell said:
Yep, I did, but Michel had helped me on another query, so I figured.....
My
db has 6 fields all populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? No error message, just does
not return any results.

John Spencer (MVP) said:
It would really be better to start your own thread. It stands out as
being a
question you asked. By the way, what is your question? Did your attempt
fail?
Did you get an error message? Did you get the wrong results? Are you
trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber


tamxwell wrote:

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? Here's a look at it. You
helped me with the UNION before. I'll list the UNION, then the Count
query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


You are missing a dot. Should be pb.pb1


The [ ] are not required, here, but you can use them around the field name
to get: pb.[pb1]
you can also use [pb].pb1 or [pb].[pb1]
but be sure to NOT use [pb.pb1] the dot within the [ ].


Hoping it may help,
Vanderghast, Access MVP


tamxwell said:
I don't know what I am doing wrong . It should work, but now it's stating
that Jet does not recognize pb[pb1] as valid name or expression

Michel Walsh said:
Hi,


If the union all query is saved under the name QU, then



TRANSFORM COUNT(*)
SELECT 1
FROM qu
GROUP BY 1
PIVOT TheNumber


should do.


Hoping it may help,
Vanderghast, Access MVP


tamxwell said:
Yep, I did, but Michel had helped me on another query, so I
figured.....
My
db has 6 fields all populated with numbers from 01-55 and there are
1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? No error message, just
does
not return any results.

:

It would really be better to start your own thread. It stands out as
being a
question you asked. By the way, what is your question? Did your
attempt
fail?
Did you get an error message? Did you get the wrong results? Are you
trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber


tamxwell wrote:

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields
all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of
the
numbers
(say number 8 comes up) in all of the fields? Here's a look at it.
You
helped me with the UNION before. I'll list the UNION, then the Count
query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
Click to expand...
Click to expand...
Click to expand...
 
T

tamxwell

Hey,
The dot is there, I just missed the key. Duane Hookum just hammered me for
something similar. Sorry! pb.[pb#1] is what it actually is. Still did not
work. I am going to change the field names, and start over.
Thanks Michel


Michel Walsh said:
Hi,


You are missing a dot. Should be pb.pb1


The [ ] are not required, here, but you can use them around the field name
to get: pb.[pb1]
you can also use [pb].pb1 or [pb].[pb1]
but be sure to NOT use [pb.pb1] the dot within the [ ].


Hoping it may help,
Vanderghast, Access MVP


tamxwell said:
I don't know what I am doing wrong . It should work, but now it's stating
that Jet does not recognize pb[pb1] as valid name or expression

Michel Walsh said:
Hi,


If the union all query is saved under the name QU, then



TRANSFORM COUNT(*)
SELECT 1
FROM qu
GROUP BY 1
PIVOT TheNumber


should do.


Hoping it may help,
Vanderghast, Access MVP


Yep, I did, but Michel had helped me on another query, so I
figured.....
My
db has 6 fields all populated with numbers from 01-55 and there are
1404
records. How can I write a query to tell me how many time each of the
numbers
(say number 8 comes up) in all of the fields? No error message, just
does
not return any results.

:

It would really be better to start your own thread. It stands out as
being a
question you asked. By the way, what is your question? Did your
attempt
fail?
Did you get an error message? Did you get the wrong results? Are you
trying to
restrict the query to just one number?

Your queries LOOK as if they should work.

SELECT pb.[pb1] as TheNumber
FROM PB
UNION ALL

SELECT pb.[pb2]
FROM PB
UNION ALL

SELECT pb.[pb3]
FROM PB
UNION ALL

SELECT pb.[pb4]
FROM PB
UNION ALL

SELECT pb.[pb5]
FROM PB
UNION ALL SELECT pb.[pb6]
FROM PB;

SELECT TheNumber, Count(TheNumber)
FROM quniNumbers
GROUP BY TheNumber


tamxwell wrote:

Hi Michel,
I have a query that I've tried a number of ways. My db has 6 fields
all
populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of
the
numbers
(say number 8 comes up) in all of the fields? Here's a look at it.
You
helped me with the UNION before. I'll list the UNION, then the Count
query

SELECT pb.[pb1] as TheNumber,1 As CountofmyNumber
FROM PB

UNION ALL

SELECT pb.[pb2],2
FROM PB

UNION ALL

SELECT pb.[pb3],3
FROM PB

UNION ALL

SELECT pb.[pb4],4
FROM PB

UNION ALL

SELECT pb.[pb5],5
FROM PB

UNION ALL SELECT pb.[pb6],6
FROM PB;

Then I wrote this to do the actual count, nada?

SELECT [TheNumber], Count([TheNumber]) AS CountOf
FROM quniNumbers
GROUP BY [TheNumber];
Click to expand...
Click to expand...
Click to expand...
 
Top