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
|
|||
|
|||
Using MakeTable and UpDate Queries
1. qryML1 makes a tblMLTemp to concatenate certain fields.
2. qryML2 makes a tblMLTemp2 to concatenate certain fields using different criteria for the fields. 3. qryMLUpdate updates the certain fields in tblMLTemp based on the values of the fields in MLTemp2. These all work as expected manually and give the correct results. Now, how do I automate this proc so that first you run qryML1 to get tblMLTemp records Correct, then run qryML2 to obtain the other changes in the table, then run the qryUpdate to modify the records in tblTemp with the records in tblTemp2. Thanks in advance! Sandra G |
#2
|
|||
|
|||
I may be missing something here, but I think there is a
very simple solution: you can create a macro to run each query in order: * Open a new macro in macro design view * Select 'OpenQuery' from the 'Action' column * Select the 1st query name from the 'Query Name' Argument (bottom of screen) * repeat (in order) for the other two queries I believe this will work If you don't want to be asked before running each query, use the 'SetWarnings' action to turn them off before the queries run - but DONT forget to turn them back on afterwards (in the last line) or you'll accidentally save design changes and all sorts of problems Kind Regards, Wes -----Original Message----- 1. qryML1 makes a tblMLTemp to concatenate certain fields. 2. qryML2 makes a tblMLTemp2 to concatenate certain fields using different criteria for the fields. 3. qryMLUpdate updates the certain fields in tblMLTemp based on the values of the fields in MLTemp2. These all work as expected manually and give the correct results. Now, how do I automate this proc so that first you run qryML1 to get tblMLTemp records Correct, then run qryML2 to obtain the other changes in the table, then run the qryUpdate to modify the records in tblTemp with the records in tblTemp2. Thanks in advance! Sandra G . |
#3
|
|||
|
|||
All this rigamarole is to construct a table to be used in
a combobox to select a donor record, and also for fields in mailings. The master donor table has a linked relations table i.e. individuals related to the master donor record. In some cases the relations are Household, in some cases the relations are ContactP. If Household, I run a query to concatenate the first names of the individuals thus John and Mary Doe (Doe being the NameID in the master record). If Contact then I want to combine the first and last name thus Harry Smith (who is a contact at ABC Bank, the NameID). The temp tables both have a common field of CombNames to contain the concatenated data from both queries. So eventually the data can be displayed as: John and Mary Doe 123 High Street -or- ABC Bank Attn: Harry Smith 123 High Street So far, so good. But now I need a way to automate this whole process for use in the combo box. In other words, when the form is loaded this procedure should run so that the temp table is current and available for selection. Will a macro work for this? How will it handle the usual prompts about making a table, updating a table, etc. Presumably, this should be seamless and invisible to the user. I will also, at some point, need to filter the results so that the combobox only shows a list of Smiths rather than a huge list of names. Thanks for your help, Sandra G -----Original Message---- I may be missing something here, but I think there is a very simple solution: you can create a macro to run each query in order: * Open a new macro in macro design view * Select 'OpenQuery' from the 'Action' column * Select the 1st query name from the 'Query Name' Argument (bottom of screen) * repeat (in order) for the other two queries I believe this will work If you don't want to be asked before running each query, use the 'SetWarnings' action to turn them off before the queries run - but DONT forget to turn them back on afterwards (in the last line) or you'll accidentally save design changes and all sorts of problems Kind Regards, Wes -----Original Message----- 1. qryML1 makes a tblMLTemp to concatenate certain fields. 2. qryML2 makes a tblMLTemp2 to concatenate certain fields using different criteria for the fields. 3. qryMLUpdate updates the certain fields in tblMLTemp based on the values of the fields in MLTemp2. These all work as expected manually and give the correct results. Now, how do I automate this proc so that first you run qryML1 to get tblMLTemp records Correct, then run qryML2 to obtain the other changes in the table, then run the qryUpdate to modify the records in tblTemp with the records in tblTemp2. Thanks in advance! Sandra G . . |
#4
|
|||
|
|||
On Tue, 21 Sep 2004 12:19:24 -0700, "Sandra Grawunder"
wrote: All this rigamarole is to construct a table to be used in a combobox to select a donor record Ummm... why construct the table? Lots of hassle and overhead! Just base the Combo on the Select query rather than changing it into a MakeTable query. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#5
|
|||
|
|||
OK, now it gets more technical (not too bad if you know
about forms design though) Here we go: 1. to make macro run when the form opens: * Open the form in design view * Press ALT+Enter to view the properties window (the properties dialog should say 'Form' in the title bar, if not select it by clicking the little square box at the top left of the form design window) * In the properties dialog, choose the 'Events' Tab and find the 'On Open' entry * Now simply select the macro you created yesterday from the dropdown list The macro will now run EVERYTIME the form is opened. 2. to prevent the prompts from appearing when the macro runs * Open the macro in design view * select the first line in the macro and choose 'rows' from the 'insert' menu * select 'SetWarnings' from the Action column in the new row * Choose 'False' in the Arguments section * Now go to the last (blank) line in the macro * select 'SetWarnings' again and enter 'True' in the Arguments section This turns off all confirmation prompts temporarily, accepting 'Yes' or 'OK' as the user response, and then re- activates them after the macro has run. Note: this also turns off the prompts for saving design changes in forms and queries so BE CAREFUL. It is important to turn the warnings back on to save yourself the embarrasment of accidentaly saving design changes after you have been fiddling around. 3. To Filter the list of values * I can't really help on this one just yet because I need to know how you plan to get the criteria for the filter (eg Input box from user, another combobox or textbox, values from a table, etc) See how you go on 1. & 2. first Regards, Wes Baker -----Original Message----- All this rigamarole is to construct a table to be used in a combobox to select a donor record, and also for fields in mailings. The master donor table has a linked relations table i.e. individuals related to the master donor record. In some cases the relations are Household, in some cases the relations are ContactP. If Household, I run a query to concatenate the first names of the individuals thus John and Mary Doe (Doe being the NameID in the master record). If Contact then I want to combine the first and last name thus Harry Smith (who is a contact at ABC Bank, the NameID). The temp tables both have a common field of CombNames to contain the concatenated data from both queries. So eventually the data can be displayed as: John and Mary Doe 123 High Street -or- ABC Bank Attn: Harry Smith 123 High Street So far, so good. But now I need a way to automate this whole process for use in the combo box. In other words, when the form is loaded this procedure should run so that the temp table is current and available for selection. Will a macro work for this? How will it handle the usual prompts about making a table, updating a table, etc. Presumably, this should be seamless and invisible to the user. I will also, at some point, need to filter the results so that the combobox only shows a list of Smiths rather than a huge list of names. Thanks for your help, Sandra G -----Original Message---- I may be missing something here, but I think there is a very simple solution: you can create a macro to run each query in order: * Open a new macro in macro design view * Select 'OpenQuery' from the 'Action' column * Select the 1st query name from the 'Query Name' Argument (bottom of screen) * repeat (in order) for the other two queries I believe this will work If you don't want to be asked before running each query, use the 'SetWarnings' action to turn them off before the queries run - but DONT forget to turn them back on afterwards (in the last line) or you'll accidentally save design changes and all sorts of problems Kind Regards, Wes -----Original Message----- 1. qryML1 makes a tblMLTemp to concatenate certain fields. 2. qryML2 makes a tblMLTemp2 to concatenate certain fields using different criteria for the fields. 3. qryMLUpdate updates the certain fields in tblMLTemp based on the values of the fields in MLTemp2. These all work as expected manually and give the correct results. Now, how do I automate this proc so that first you run qryML1 to get tblMLTemp records Correct, then run qryML2 to obtain the other changes in the table, then run the qryUpdate to modify the records in tblTemp with the records in tblTemp2. Thanks in advance! Sandra G . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update Queries using queries | Vel | Running & Setting Up Queries | 7 | June 4th, 2004 09:21 PM |