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.