I am in the midst of a redesign of a database that has been running for 3 years now. One piece has always been problematic and I am sure there is a way to do this easily, but I am hoping that smarter folks, that's everyone else, could give me an idea.
Table one: Parent for an Aircraft Reservation
Child Table: Services for that aircraft reservation. Basically a Service type field and any notes
There will be 15 or so hard coded service types and there can be many of the same services for each reservation. Many times only 2-3 services are used.
I need to create a status board which lists all of the reservations and checks to see the lowest status for each type of service (15 possible) that exists amongst the child records for that reservation and display it in a table format. This could get quite computer labor intensive looking up each displayed reservation and all of the services for that reservation. My current application is a flat file containing all of the services possible in the reservation record, but this is very problematic and one of the reasons for the switch.
The status board runs on three machines and is updated every 30 seconds which is why I would like to have the most minimally invasive way to get this data.
Is there an easy calculated field perhaps that can look at only the child records and then I can check all 15 possible services to get the lowest value? This has been in my mind for the last three years and I want to move forward with the new rewrite. Thanks for your time in advance.
Table one: Parent for an Aircraft Reservation
Child Table: Services for that aircraft reservation. Basically a Service type field and any notes
There will be 15 or so hard coded service types and there can be many of the same services for each reservation. Many times only 2-3 services are used.
I need to create a status board which lists all of the reservations and checks to see the lowest status for each type of service (15 possible) that exists amongst the child records for that reservation and display it in a table format. This could get quite computer labor intensive looking up each displayed reservation and all of the services for that reservation. My current application is a flat file containing all of the services possible in the reservation record, but this is very problematic and one of the reasons for the switch.
The status board runs on three machines and is updated every 30 seconds which is why I would like to have the most minimally invasive way to get this data.
Is there an easy calculated field perhaps that can look at only the child records and then I can check all 15 possible services to get the lowest value? This has been in my mind for the last three years and I want to move forward with the new rewrite. Thanks for your time in advance.
Comment