Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 51

Trying to automate access

  1. #16
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Steve,
    The first "ParseFccDataFile.mdb" you sent does work and saves time, but I still need to edit the table to get correct results. The Station "KABC" and Station "KXYZ" rows have to be removed (not a big problem) but the real issue is the way the FCC table starts with 0 and then starts counting up to 359 as the final entry in the table.
    What typically do with Excel is to cut the first data pair "0 145.2 m" in the table, change it to read 360 145.2 m and then paste it to follow 359 142.3 m
    (It prevents the 0 data pair from being called the 1 data which is actually different).This way it starts counting at 1 and goes up to 360. I see that a comma (,) divides it from the next table but Access continues to count consecutively and starts the first data pair 361. Is there an easy fix to this ?
    I can't seem to get the second "ParseFccDataFile.mdb2" to work. It goes to a de-bug page and returns a Compile error. For some reason the "Parse" button will not highlight.
    I'm not sure what I'm doing wrong. Even if the code will just run the tables one at a time, this would still save me hours in the long run - so if possible I would welcome that.
    Thanks again, John

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,305
    Hi John,

    Can you tell us what you do with the data? Can be general, but plain English would be best.
    I see you mention different call signs/letters, but also I see within a 100 km radius of a point.
    Perhaps there are different patterns/options/queries.

    I haven't looked closely at what Steve has changed, but if there is some list of call letters to be processed, it would seem that some looping construct could handle it. Certainly parsing the 360 degree values can be done with a reusable routine.

    Good luck with your project.

  3. #18
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Steve,
    The first "ParseFccDataFile.mdb" you sent does work and saves time, but I still need to edit the table to get correct results. The Station "KABC" and Station "KXYZ" lines have to be removed (not a big problem) but the real issue is the way the FCC table starts with 0 and then starts counting up to 359 as the final entry in the table.
    What I typically do with Excel is to cut out the first data pair "0 145.2 m" in the table, change it to read 360 145.2 m and then paste it to follow 359 142.3 m This way Access starts counting at 1 and goes up to 360 and the Source lines and Output lines correspond. I see that a comma (,) divides it from the next table but Access continues to count consecutively and starts the next data pair as 361, 362 etc. Is there an easy fix to this ?
    I'm not sure what I'm doing wrong but the second "ParseFccDataFile.mdb2" goes to a de-bug page and returns a Compile error. For some reason the "Parse" button will not highlight. Even by doing the tables one at a time, this code will save me hours over the long run so if it's possible to get a one table version I would welcome that.
    Thanks again, John

  4. #19
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Ooops.....
    Looks like I posted the same reply twice. Sorry. Still learning my way around the Forum. To answer the question from Orange I use the results of the Calculate HAAT tables to calculate a stations "Service Contour" or Coverage if you will. Attached is a sample.
    I'm retired and pushing 70 but still dabble in the radio world for a few clients. All of what we are juggling here can be done by commercially available software if you have the thousands to rent the license and pay for the monthly updates. I manage to do the same using multiple little processes with Access and Excel - it's just tedious and time consuming, leaving no time to fish...
    Attached Files Attached Files

  5. #20
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    This is an interesting thread.

    I would like to know what you're doing with the 'usable' data.
    Are you importing that information into MS access after the fact? If you are it seems to me you could just append records to a table as you go along rather than creating a new text file
    Are you trying to process multiple files at a time? If so you can use filesystemobject commands to process all files in a specific folder rather than having to hand pick each one
    is each file specifically for 1 radio station? based on what I see in the example file it seems like each station will have it's own file because there's nothing in the file to tell you what the call sign is that I can identify
    Are the files named in the same format all the time (in the example it starts with the radio station call letters KPRT-FM)? If they are you can extract that bit of information from the start of the file name to help you identify different stations.

    I'm filled with questions as to the WHY/HOW this data is going to be used.

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,305
    rpeare,

    Good questions. I could say "ditto", as I am also trying to sort out the usage and relation of the various pieces.
    Certainly some automation (bypassing some software steps) seems possible, but we need to know some of the details.

    John, can you give us a scenario..... XYZ has a question about station AZZY---he/she wants to know.....
    Or is there some other pattern eg. for FM stations within 100 miles of 35.22 N, 80.8 W what is the most appropriate antenna height?

    tblOne-- stations call letters for which we need info.

    tblTwo -- coordinates of points for which we need radio/antenna info.

    These are made up, but is the level I'm working at.

    Good luck with the project.

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    John,
    I modified the code to change 0 to 360. But I just realized you are using A2002 and I have A2010. The dB should run, but if you still can't open/execute the dB, I'll fire up one of the confusers at home that has A2000 and convert the dB.
    I cut out all of the code except the two subs I wrote and modified the form - just two text boxes and two buttons.

    You should check the references for anything that says "Missing" and uncheck them. You might need to add a reference for "Microsoft Scripting Library".
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #23
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Gentlemen,
    The "usable" data that rpeare refers to is my final list of stations to consider when looking for an opening on the map to move a station or maybe upgrade a station to a higher power level. The FCC has a lengthy set of regulations for this process. Simply put, there's a minimum distance to locate an FM station from other stations on the same frequency. That way the football game won't be mixed with gangster rap music and cause give us all a migraine.
    Furthermore, I have to consider stations on the 3 frequencies just above and just below my channel. So if my frequency is 99.1, then 99.3, 99.5 and 99.7 all limit any open areas on the map. Also 98.9, 98.7 and 98.5 are limiters below me. I use Excel to help me visualize & sort all this out although, I have a feeling Access would work too (likely better) but I have yet to learn it or Visual Basic.

    The attached .pdf was from a map with the circles representing distances and the dotted lines the station contours. The only true open area on the map is the pizza shaped triangle at the western edge (left side) of the map.
    If the 3 pairs of channels above and below my frequency don't work then moving up or down one channel might create an opening on the map. That creates a new set of 7 channels I need to plot on the map. With some exceptions the FCC allows an FM station to move up 3 or down 3 channels from it's assigned frequency. So that makes 7x7=49 sets of data to review. The FCC even has an another pair of "I.F. channels" I have to plot in many cases, but enough for now...
    Besides just distances, the FCC has more rules on the station contours (the dotted circle-like figures) so to create contours, the Calculate HAAT (Height Above Average Terrain) data tables are needed.
    The concept rpeare has of appending to a table and using folders may be the answer. The FM Query database remains generally unchanged over time with only a few additions and deletions here and there on a weekly basis.
    Because Access is a database tool it would be nice to just load the FCC data into it and then query what is needed and nothing more. Each row would be an entry and additionally there are several links on each row that return needed data.
    Steve wrote some code that helps me to automate the Calulate HAAT link data so it would be nice to get Access to return a list of those tables too.
    I hope no one is shaking their head or rolling their eyes...
    Attached Files Attached Files

  9. #24
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,305
    John, Thanks for the update. But what exactly starts the process-- a new station, a new frequency or some geo coordinates?
    I guess I'm a little slow on understanding/analyzing the requirement.
    Steve has code to get the HAAT given the station. But what list of stations, and their respective HAAT is needed. If it's the big picture that would benefit from automation, then details of the whole process may identify the points where Access fits.
    It doesn't seem (to me at least) that calculating the HAAT values is all that is required.

    Load FCC data initially (for what area generally?)
    Check for and apply updates to FCC data as necessary
    Identify Stations or Coordinates to be analyzed using HAAT calculations and FCC data
    Review queries/reports (analysis)
    Identify options/decisions

  10. #25
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Many radio station owners rent space for their antennas on a tower they do not own. The building at the tower base houses their transmitter and associated equipment, with air conditioning, lighting and security fence all provided by the tower owner.
    When their lease expires the rent almost always goes up and up. At some breaking point the station owner decides to look for another cheaper tower or even some land where they can build their own tower and equipment shelter.
    Another scenario is that a stations coverage into a city is marginal and they want to move closer to reach more listeners. How close can they get without interference to the other stations has to be calculated.
    Within yet another set of rules they might even be able to move their station to cover another small town or city some distance away - that's called a Change of Community. So the process is almost always initiated by the station owner or licensee.
    There is now a proposal to create a Low Power AM category that will likely require the applicant to be a non-profit based in the community to which the station would be assigned or licensed. A few years ago they did this with the FM band and got hundreds of 100 watt (or less) local FM stations on the air. Where FM needs a tall tower or hilltop, AM works best in wet, conductive soil in a valley or swamp. This Low Power AM would be the "New Station" scenario.
    So based on where the station is and/or wants to move to, my initial FM Query is done to gather data and then later filter and structure it as needed.
    The FCC data will also show the distances of all the stations from the latitude/longitude figures you entered if you use a radius search. That can be very handy to know in some cases.
    The steps you outlined are logical and pretty much do describe the whole process :

    1. Load FCC data initially (usually based on a radius search).
    2. Check for and apply updates - Data will be current but maybe subject to change in a day or a week.
    3. Identify Stations and Coordinates to be analyzed using HAAT calculations and FCC data
    4. Review queries/reports (analysis)
    5. Identify options/decisions

    Somewhere in the list should be a step to generate a map. I do that using the Topo North America program by entering comma delimited sets for each station. An example is attached in a text file. It plots them as points on the map. Like they say - A picture is worth a thousand words. It's the final result after crunching the numbers from a Calculate HAAT table.
    Hope this sheds more light on things - John
    Attached Files Attached Files

  11. #26
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I'm enclosing my example of how to extract the data from multiple files at once. The form will browse the 'default' folder when you open it to list all the available files then you can select the ones you want to process. There is some simple checking going on in the background, like you'll be prompted to confirm whether or not you want to overwrite existing data or not and you'll be prompted to create a folder for your processed/unprocessed folders if you put in a folder name that doesn't exist. I basically cut and paste the data from the website you put in your post into individual text files and named them similarly to the way your single example file was named. This is the expected naming convention for the file because the call letters do not appear in the text file itself.

    At any rate this was kind of fun and I would really like to see where this goes in terms of your calculations etc.

    Signals.zip

  12. #27
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    @rpeare,
    Nice bit of code.
    However I was getting duplicate "RSH_Degree" values in the table and the degrees stopped at 119. I finally tracked it down to two aLine() values:

    Your code
    Code:
    <snip>
    Do Until InStr(sline, Chr(176)) = 0
                            sline = Replace(Replace(Replace(Replace(Replace(sline, Chr(176), ""), "m", ""), "  ", " "), "  ", " "), "  ", " ")
                            aLine = Split(sline, " ")
                            'insert each radial value into the table tblRadioStationHAAT
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(0) = "0", 360, aLine(0)) & ","
                                sSQL = sSQL & aLine(1) & ")"
                                CurrentDb.Execute sSQL
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(2) = "0", 360, aLine(0)) & ","
                                sSQL = sSQL & aLine(3) & ")"
                                CurrentDb.Execute sSQL
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(4) = "0", 360, aLine(0)) & ","
                                sSQL = sSQL & aLine(5) & ")"
                                CurrentDb.Execute sSQL
                            sline = Trim(f.readline)
    <snip>
    I changed it to :
    Code:
    <snip>
    Do Until InStr(sline, Chr(176)) = 0
                            sline =  Replace(Replace(Replace(Replace(Replace(sline, Chr(176), ""), "m", ""), "   ", " "), "  ", " "), "  ", " ")
                            aLine = Split(sline, " ")
                            'insert each radial value into the table tblRadioStationHAAT
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(0) = "0", 360, aLine(0)) & ","
                                sSQL = sSQL & aLine(1) & ")"
                                CurrentDb.Execute sSQL
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(2) = "0", 360, aLine(2)) & ","    '<<-- changed 0 to 2
                                sSQL = sSQL & aLine(3) & ")"
                                CurrentDb.Execute sSQL
                                sSQL = "INSERT INTO tblRadioStationHAAT (RS_ID,RSH_Degree,RSH_Height) VALUES ("
                                sSQL = sSQL & iRSID & ","
                                sSQL = sSQL & IIf(aLine(4) = "0", 360, aLine(4)) & ","    '<<-- changed 0 to 4
                                sSQL = sSQL & aLine(5) & ")"
                                CurrentDb.Execute sSQL
                            sline = Trim(f.readline)
    <snip>
    No more duplicated "RSH_Degree" values.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #28
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    25
    Thank you (all) for the FileParser program. I tried to open it with my ancient Access 2000 but it returned an error message.
    I also tried with another program that supposedly will open Access 2007 but it also would not work. Is there a way you can
    save it in the older .mdb format ? I'm trying to get a laptop with a newer version of Access - A2010 I think.
    The sample text files are the exact way the FCC returns their tables with the Calculate HAAT link. They also display the average HAAT based on an input for Above Mean Sea Level (AMSL). And they post a very handy conversion from NAD 83 to NAD 27 latitude/longitude pairs. For some reason all radio towers are referenced to the newer North American Datum 83, but the stations that are licensed on those towers are displayed in the NAD 27 datum. And they still use miles and kilometers in their regulations. Reminds me when some of the gas stations had pumps that measured in liters - not very popular at the time...

  14. #29
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,305
    Perhaps Steve or rpeare who have the latest code can supply an mdb version.
    What error message/number did you receive?

  15. #30
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Thanks for the catch ssnafu... that's what I get for cutting and pasting code!

    This is a version saved in Access 2000 format. if that doesn't work well.. the code I'm using has worked since access 1997 so you could cut and paste the code I suppose.

    FCC_File_Parser_2000.zip

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-20-2019, 10:42 AM
  2. automate importing access
    By mcchung52 in forum Access
    Replies: 1
    Last Post: 01-21-2012, 02:20 AM
  3. Automate report on ACCESS
    By Schoolo in forum Database Design
    Replies: 1
    Last Post: 12-18-2011, 02:11 AM
  4. Automate Access Queries
    By andreala in forum Queries
    Replies: 5
    Last Post: 08-23-2011, 06:01 PM
  5. How to automate a access query ... Help me
    By kusamharsha in forum Programming
    Replies: 0
    Last Post: 02-25-2009, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums