Calculation or Script to avoid double or repeated data/records

[amerioca]

I am proud to announce that I have just managed to create a ground operational aviation control mission db. Meaning: A db which calculates and reports crew duty times, flight hours, schedules , cargo loads, passenger loads, fuel uploads and usage, departure times, arrival times, delay hours and reasons, daily, weekly, monthly schedules for airplanes and crews and quite a few more things.

Even though, looking behind the curtain, many of the db cracks would probably consider it a joke…..for me it works and “ the mother even looks good” and last but not least, which was my goal from the very beginning - she is very user friendly.

Now I need one more feature (for now): I have to avoid double scheduling of airplanes – same airplane, same day - same time - and crews. In plane English: I want the db to avoid the creation of the same record with the same data in the fields ‘date ‘ and ‘time’. One air plane can fly various flights per day, but not within the same time period on the same day. For air crews it gets a little more complex as they are only allowed to fly certain time in a certain period (100 hours in 28 days or 120 in 30 days).

Anybody aware of a script of calculation or a combination of both, which would automatically refuse double data entry and gives a custom message on attempt?? Here the tables

- Airplane Details = Airplane Registration, call sign, structural weights, scheduled times etc.

- Details Pilot in Command = Personal data, hours flown while on contract, hours on entry, etc.

- Details Second in Command = Same as Pilot in Command

- Flight Mission Detail = 60 fields consisting out of actual departure, arrival and flight times, crew names, delays, actual cargo loads, actual passengers and many more aviation specific information.

Any help is highly appreciated.

[comment]

You can detect overlaps by using a self-join relationship, such as:

Flights::AirplaneID = Flights 2::AirplaneID

AND

Flights::FlightStart < Flights 2::FlightEnd

AND

Flights::FlightEnd > Flights 2::FlightStart

AND

Flights::FlightID ≠ Flights 2::FlightID

In this example, FlightStart and FlightEnd would be timestamp fields.

If a flight has any related records in Flights 2, there is a conflict - so you could validate a field in Flights (any field, as long as the validation is defined to validate 'Always') by a calculation =

IsEmpty ( Flights 2::AirplaneID )

If the creation/modification of flights is scripted, you can have the script check the same condition and avoid the annoying validation fail message.

Off topic, I don't see why you should have two tables for pilots instead of one for the entire flight personnel.

[amerioca]

Tnx a lot for your quick reply, I will try it out. I forgot to mention, that I am a total rookie and after 20 hours Lynda over and over again., this is my first project, which as I have said, looks sort of chaotic behind the curtain -which doesn't really matter at this point, as I intent to used it for my personal purpose only - but most of the features work just great.

I guess, before I go into scripting/calculating I would have to create a self join relationship in the ERD. Is that correct??

[comment]

Basically, yes - except the relationships graph is NOT an ERD.

[amerioca]

Tnx a lot, once again. I further presume, that I have to validate the fields with the formula?

As for the 2 tables for crew, initially I had only one for all of them, but whenever I tried to schedule both, PIC and SIC (and our airplanes need both)taking the data out of the crew table on the same schedule in air craft schedule table, with their respective RecordID.pk, I could not get it to work. Whenever I changed the ID of the PIC, the SIC automatically changed, so I was only able to retrieve one value.

I am new here, and do not exactly know, (yet) how to attach files but I will attach a screen shot of the RD and Aircraft schedule table.

Would appreciate your feed back.

Tnx again and sorry for the work load with the "rookies".

[comment]

I further presume, that I have to validate the fields with the formula?

ONE of the fields. FlightEnd would be a good choice for this.

I can't see your attachments, probably due to some blink of the forum's software. Let's see if I can attach one.

...

Nope. Same thing.

Edited September 22, 2008 by Guest

[amerioca]

Hi and tnx for your efforts and input. As for your attachments, or mine, that's a negative. Unfortunately it looks like, attachments do not work here.

If you can supply any email, I would like to forward to you, wile we can keep communicating via post, so that other users may benefit from the solutions.

