MS Access Query - Replace String1 with String2 Where Value of String2 is Based on Value of String1
up vote
0
down vote
favorite
We have a dozen tables in our MS Access DB. The two tables I'm looking at are e_pages and e_panel_list.
e_pages has a text field called page_xml that can contain any number of characters, including URL strings, such as "/site/documents/?l=32&p=224"
e_pages has another field called pageID and another page_name with values such as pageID=224 and page_name="Weekly Schedule", where 224 is the pageID assigned to the record with the page_name of "Weekly Schedule".
e_panel_list has fields panel_listID and list_name, where, for example, one record has panel_listID=32 and list_name="October Schedule".
The URL in e_pages.page_xml is created using the values for panel_listID and pageID. I need to replace those ID numbers with the values from the names fields. So, "/site/documents/?l=32&p=224" should be replaced with "/site/documents/october-schecdule/weekly-schedule".
I can do it using the replace() function in Access, but only if I manually enter the values for each case (ie /?l=32& becomes /october-schedule/ and p=224 becomes weekly-schedule), but there are about 40,000 pageID values.
Is there a way to use the field names (pageID, page_name, panel_listID, list_name) and the ?l=* and &p=* (where * can equal an int any number of digits) to replace the values correctly anywhere and every time this appears in the page_xml field?
I am using the Access Query Design. (I can do this in Excel, but the page_xml contents is too large for an Excel cell.) Thanks!
database string ms-access replace query-designer
asked Nov 8 at 22:11
calwex718
427
add a comment |
up vote
0
down vote
favorite
We have a dozen tables in our MS Access DB. The two tables I'm looking at are e_pages and e_panel_list.
e_pages has a text field called page_xml that can contain any number of characters, including URL strings, such as "/site/documents/?l=32&p=224"
e_pages has another field called pageID and another page_name with values such as pageID=224 and page_name="Weekly Schedule", where 224 is the pageID assigned to the record with the page_name of "Weekly Schedule".
e_panel_list has fields panel_listID and list_name, where, for example, one record has panel_listID=32 and list_name="October Schedule".
The URL in e_pages.page_xml is created using the values for panel_listID and pageID. I need to replace those ID numbers with the values from the names fields. So, "/site/documents/?l=32&p=224" should be replaced with "/site/documents/october-schecdule/weekly-schedule".
I can do it using the replace() function in Access, but only if I manually enter the values for each case (ie /?l=32& becomes /october-schedule/ and p=224 becomes weekly-schedule), but there are about 40,000 pageID values.
Is there a way to use the field names (pageID, page_name, panel_listID, list_name) and the ?l=* and &p=* (where * can equal an int any number of digits) to replace the values correctly anywhere and every time this appears in the page_xml field?
I am using the Access Query Design. (I can do this in Excel, but the page_xml contents is too large for an Excel cell.) Thanks!
database string ms-access replace query-designer
asked Nov 8 at 22:11
calwex718
427
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
We have a dozen tables in our MS Access DB. The two tables I'm looking at are e_pages and e_panel_list.
e_pages has a text field called page_xml that can contain any number of characters, including URL strings, such as "/site/documents/?l=32&p=224"
e_pages has another field called pageID and another page_name with values such as pageID=224 and page_name="Weekly Schedule", where 224 is the pageID assigned to the record with the page_name of "Weekly Schedule".
e_panel_list has fields panel_listID and list_name, where, for example, one record has panel_listID=32 and list_name="October Schedule".
The URL in e_pages.page_xml is created using the values for panel_listID and pageID. I need to replace those ID numbers with the values from the names fields. So, "/site/documents/?l=32&p=224" should be replaced with "/site/documents/october-schecdule/weekly-schedule".
I can do it using the replace() function in Access, but only if I manually enter the values for each case (ie /?l=32& becomes /october-schedule/ and p=224 becomes weekly-schedule), but there are about 40,000 pageID values.
Is there a way to use the field names (pageID, page_name, panel_listID, list_name) and the ?l=* and &p=* (where * can equal an int any number of digits) to replace the values correctly anywhere and every time this appears in the page_xml field?
I am using the Access Query Design. (I can do this in Excel, but the page_xml contents is too large for an Excel cell.) Thanks!
database string ms-access replace query-designer
asked Nov 8 at 22:11
calwex718
427
We have a dozen tables in our MS Access DB. The two tables I'm looking at are e_pages and e_panel_list.
e_pages has a text field called page_xml that can contain any number of characters, including URL strings, such as "/site/documents/?l=32&p=224"
e_pages has another field called pageID and another page_name with values such as pageID=224 and page_name="Weekly Schedule", where 224 is the pageID assigned to the record with the page_name of "Weekly Schedule".
e_panel_list has fields panel_listID and list_name, where, for example, one record has panel_listID=32 and list_name="October Schedule".
The URL in e_pages.page_xml is created using the values for panel_listID and pageID. I need to replace those ID numbers with the values from the names fields. So, "/site/documents/?l=32&p=224" should be replaced with "/site/documents/october-schecdule/weekly-schedule".
I can do it using the replace() function in Access, but only if I manually enter the values for each case (ie /?l=32& becomes /october-schedule/ and p=224 becomes weekly-schedule), but there are about 40,000 pageID values.
Is there a way to use the field names (pageID, page_name, panel_listID, list_name) and the ?l=* and &p=* (where * can equal an int any number of digits) to replace the values correctly anywhere and every time this appears in the page_xml field?
I am using the Access Query Design. (I can do this in Excel, but the page_xml contents is too large for an Excel cell.) Thanks!
database string ms-access replace query-designer
database string ms-access replace query-designer
asked Nov 8 at 22:11
calwex718
427
asked Nov 8 at 22:11
calwex718
427
asked Nov 8 at 22:11
calwex718
427
asked Nov 8 at 22:11
calwex718
427
asked Nov 8 at 22:11
calwex718
427
427
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00
add a comment |
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53216927%2fms-access-query-replace-string1-with-string2-where-value-of-string2-is-based-o%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please list some rows of sample data and expected result in tabular format, using ozh.github.io/ascii-tables
– Andre
Nov 9 at 7:00