Most Characters Matched query

  • Thread starter Thread starter W Dean
  • Start date Start date
W

W Dean

Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2, M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
 
Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,

Thanks for the reply, your query worked but now I'm having problems with a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes have a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Tom Ellison said:
Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2, M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
 
Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems with a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes have a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Tom Ellison said:
Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2, M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Hi Tom,

Thanks again. The query you gave returns the right results. The one with the
Max statement seemed to return all records in the user table whether there
was a match or not.in the regions table. This is the data I had in them:

Users Table
UserName, PostCode

another,
blahblah, M15 4GB
google,
john,
newuser,
sarah,
test, zzz
wodge, M19 3LW

Regions Table

PostCode, Supplier
M1, another
M15, test
M19, another
M20, test

Query Results
UserName, PostCode
another,
blahblah, M15 4GB
google,
john,
sarah,
test, zzz
wodge,M19 3LW
newuser

One last question, I also have another table with addresses belonging to
each user given a postcode from this table which matches a region how do I
find all the other addresses in this table which are managed by the same
supplier of that region.

Once again thanks for all your help.

wodge


Tom Ellison said:
Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems with a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes have a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Tom Ellison said:
Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2, M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Dear Wodge:

I almost answered this in the first reply, till I suddenly realized
you were using a parameter to enter the desired PostCode.

I was saying to make a cross-product:

SELECT T.PostCode SourcePostCode, R.PostCode RegionalPostCode
FROM Regions R, AnotherTable T
WHERE T.PostCode LIKE R.PostCode & "*"
AND LEN(R.PostCode) = (SELECT MAX(LEN(R1.PostCode)
FROM Regions R1 WHERE T.PostCode LIKE R1.PostCode & "*")

I think I've got this right now. It can be difficult when you cannot
test your code, ya know.

Substitute the name of your other table in the above, and correct the
reference to its PostCode column if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

Thanks again. The query you gave returns the right results. The one with the
Max statement seemed to return all records in the user table whether there
was a match or not.in the regions table. This is the data I had in them:

Users Table
UserName, PostCode

another,
blahblah, M15 4GB
google,
john,
newuser,
sarah,
test, zzz
wodge, M19 3LW

Regions Table

PostCode, Supplier
M1, another
M15, test
M19, another
M20, test

Query Results
UserName, PostCode
another,
blahblah, M15 4GB
google,
john,
sarah,
test, zzz
wodge,M19 3LW
newuser

One last question, I also have another table with addresses belonging to
each user given a postcode from this table which matches a region how do I
find all the other addresses in this table which are managed by the same
supplier of that region.

Once again thanks for all your help.

wodge


Tom Ellison said:
Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems with a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes have a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2,
M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Thanks for all your help. This is the final query based on your code.

SELECT 0 AS ID, 'Default' AS Description, T.Company, T.Address1, T.Address2,
T.Town, T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, Users AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL))
UNION SELECT T.ID, T.Description, T.Company, T.Address1, T.Address2, T.Town,
T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, UserAddresses AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL));


wodge

Tom Ellison said:
Dear Wodge:

I almost answered this in the first reply, till I suddenly realized
you were using a parameter to enter the desired PostCode.

I was saying to make a cross-product:

SELECT T.PostCode SourcePostCode, R.PostCode RegionalPostCode
FROM Regions R, AnotherTable T
WHERE T.PostCode LIKE R.PostCode & "*"
AND LEN(R.PostCode) = (SELECT MAX(LEN(R1.PostCode)
FROM Regions R1 WHERE T.PostCode LIKE R1.PostCode & "*")

I think I've got this right now. It can be difficult when you cannot
test your code, ya know.

Substitute the name of your other table in the above, and correct the
reference to its PostCode column if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

Thanks again. The query you gave returns the right results. The one with the
Max statement seemed to return all records in the user table whether there
was a match or not.in the regions table. This is the data I had in them:

Users Table
UserName, PostCode

another,
blahblah, M15 4GB
google,
john,
newuser,
sarah,
test, zzz
wodge, M19 3LW

Regions Table

PostCode, Supplier
M1, another
M15, test
M19, another
M20, test

Query Results
UserName, PostCode
another,
blahblah, M15 4GB
google,
john,
sarah,
test, zzz
wodge,M19 3LW
newuser

One last question, I also have another table with addresses belonging to
each user given a postcode from this table which matches a region how do I
find all the other addresses in this table which are managed by the same
supplier of that region.

Once again thanks for all your help.

wodge


Tom Ellison said:
Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems
Click to expand...
with
a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes
Click to expand...
have
a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2,
M23
on which I'm want to run a wildcard search so that it returns the record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Dear Wodge:

And now, a survey question, if you don't mind.

Did you learn HOW to do the things I told you about, or did you just
get this one problem solved without learning much about how to solve
similar problems when they arise?

I didn't give a lot of explanation. Here are some topics you could
study to explain some of what I supplied:

- aliasing
- subqueries

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for all your help. This is the final query based on your code.

SELECT 0 AS ID, 'Default' AS Description, T.Company, T.Address1, T.Address2,
T.Town, T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, Users AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL))
UNION SELECT T.ID, T.Description, T.Company, T.Address1, T.Address2, T.Town,
T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, UserAddresses AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL));


wodge

Tom Ellison said:
Dear Wodge:

I almost answered this in the first reply, till I suddenly realized
you were using a parameter to enter the desired PostCode.

I was saying to make a cross-product:

SELECT T.PostCode SourcePostCode, R.PostCode RegionalPostCode
FROM Regions R, AnotherTable T
WHERE T.PostCode LIKE R.PostCode & "*"
AND LEN(R.PostCode) = (SELECT MAX(LEN(R1.PostCode)
FROM Regions R1 WHERE T.PostCode LIKE R1.PostCode & "*")

I think I've got this right now. It can be difficult when you cannot
test your code, ya know.

Substitute the name of your other table in the above, and correct the
reference to its PostCode column if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

Thanks again. The query you gave returns the right results. The one with the
Max statement seemed to return all records in the user table whether there
was a match or not.in the regions table. This is the data I had in them:

Users Table
UserName, PostCode

another,
blahblah, M15 4GB
google,
john,
newuser,
sarah,
test, zzz
wodge, M19 3LW

Regions Table

PostCode, Supplier
M1, another
M15, test
M19, another
M20, test

Query Results
UserName, PostCode
another,
blahblah, M15 4GB
google,
john,
sarah,
test, zzz
wodge,M19 3LW
newuser

One last question, I also have another table with addresses belonging to
each user given a postcode from this table which matches a region how do I
find all the other addresses in this table which are managed by the same
supplier of that region.

Once again thanks for all your help.

wodge


Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems with
a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a
UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses Postcodes have
a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode
LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a table with Partial Postcodes as the key eg. M1, M15, M19, M2,
M23
on which I'm want to run a wildcard search so that it returns the
record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the
record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I think the example you gave was a good example of how to use sub queries
which I'd not really used before. I managed to create the following query
based on it:

SELECT A.ID, A.Description, A.Company, A.Address1, A.Address2, A.Town,
A.PostCode, R.Supplier, R.PostCode AS PPostCode
FROM Regions AS R, sp_SelectUserAddressCollection AS A
WHERE (A.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
A.PostCode LIKE R1.PostCode & "%")
AND ((A.UserName = [aUserName]) OR ([aUserName] IS NULL))
AND ((R.Supplier=[aSupplier]) OR ([aSupplier] IS NULL))
AND EXISTS(SELECT * FROM UserServices AS U WHERE U.UserName =
R.Supplier);

which checks all the addresses a user has which are serviced by the same
supplier and were the supplier is providing services.

Wodge


Tom Ellison said:
Dear Wodge:

And now, a survey question, if you don't mind.

Did you learn HOW to do the things I told you about, or did you just
get this one problem solved without learning much about how to solve
similar problems when they arise?

I didn't give a lot of explanation. Here are some topics you could
study to explain some of what I supplied:

- aliasing
- subqueries

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for all your help. This is the final query based on your code.

SELECT 0 AS ID, 'Default' AS Description, T.Company, T.Address1, T.Address2,
T.Town, T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, Users AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL))
UNION SELECT T.ID, T.Description, T.Company, T.Address1, T.Address2, T.Town,
T.PostCode, R.PostCode AS PPostCode
FROM Regions AS R, UserAddresses AS T
WHERE (T.PostCode LIKE R.PostCode & "%")
AND R.PostCode = (SELECT MAX(R1.PostCode) FROM Regions AS R1 WHERE
T.PostCode LIKE R1.PostCode & "%")
AND ((T.UserName=[aUserName]) OR ([aUserName] IS NULL));


wodge

Tom Ellison said:
Dear Wodge:

