Formula Help

[Robert]

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.

 

[Duke Carey]

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!

 

[Robert]

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]

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]

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.

 

[Robert]

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]

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]

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.

 

[Robert]

Oh 1 other the thing, this initial query icon is different than the other
query icons. This might help.

Robert

 

[Duke Carey]

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]

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,

 

[Robert]

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]

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]

The table is named RF already, any more thoughts.

Robert

 

[Duke Carey]

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]

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