Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
A B C D E F
G 1 Frequency Input License Type Tone Description Mode 2 158.89500 154.84500 WPKW723 RM 82.5 PL Wilkes Barre P1 FM 3 156.09000 154.80000 WPKW723 RM 146.2 PL Wilkes Barre P2 FM 4 155.97000 WPKW723 BM Wilkes Barre P3 FM Here is what I am trying to do, Notice line 1 has a column A frequency and column B input, what I am trying to do is and if statement tha can do a comparison, if the comparison is different, insert a blank line underneath it and then copy the contents of the line that it compared into it, this time column A would be the column b see below. Once the contents have been copied delete the contents of cell B2, then append the word input into the end of the contents of cell F3. A B C D E F G 1 Frequency Input License Type Tone Description Mode 2 158.89500 WPKW723 RM 82.5 PL Wilkes Barre P1 FM 3 154.84500 WPKW723 RM 82.5 PL Wilkes Barre P1 Input FM 4 156.09000 WPKW723 RM 146.2 PL Wilkes Barre P2 FM 5 154.80000 WPKW723 RM 146.2 PL Wilkes Barre P2 Input FM 6 155.97000 WPKW723 BM Wilkes Barre P3 FM Is this even possible? The data is out of a large database query of the FCC frequency/ULS lists. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
A formula cannot insert new lines or do any of things you want, except return
a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Yes it is imported from a website. I would like to run the macro after the
import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Robert -
I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Is there a way to create a new table in the same database with the output?
This way I could have an originals table and then the final output, ironically this will become the input folder. I tried what you siad and it doesnt appear to make anything change. I copied the query from your message, that worked fine. The second query created an output identical to the initial table. :( "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Ok, I got it to work. It appears that I selected all in the query, I toggled
the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Robert -
You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Here is what I put in the initial sql query.
I forgot one field initially but added it, it does contain data and is in the table. SELECT Idx, Frequency, License, Type, Tone, CH, Description, Mode, Null as Source FROM RF UNION ALL SELECT Idx, INPUT as Frequency, License, Type, Tone, CH, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL ORDER BY Idx; Once the query was created and saved, I double click the query and I get prompted for each field to type information, is this what should happen? I must have done something wrong. "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Oh 1 other the thing, this initial query icon is different than the other
query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
The query icon looks different because the query uses UNION ALL. N other
significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Here is the exact sql query, it all matches up with the field in the table.
Here is the text box title that comes up when double click the sql query - Enter parameter value, it then goes through each field name, from the second select line, here they are Frequency, License, Type, Tone, CH, Description, Mode, INPUT all of these give you the oppprtunity to type something in a text box. Hope this helps... Thanks for all of your efforts. Robert SELECT Idx, Frequency, License, Type, Tone, CH, Description, Mode, Null as Source FROM RF UNION ALL SELECT Idx, INPUT as Frequency, License, Type, Tone, CH, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL ORDER BY Idx; Now here is the window that opens title, "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Further information, if I type something in each box when prompted, I chose
the field names, the database gets filled with what I type for each prompt. IE, when prompted for the frequency, I type frequency and so on, for each field. here is wher eit hinkn that the problem is, once the baove completes, I see the query results, I called the query query1, the default name, it then display like this Query1 : Union Query. The odd thins is the last field shown is called Source? Where did this come from, unless it came from here the query line here - "Input" as Source Thanks Robert "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
The obvious question now is: what did you name the table? Is it RF? If not,
change the two instances of FROM RF to FROM your_table_name "Robert" wrote: Further information, if I type something in each box when prompted, I chose the field names, the database gets filled with what I type for each prompt. IE, when prompted for the frequency, I type frequency and so on, for each field. here is wher eit hinkn that the problem is, once the baove completes, I see the query results, I called the query query1, the default name, it then display like this Query1 : Union Query. The odd thins is the last field shown is called Source? Where did this come from, unless it came from here the query line here - "Input" as Source Thanks Robert "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
The table is named RF already, any more thoughts.
Robert "Duke Carey" wrote: The obvious question now is: what did you name the table? Is it RF? If not, change the two instances of FROM RF to FROM your_table_name "Robert" wrote: Further information, if I type something in each box when prompted, I chose the field names, the database gets filled with what I type for each prompt. IE, when prompted for the frequency, I type frequency and so on, for each field. here is wher eit hinkn that the problem is, once the baove completes, I see the query results, I called the query query1, the default name, it then display like this Query1 : Union Query. The odd thins is the last field shown is called Source? Where did this come from, unless it came from here the query line here - "Input" as Source Thanks Robert "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
If you want to post your email address - disguise it somehow so so it's not
an obvious email address - spell out the @ sign for instance - I'll email you the Access file I've got in which the query works just fine "Robert" wrote: The table is named RF already, any more thoughts. Robert "Duke Carey" wrote: The obvious question now is: what did you name the table? Is it RF? If not, change the two instances of FROM RF to FROM your_table_name "Robert" wrote: Further information, if I type something in each box when prompted, I chose the field names, the database gets filled with what I type for each prompt. IE, when prompted for the frequency, I type frequency and so on, for each field. here is wher eit hinkn that the problem is, once the baove completes, I see the query results, I called the query query1, the default name, it then display like this Query1 : Union Query. The odd thins is the last field shown is called Source? Where did this come from, unless it came from here the query line here - "Input" as Source Thanks Robert "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Here is is and once again thanks for all of your efforts s o f t w a r e a t
l e h i g h t o n d o t o r g Robert "Duke Carey" wrote: If you want to post your email address - disguise it somehow so so it's not an obvious email address - spell out the @ sign for instance - I'll email you the Access file I've got in which the query works just fine "Robert" wrote: The table is named RF already, any more thoughts. Robert "Duke Carey" wrote: The obvious question now is: what did you name the table? Is it RF? If not, change the two instances of FROM RF to FROM your_table_name "Robert" wrote: Further information, if I type something in each box when prompted, I chose the field names, the database gets filled with what I type for each prompt. IE, when prompted for the frequency, I type frequency and so on, for each field. here is wher eit hinkn that the problem is, once the baove completes, I see the query results, I called the query query1, the default name, it then display like this Query1 : Union Query. The odd thins is the last field shown is called Source? Where did this come from, unless it came from here the query line here - "Input" as Source Thanks Robert "Duke Carey" wrote: The query icon looks different because the query uses UNION ALL. N other significance. When you imported the table, did you name each of the fields exactly what you put in the initial message? That's how I got the field names. If your table doesn't have columns that correspond to the names in the query, you'll need to change the query text so it uses the column names in YOUR table, not the ones in mine. That's the only reason that comes to my mind for Access to prompt you for the values for those names. "Robert" wrote: Oh 1 other the thing, this initial query icon is different than the other query icons. This might help. Robert "Duke Carey" wrote: Robert - You can change a query type in Access to a Create Table query. What you have to watch for is that, once you've created the table, the next time you run the query it will delete the table and re-create it. If you want to retain the original data, change the query - the second time you run it - to an Append query. Just open the query in design mode and use the Query menu option to change the type As far as the way the query worked, if you copy everything just as I gave it to you, including the UNION ALL part, putting it all in the SQL design window, you will get the extra field with INPUT at the end of the Input records. "Robert" wrote: Ok, I got it to work. It appears that I selected all in the query, I toggled the frequency field off and it created the right output. The only thing missing is the appending of the word input into the description field at the end of the existing text. Then all that is left is to create the new table and then another query to merge the tables. Then I could export the combined table to a csv file. This is then imported into another program. Is the creation of the new table with the ouput of the query possible? If it is, I can then create another query to export the first table excluding the input field, then another quuery to merge them? Is all or any of this possible? "Duke Carey" wrote: Robert - I'd pull the data into Access instead, allowing Access to create an index field. Let's say the table that Access creates is called RF (for radio frequencies) Then use a query like this: SELECT Idx, Frequency, License, Type, Tone, Description, Mode, Null as Source FROM RF union all SELECT Idx, INPUT as Frequency, License, Type, Tone, Description, Mode, "Input" as Source FROM RF Where Input is Not NULL order by Idx Enter this query in Access by creating a new query in SQL mode Frequency Input License Type Tone Description Mode "Robert" wrote: Yes it is imported from a website. I would like to run the macro after the import is complete, it can be run manually, ideally it would do the output into another worksheet keeping the data intact on the original sheet. Thanks Robert "Duke Carey" wrote: A formula cannot insert new lines or do any of things you want, except return a TRUE/FALSE on the comparison of values. You really need a macro. The queston, though, is what kind of macro? Do you import this data from some other application, and you'd like the results you describe to occur after the import is complete? If so, you want to run a routine manually after the import is complete. Or, does a user actually use Excel to key the Input values into the cells you've described? If this is the case, do you want something to happen after the users completes the input on a line, or when the user indicates that he/she is finished with the input process? If the former, then you would use an Excel event macro, called when a cell is changed. If the latter, you're back to running a routine manually when the user says to. Post back with some more details, please! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |