Relating records based on time and date

[one09jason]

Here's a problem I'd like to solve with FM.

Imagine you have two databases. One contains a list of, say, zodiac signs. Each record would contain a sign like capricorn and the dates that define that sign.

Another database contains a list of people and their birthdays.

I'd like to write a script that goes through the database of people, and adds to each record what sign each person belongs to.

Can anyone think of a way to do this?

[Vaughan]

Make a "Zodiac" database that contains records that are dates of the year. The format for this date should be just the day and month, perhaps mm-dd. On each date record, make another field with the Zodiac sign entered into it for each record.

In the "Birthdays" database, each record is a person with a field for their name etc and their birthday. Define a calc field that converts the brithday into a mm-dd value in exactly the same format as used in the Zodiac database.

Now relate the two databases using the mm-dd field as the match fields. If you make the Birthdays database the master (that is you define the realtionship in the Birthdays file) then the Zodiac sign can be automatically pulled in as soon as the brithday is entered -- no need for scripts.

[one09jason]

Thanks for the idea. The zodiac example, however, was just for the purpose of illustrating the type of problem. The real-world problem is actually a wildlife study that tracks free-ranging animals' visits to certain locations. One database contains ranges of exact times an dates that certain conditions were true at those locations (zodiac signs). The other database contains the exact date and time that individuals visited those locations. (birhtdays)

So you see, it wouldn't be practical to create a "zodaic" database that contains a record for every possible point in time, since animals can visit the locations at any time on any date.

I need to be able to use an exact date and time from a list of events, and find out which period of time that record belongs to. Think of it this way, imagine you have the birthdays of individual dinosaurs in one database, and you want to add a field to each record that tells you what epoch that dianasaur lived in. You'd want the epoch database to contain just the names of the epochs and the range of dates that they were in effect. It wouldn't be practical to create a database that contains one record for every day over millions of years, just to be able to find out when each dinosaur lived.

I appreciate the suggestion though. Anyone else want to venture a guess as to how this could be done?

Thanks

[Vaughan]

quote:

---

"Here's a problem I'd like to solve with FM... I'd like to write a script that goes through the database of people, and adds to each record what sign each person belongs to."

---

For some reason -- silly me -- I misread your opening line as an *actual* request for help on making a brithdays/zodiac database.

[one09jason]

The reason that I used the zodiac example was for the sake of simplicity - it was the simplest framework I could think of for describing the problem. I didn't want to confuse peple with an unnesecarrily complicated description of the problem, for fear that they might not understand what I was actually trying to solve. You certainly did solve the problem. I just didn't anticipate that the solution to the example would be too simple for the actual problem. My mistake - but the problem, and the question still stand.