Query to find a range of data

[kimb via AccessMonster.com]

I am trying to figure out a way to have a query search between 2 separate
fields that are both alpha/numeric.

I am trying to match our Canadian offices with specific postal codes in
Canada. Since there are so many postal codes in Canada the table was set up
by ranges. The table is broken down by Province, City and Postal codes. The
Postal codes are listed completely and they are also broken down into the 1st
“3†and the 2nd “3†For example:
(The field names are: Country, Province, City, 1st High, 2nd Low, Whole High,
Whole Low, Office, BLDG)

CA AB CALGARY T2Y T2Y 0A0 9Z9 T2Y0A0 T2Y9Z9 7936 7439
CA AB CALGARY T2Z T2Z 0A0 9Z9 T2Z0A0 T2Z9Z9 7936 7439

This is just a small example(the table has approx 15,000 records), but
basically our Office 7936 falls under the Postal Code range T2Y0A0 AND T2Y9Z9.
The data we receive has a specific address. We need to match their Postal
Code to the Office and Bldg. So, if Jane Doe is at 555 Main street, Calgary
AB, T2Y4R3, we would need a query to recognize that that postal code falls
between T2Y0A0 & T2Y9Z9 and will therefore populate the Office and Bldg
fields with 7936 and 7439.

Please keep in mind that I am not an Access master. Any help would be
GREATLY appreciated. Thanks in advance for your help!

 

[KenSheridan via AccessMonster.com]

You'll need to create the query in SQL view as the join is not one which can
be created in design view. Assuming tables named Contacts and PostCodes

SELECT [Contacts].[FirstName], [Contacts].[LastName],
[Contacts].[Address], [Contacts].[City], [Contacts].[Province],
[Contacts].[PostCode], [PostCodes].[Office], [PostCodes].[Building]
FROM [Contacts] INNER JOIN [PostCodes]
ON ([Contacts].[PostCode] BETWEEN [PostCodes].[WholeLow]
AND [PostCodes].[WholeHigh]);

NB the parentheses in the JOIN clause are required.

Ken Sheridan
Stafford, England

 

[kimb via AccessMonster.com]

Thanks Ken for your response. So, this SQL will search between the 2 postal
code fields? It is not going to looking for an exact match?

Kim

You'll need to create the query in SQL view as the join is not one which can
be created in design view. Assuming tables named Contacts and PostCodes

SELECT [Contacts].[FirstName], [Contacts].[LastName],
[Contacts].[Address], [Contacts].[City], [Contacts].[Province],
[Contacts].[PostCode], [PostCodes].[Office], [PostCodes].[Building]
FROM [Contacts] INNER JOIN [PostCodes]
ON ([Contacts].[PostCode] BETWEEN [PostCodes].[WholeLow]
AND [PostCodes].[WholeHigh]);

NB the parentheses in the JOIN clause are required.

Ken Sheridan
Stafford, England

I am trying to figure out a way to have a query search between 2 separate
fields that are both alpha/numeric.

[quoted text clipped - 20 lines]

Please keep in mind that I am not an Access master. Any help would be
GREATLY appreciated. Thanks in advance for your help!

 

[KenSheridan via AccessMonster.com]

Kim:

That's right. Because each range of post code values sorts from the lower
value to the higher value, e.g. from T2Y0A0 to T2Y9Z9 any value which falls
anywhere in this range e.g. T2Y4P2 will be picked up by the BETWEEN….AND
operation. This can be done as I did in my earlier message providing that
the join expression is enclosed in parentheses, or it can be done by using
the >= and <= operators like so:

SELECT [Contacts].[FirstName], [Contacts].[LastName],
[Contacts].[Address], [Contacts].[City], [Contacts].[Province],
[Contacts].[PostCode], [PostCodes].[Office], [PostCodes].[Building]
FROM [Contacts] INNER JOIN [PostCodes]
ON [Contacts].[PostCode] >= [PostCodes].[WholeLow]
AND [Contacts].[PostCode] <= [PostCodes].[WholeHigh];