tnx

[comment]

I am sure Stephen will fix this shortly. Meanwhile you can see my attachment (I hope) at:

http://www.filedropper.com/crew

[amerioca]

Hey, tnx a lot for all your help. This is actually my first project and I can really use all the help I can get.

The RD was originally set up the way you posted it in your attachment. Now it has the joint relationships, which I have created. I have posted you some details at:

http://docs.google.com/Doc?id=df84tvbc_7t9drc8d4

which provides a little more detailed where I stand, what it is and how it looks.

appreciate your input

tnx again

Edited September 24, 2008 by Guest

[comment]

Lot of issues - perhaps too much to be discussed at once?

The formula I have provided:

Flights::AirplaneID = Flights 2::AirplaneID

AND

Flights::FlightStart < Flights 2::FlightEnd

AND

Flights::FlightEnd > Flights 2::FlightStart

AND

Flights::FlightID ≠ Flights 2::FlightID

is to be applied in the definition of the self-join relationship, NOT in field validation. The field validation, as stated above, is just this =

IsEmpty ( Flights 2::AirplaneID )

In a nutshell, the relationship makes any overlapping flight (except self) related. The validation requires that no related records exist.

Note that the relationship uses timestamp fields, so that a flight crossing midnight can be correctly identified as overlapping other flights, both before and after midnight. If you prefer entering start and end data as date and time, you need to add calculation fields to transform the entered data into timestamps, and use these calculation fields for the relationship.

I wasn't able to tell what's the difference between your 'Air Craft Schedule' table and 'Flight Mission Detail' table. It seems to me one aircraft has many mission to fly, and all these mission together form the aircraft's schedule - what am I missing?

[amerioca]

Tnx for the input..and no, you did not miss a thing...I went over the whole thing last night and came to the same conclusion. The "Schedule" Table and the "Mission Detail" table consisted out ot 80% identical data. I have restructured and posted the new RD here:

http://docs.google.com/Doc?id=df84tvbc_13fs58634p

I got you as far as the validation is concerned. Unfortunately, as mentioned, I am brand new in this thing and can not figure out, how to apply the formula in the definition of a self join relationship. I am also not sure, if I have set up the joint relationship correctly.

Would you pls tell me in detail, how to do that?

1000 tnx

[comment]

It should look something like this:

http://www.filedropper.com/flights

Edited September 25, 2008 by Guest

[amerioca]

Tnx a lot Comment. I have applied everything, as you designed, but still not working. It still allows double entries. Would be great if you could check. Have posted you the snap shots at:

http://docs.google.com/Doc?id=df84tvbc_15g7ppwgfb

At a later point I will need it like you have mentioned, as for operational reasons, I need the times separated from the dates. but for now, I think, I have to get it working at all, before going into changing the details.

Quote:

If you prefer entering start and end data as date and time, you need to add calculation fields to transform the entered data into timestamps, and use these calculation fields for the relationship.

Quote Finish

Tnx once again

[comment]

I'm afraid I cannot debug your file from snapshots (even if I wanted to… ). I suggest you take a look at this one:

http://www.filedropper.com/validateoverlapfp7

[amerioca]

No problem. You have already helped tremendously to widen my limited FM Horizon. I will play around with it over the weekend, will try in a blank db with only 4 fields and let you know.

Have a great weekend.

[amerioca]

Sorry for being quite this week, but was out of the country and also it took some time to debug the db.

Well, I got it working this way. Really good idea with the portal you have placed in the sample. I did the same thing and with this portal, I basically can control all flight missions and also crews.

Even though, the portals make it sort of perfect for the mission specific requirements, having it taken to this point, for the sake of interest, I would love to know, how can it work with simple date and time fields, as I can not use time stamps for the planning, for reporting reasons.

Any idea??? Tnx once again. Great job.

[comment]

I thought I have answered this already. You can use date and time fields for data entry (and for anything else you like). For the relationship, use timestamp fields calculated from the date and time.