Automatic traffic count on small airport.

[methosmen]

Hi, I work at a small airport in Norway. Today we write traffic statistics by hand and I would like to make this more automatic.



I only have a general knowledge of Access and would appreciate some help getting started.

I'm using Windows 7 and MS Access 2007.

Everyday we can save arrivals and departures from the Norwegian Aeronautical Information System into a text file. I have included the text file here.
As you can see, the problem is that the text file is not csv. In access I find the most logic choise would be to import it using fixed width. When doing so I get a table looking like this.

I would like for Access to automatically build a table like this everyday if a text file with "todays" date e.g. "100611.txt" exists in folder X.
I would then want Access to run through the table creating two new tables, one for departures and one for arrivals for that date. The field names should match the field names of ID7 from the .jpg file. When a match for ENXX under field7 is found it should write a new line to Arrivals table using the ID from the match and go through all the fields. Same for departures when a match for ENXX is found under field6.

I hope I was able to make this understandable. I am not into the lingo of programming.

Is this possible? Any starting help here is highly appreciated.

[NTC]

yes it is possible. though your terminology and approach isn't...but that is ok.

One can import a text file. But to automate that isn't really an Access issue. Access can easily set up an 'import specification' that you can trigger with a button click. You can either remember to it each morning - or have it trigger upon open of the application (presuming you open the application each day) or otherwise get to know the Task Manager type feature of Windows7 and try to make the opening of the application (and import trigger) a scheduled system event.

Your import task - fundamentally "builds the table". One doesn't need any other table building per se - and one would not "run thru the table and create 2 new tables". The data is there or it is not (this isn't an Access issue but a fundamental database concept). Thus to see Arrivals you would query that table. Same for Departures, same for todays, same for yesterdays, etc.

Bottom line - yes Access database can definitely do what you need. The only real limitation of Access is that it is not web based (unless you co-invest with Sharepoint) and about 20+ simultaneous users really pounding on the data (no hard and fast rule on this one) one needs to be wary. Also its 2G max data file size. But otherwise it is a very solid database with integrated form development and report development.