Whichever way its done the results will be the same. Because the joint types
used here can't be expressed in design view, however, it has to be done in
SQL view. But you could design it first in design view joining the Contacts
table to the PostCodes table on [Contacts].[PostCode] = [PostCodes].[WholeLow]
by dragging from one to the other in the usual way. If you then switch to
SQL view the join will be:

ON [Contacts].[PostCode] = [PostCodes].[WholeLow]

This would look for exact matches, so you just have to edit it to:

ON ([Contacts].[PostCode] BETWEEN [PostCodes].[WholeLow]
AND [PostCodes].[WholeHigh])

or to:

ON [Contacts].[PostCode] >= [PostCodes].[WholeLow]
AND [Contacts].[PostCode] <= [PostCodes].[WholeHigh]

Ken Sheridan
Stafford, England

Thanks Ken for your response. So, this SQL will search between the 2 postal
code fields? It is not going to looking for an exact match?

Kim

You'll need to create the query in SQL view as the join is not one which can
be created in design view. Assuming tables named Contacts and PostCodes

[quoted text clipped - 16 lines]

 

[kimb via AccessMonster.com]

Thanks I am going to give this is try. I'll let you know how I fair with it.

Kim

Kim:

That's right. Because each range of post code values sorts from the lower
value to the higher value, e.g. from T2Y0A0 to T2Y9Z9 any value which falls
anywhere in this range e.g. T2Y4P2 will be picked up by the BETWEEN….AND
operation. This can be done as I did in my earlier message providing that
the join expression is enclosed in parentheses, or it can be done by using
the >= and <= operators like so:

SELECT [Contacts].[FirstName], [Contacts].[LastName],
[Contacts].[Address], [Contacts].[City], [Contacts].[Province],
[Contacts].[PostCode], [PostCodes].[Office], [PostCodes].[Building]
FROM [Contacts] INNER JOIN [PostCodes]
ON [Contacts].[PostCode] >= [PostCodes].[WholeLow]
AND [Contacts].[PostCode] <= [PostCodes].[WholeHigh];

Whichever way its done the results will be the same. Because the joint types
used here can't be expressed in design view, however, it has to be done in
SQL view. But you could design it first in design view joining the Contacts
table to the PostCodes table on [Contacts].[PostCode] = [PostCodes].[WholeLow]
by dragging from one to the other in the usual way. If you then switch to
SQL view the join will be:

ON [Contacts].[PostCode] = [PostCodes].[WholeLow]

This would look for exact matches, so you just have to edit it to:

ON ([Contacts].[PostCode] BETWEEN [PostCodes].[WholeLow]
AND [PostCodes].[WholeHigh])

or to:

ON [Contacts].[PostCode] >= [PostCodes].[WholeLow]
AND [Contacts].[PostCode] <= [PostCodes].[WholeHigh]

Ken Sheridan
Stafford, England

Thanks Ken for your response. So, this SQL will search between the 2 postal
code fields? It is not going to looking for an exact match?

[quoted text clipped - 6 lines]

 

[kimb via AccessMonster.com]

Ken I think I've got this to work for me. But, can you tell me how to make
it an Update Query? I would like when I run the query for the Office and
Building fields to automatcially populate by using an Update query.

Thanks so much! This is fantastic. I have been working on this problem for
over a month!
Kim

You'll need to create the query in SQL view as the join is not one which can
be created in design view. Assuming tables named Contacts and PostCodes

SELECT [Contacts].[FirstName], [Contacts].[LastName],
[Contacts].[Address], [Contacts].[City], [Contacts].[Province],
[Contacts].[PostCode], [PostCodes].[Office], [PostCodes].[Building]
FROM [Contacts] INNER JOIN [PostCodes]
ON ([Contacts].[PostCode] BETWEEN [PostCodes].[WholeLow]
AND [PostCodes].[WholeHigh]);

NB the parentheses in the JOIN clause are required.

Ken Sheridan
Stafford, England

