If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to find a range of data
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 a 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#2
|
|||
|
|||
Query to find a range of data
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 wrote: 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 a 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! -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Query to find a range of data
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 KenSheridan wrote: 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! -- Thanks, Kim B. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#4
|
|||
|
|||
Query to find a range of data
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 kimb wrote: 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] Please keep in mind that I am not an Access master. Any help would be GREATLY appreciated. Thanks in advance for your help! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#5
|
|||
|
|||
Query to find a range of data
Thanks I am going to give this is try. I'll let you know how I fair with it.
Kim KenSheridan wrote: 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] Please keep in mind that I am not an Access master. Any help would be GREATLY appreciated. Thanks in advance for your help! -- Thanks, Kim B. Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Query to find a range of data
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 KenSheridan wrote: 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! -- Thanks, Kim B. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#7
|
|||
|
|||
Query to find a range of data
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 kimb wrote: 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] Please keep in mind that I am not an Access master. Any help would be GREATLY appreciated. Thanks in advance for your help! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#8
|
|||
|
|||
Query to find a range of data
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. KenSheridan wrote: 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] Please keep in mind that I am not an Access master. Any help would be GREATLY appreciated. Thanks in advance for your help! -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Query to find a range of data
Ken,
I created the new query this morning and it is working perfectly! Thank you SOOOO much for your help! I greatly appreciate it. ) KenSheridan wrote: 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] Please keep in mind that I am not an Access master. Any help would be GREATLY appreciated. Thanks in advance for your help! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
Thread Tools | |
Display Modes | |
|
|