Results 1 to 6 of 6
  1. #1
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54

    Question Airport database

    Hi,



    For my internship I have to create a database for 103 airports in Australia.

    Data like what kind of operations (regional, domestic, international), passenger numbers and air movements were available in a Excel sheet. And information like ICAO code(international acknowledged ID code), which state, what kind of airport etc. has been collected. Now I need to implement it into Access.

    I would say my knowledge of Access now is just at intermediate lvl. I know how to set up the relations, create queries, forms etc. But there are several problems I have walked in to.

    For the user it should be possible to look up information about a random airport in a form. Information like what state, type of operation are easy to display. But I also got data of each airport that goes back to 1985-86. What I would like to to do, is in the Form the user should be able to select a airport. Within that form, the user should be able to select a year and then he would still be in the same form, but see the data from 1996 for example.

    What I figured out is that I can setup a table of each airport with an AirportID, an table of each year with an own ID, and a table with type of operation and their own ID. What I would then do is create a table (name it DataID) and relate those ID numbers to each other. What you would have then is a table with 103*28=2884 rows.

    For example: |AirportID| YearID| TypeOfOperationID|
    |1| 1| 3|
    |2| 1| 2|
    |3| 1| 1|
    |4| 1| 2|
    x28
    |1| 2| 3|
    |2| 2| 2|
    |3| 2| 1|
    x28
    etc.

    I cant imagine there is not a better way of doing this?! It is possible, but I wouldnt like it doing it this way, their should be a more sophisticated way.
    The result will be now that in a form you will have a dropdown box with 2884 rows.

    Is there a way around this with programming?

    I got more questions, but this will have to do for now.

    I appreciate your time for reading, and possible answer!

    Cheers.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    How you query your data should not dictate the design of the tables.maybe a main form with some comboboxes and a dependent subform will do the trick.

    You can research dependant comboboxes. I uploaded a sample db here in the sample db forum. It illustrates using VBA to change the rowsource of comboboxes using dynamic SQL strings.

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, You're not far wrong. No reason to give a "Year" a separate table, it's just a datevalue on the record. No reason to put all the values in a single dropdown, when they are derived from three distinct values. use three dropdowns, one for airport, one for year (default to most current), and one for operation.

    When the airport dropdown is selected, the Afterupdate event should set the available values in the combo boxes for year and operation. In each combo box's afterupdate, you set the filters on the query that displays data for the user.

    Make sense?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would suggest you create a data model showing your tables and attributes and relationships.
    Here's a tutorial that goes through the process. Well worth working through before you get too deep into Access nitty-gritty.
    Good luck with your project.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I agree with orange's suggestion of visiting Roger Carlson's tutorial site. A couple of hours there will save you dozens of hours of head-banging and redesign later.

  6. #6
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Thanks for all your response, you will hear from me!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-27-2013, 09:51 AM
  2. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  3. Replies: 5
    Last Post: 08-22-2012, 04:48 PM
  4. Help with making airport booking system
    By matthewjones108 in forum Access
    Replies: 3
    Last Post: 03-13-2012, 08:10 AM
  5. Automatic traffic count on small airport.
    By methosmen in forum Access
    Replies: 1
    Last Post: 06-11-2010, 07:32 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
  •  
Other Forums: Microsoft Office Forums