I am trying to figure out a way to have a query search between 2 separate
fields that are both alpha/numeric.

[quoted text clipped - 20 lines]

Please keep in mind that I am not an Access master. Any help would be
GREATLY appreciated. Thanks in advance for your help!

 

[KenSheridan via AccessMonster.com]

Kim:

The query won't translate directly into an update query because of the nature
of the join which renders the query non-updatable, but it should be possible
to do it by means of the DLookup function. Before showing you how to do it,
though, do you really need to update the columns in the Contacts table? As
the query gives you the Office and Building values you can just use the query
whenever you want to return those value for a 'contact'. As the query is not
updatable, however, you couldn't use it as the RecordSource for a data entry
form, but you could have a form based on the Contacts table and have two
unbound text box controls to show the Office and Building values. For the
Office text box the ControlSource would be:

=DLookup("[Office]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

and for Building:

=DLookup("[Building]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

More to the point is that by updating Office and Building columns in Contacts
you'd be introducing redundancy into the table. We'd be told multiple times
what the Office and Building are for every instance of a postcode within each
range. When a table is not normalized by virtue of containing potential
redundancies this is not merely a question of inefficient data storage (In
fact performance will be better with the redundant columns), but more
importantly it leaves the table open to inconsistent data in that there is
nothing to stop the Building and Office values being independently changed so
that they are inconsistent with each other, and/or are inconsistent with the
post code in that row. Returning them from the PostCodes table in a query
eliminates the possible redundancy.

However, there are many databases in day to day use which include tables
which are not correctly normalized, so its for you to decide whether you want
to accept this degree of non-normalization (and the consequent risk to the
integrity of the data) or not. If you decide that you do then a query to
update the Office and Building columns in Contacts would be like this:

UPDATE Contacts
SET Office =
DLookup("[Office]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]"),
Building =
DLookup("[Building]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]");

If you are wondering why there are so many quotes characters in the above its
because to represent a literal quotes character within a string a pair of
contiguous quotes characters is used. In the above expressions because
PostCode is a text data type its values have to be wrapped in quotes, so:

"""" & [PostCode] & """ Between [Whole Low] And [Whole High]"

will evaluate to something like:

"T2Y4R3" Between [Whole Low] And [Whole High]

Ken Sheridan
Stafford, England

Ken I think I've got this to work for me. But, can you tell me how to make
it an Update Query? I would like when I run the query for the Office and
Building fields to automatcially populate by using an Update query.

Thanks so much! This is fantastic. I have been working on this problem for
over a month!
Kim

You'll need to create the query in SQL view as the join is not one which can
be created in design view. Assuming tables named Contacts and PostCodes

[quoted text clipped - 16 lines]

 

[kimb via AccessMonster.com]

Thanks Ken! To answer your question of if I do need to update the columns in
the Contact table?..... As far as I know (meaning with my base of knowledge
which is not advanced) I believe that I do need to update these columns. This
query is actually a "step" among several steps to a report that I am
generating on a daily basis. Each record must have the Office and Building
numbers when I export the information to Excel. Once in Excel I am creating a
Pivot Table based on the Building and Office numbers. The records need to be
grouped in this way so that the individuals who are receiving this report can
look at their Building and Office quicky and see if there are any problems
that need to be fixed.

Thanks for all your help! I will give the new suggestion a try tomorrow when
I come back in to work tomorrow.

Kim:

The query won't translate directly into an update query because of the nature
of the join which renders the query non-updatable, but it should be possible
to do it by means of the DLookup function. Before showing you how to do it,
though, do you really need to update the columns in the Contacts table? As
the query gives you the Office and Building values you can just use the query
whenever you want to return those value for a 'contact'. As the query is not
updatable, however, you couldn't use it as the RecordSource for a data entry
form, but you could have a form based on the Contacts table and have two
unbound text box controls to show the Office and Building values. For the
Office text box the ControlSource would be:

=DLookup("[Office]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

and for Building:

=DLookup("[Building]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

More to the point is that by updating Office and Building columns in Contacts
you'd be introducing redundancy into the table. We'd be told multiple times
what the Office and Building are for every instance of a postcode within each
range. When a table is not normalized by virtue of containing potential
redundancies this is not merely a question of inefficient data storage (In
fact performance will be better with the redundant columns), but more
importantly it leaves the table open to inconsistent data in that there is
nothing to stop the Building and Office values being independently changed so
that they are inconsistent with each other, and/or are inconsistent with the
post code in that row. Returning them from the PostCodes table in a query
eliminates the possible redundancy.

However, there are many databases in day to day use which include tables
which are not correctly normalized, so its for you to decide whether you want
to accept this degree of non-normalization (and the consequent risk to the
integrity of the data) or not. If you decide that you do then a query to
update the Office and Building columns in Contacts would be like this:

UPDATE Contacts
SET Office =
DLookup("[Office]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]"),
Building =
DLookup("[Building]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]");

If you are wondering why there are so many quotes characters in the above its
because to represent a literal quotes character within a string a pair of
contiguous quotes characters is used. In the above expressions because
PostCode is a text data type its values have to be wrapped in quotes, so:

"""" & [PostCode] & """ Between [Whole Low] And [Whole High]"

will evaluate to something like:

"T2Y4R3" Between [Whole Low] And [Whole High]

Ken Sheridan
Stafford, England

Ken I think I've got this to work for me. But, can you tell me how to make
it an Update Query? I would like when I run the query for the Office and

[quoted text clipped - 9 lines]

 

[kimb via AccessMonster.com]

Ken,
I created the new query this morning and it is working perfectly! Thank you
SOOOO much for your help! I greatly appreciate it. )

Kim:

The query won't translate directly into an update query because of the nature
of the join which renders the query non-updatable, but it should be possible
to do it by means of the DLookup function. Before showing you how to do it,
though, do you really need to update the columns in the Contacts table? As
the query gives you the Office and Building values you can just use the query
whenever you want to return those value for a 'contact'. As the query is not
updatable, however, you couldn't use it as the RecordSource for a data entry
form, but you could have a form based on the Contacts table and have two
unbound text box controls to show the Office and Building values. For the
Office text box the ControlSource would be:

=DLookup("[Office]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

and for Building:

=DLookup("[Building]", "[PostCodes]", """" & [PostCode] & """ Between [Whole
Low] And [Whole High]")

More to the point is that by updating Office and Building columns in Contacts
you'd be introducing redundancy into the table. We'd be told multiple times
what the Office and Building are for every instance of a postcode within each
range. When a table is not normalized by virtue of containing potential
redundancies this is not merely a question of inefficient data storage (In
fact performance will be better with the redundant columns), but more
importantly it leaves the table open to inconsistent data in that there is
nothing to stop the Building and Office values being independently changed so
that they are inconsistent with each other, and/or are inconsistent with the
post code in that row. Returning them from the PostCodes table in a query
eliminates the possible redundancy.

However, there are many databases in day to day use which include tables
which are not correctly normalized, so its for you to decide whether you want
to accept this degree of non-normalization (and the consequent risk to the
integrity of the data) or not. If you decide that you do then a query to
update the Office and Building columns in Contacts would be like this:

UPDATE Contacts
SET Office =
DLookup("[Office]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]"),
Building =
DLookup("[Building]", "[PostCodes]", """" & [PostCode] &
""" Between [Whole Low] And [Whole High]");

If you are wondering why there are so many quotes characters in the above its
because to represent a literal quotes character within a string a pair of
contiguous quotes characters is used. In the above expressions because
PostCode is a text data type its values have to be wrapped in quotes, so:

"""" & [PostCode] & """ Between [Whole Low] And [Whole High]"

will evaluate to something like:

"T2Y4R3" Between [Whole Low] And [Whole High]

Ken Sheridan
Stafford, England

Ken I think I've got this to work for me. But, can you tell me how to make
it an Update Query? I would like when I run the query for the Office and

[quoted text clipped - 9 lines]