I almost answered this in the first reply, till I suddenly realized
you were using a parameter to enter the desired PostCode.

I was saying to make a cross-product:

SELECT T.PostCode SourcePostCode, R.PostCode RegionalPostCode
FROM Regions R, AnotherTable T
WHERE T.PostCode LIKE R.PostCode & "*"
AND LEN(R.PostCode) = (SELECT MAX(LEN(R1.PostCode)
FROM Regions R1 WHERE T.PostCode LIKE R1.PostCode & "*")

I think I've got this right now. It can be difficult when you cannot
test your code, ya know.

Substitute the name of your other table in the above, and correct the
reference to its PostCode column if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

Thanks again. The query you gave returns the right results. The one
Click to expand...
with
the
Max statement seemed to return all records in the user table whether there
was a match or not.in the regions table. This is the data I had in them:

Users Table
UserName, PostCode

another,
blahblah, M15 4GB
google,
john,
newuser,
sarah,
test, zzz
wodge, M19 3LW

Regions Table

PostCode, Supplier
M1, another
M15, test
M19, another
M20, test

Query Results
UserName, PostCode
another,
blahblah, M15 4GB
google,
john,
sarah,
test, zzz
wodge,M19 3LW
newuser

One last question, I also have another table with addresses belonging to
each user given a postcode from this table which matches a region how
Click to expand...
do
I
find all the other addresses in this table which are managed by the same
supplier of that region.

Once again thanks for all your help.

wodge


Dear Wodge:

One thing I'd note is that, when you do WHERE EXISTS all that matters
is whether there is one or more rows in the subquery that follows.
Asking for MAX in this case is meaningless. If there are no rows,
there will be no MAX of those rows. If there are rows, then there
will be a MAX. So, asking it to find the MAX is a potential waste of
time. When performing EXISTS I make it a policy to just use "SELECT
*"

Now you would have:

SELECT UserName, PostCode
FROM Users
WHERE EXISTS(SELECT * FROM Regions
WHERE Users.PostCode LIKE Regions.PostCode + "*")

Does this return strange results? And what is strange about it? I
don't readily see anything wrong, so I'm going to need more
information if there is a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

Thanks for the reply, your query worked but now I'm having problems with
a
slighty more complicated one.

I have two tables:

A Managed Areas table which contains a Partial Postcode field and a
UserID

and

A User table which contain a UserID and a full Postcode.

What I want the query to do is return all the Users whoses
Click to expand...
Postcodes
have
a
'best match' entry in the Managed Areas Table

I tried

SELECT
Users.UserName,
Users.PostCode
FROM
Users
WHERE
EXISTS(SELECT MAX(Regions.PostCode) FROM Regions WHERE Users.Postcode
LIKE
Regions.PostCode + "*")

but it seems to be return strange results.

Wodge

Dear Wodge:

I think your concept of what a wildcard match is supposed to do is
flawed. You might want to review the help on this to get a better
idea.

So, your sample showing M193L2 should find the M19 but not M1, right?
You want to longest match only.

I think we're going to have to count the lengths of matches and
maximize that to accomplish this.

Beginning with a cross-product of the two tables, we count the length
of every Partial Postcode that matches, and use the longest. (I was
wrong about there being two tables as it appears you are just having
the user key in a singe [aPostCode] value. If you had a whole table
of them to do, then the cross-product idea would be good.) Assuming
the Partial Postcodes are all unique, this should work well, giving
only one "best match" or no matches at all.

SELECT PostCode
FROM Regions
WHERE [aPostCode] LIKE PostCode & "*"
AND LEN(PostCode) = (SELECT MAX(LEN(PostCode)
FROM Regions WHERE [aPostCode] LIKE PostCode & "*")

Any luck with this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
wrote:

Hi,

I have a table with Partial Postcodes as the key eg. M1, M15,
Click to expand...
M19,
M2,
M23
on which I'm want to run a wildcard search so that it returns the
record
with the most characters matched. So If I used the search criteria
[aPostCode] Like Table.PartialCode + '*' it should return the longest
matching value. So a search using 'M19 3LW' would return only the
record
with M19 as the key.
Any Help on how to do this would be appreciated.
I've tried using the following

SELECT Regions.Postcode, Max(Len(Regions.PostCode)) FROM Regions WHERE
[aPostCode] Like Regions.PostCode + '*'
GROUP BY Regions.PostCode, Len(Regions.PostCode);

in Access 2003 but it returns more than one record.

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Back
Top