Jump to content

Relating records based on time and date


This topic is 7314 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7314 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.