Fuzzy query - find almost matching

  • Thread starter Thread starter DavPet
  • Start date Start date
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?
 
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
 
That worked for my example but I have billions (hundreds) of examples.

any more help ?
 
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]
 
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];
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to pull records based of subquery column 10
pulling records from one table based of another table column 11
Replacing Query 5
Data Matching 1
iterate a query 3
Assign price category based on multiple record criteria 9
fuzzy matching help please in an Access 2003 query 2
Top 3 2

Back
Top