Reorganize Data For Time Series Analysis

[Hellmut]

Thanks to your help, I have defined a simple database and entered the data. Now I need to reorganize the data into several timelines.

The data tracks violence in the lives of high officers of the English crown who held office between 1603 and 1763. The tables reflect events in the lives of the officers.

I would like to reorganize the data into timelines that list certain events annually such as

how many individuals are alive in any given year,

how many of them suffered a violent event of any kind in any given year,

how many knights, barons, earls etc. were alive in any given year,

how many knights, baron etc. had to suffer violence in any given year,

what kind of violent events occured how many times any given year, and

what kind of violent events occurred to knights, barons etc. in any given year.

FYI, I am enclosing the database.

I gather that calculating field values might be one way, especially if one creates a layout with annual values. Since I would have to define and program some 160 fields per timeline, I am sure that there will be a more elegant solution.

Also, I have no idea about the code required to calculate fields and would be grateful for general and specific tips about that as well.

Leviathan__s_Database.fp7_2.zip

[comment]

I believe you need an additional table of Years, with 160 records - one record per year. Then a single calculation field in the Years table will provide 160 different results. You will need to define relationships between the Years table and your other tables (more precisely, between the Years table and occurences of your other tables).

For example, if you have a relationship to a table occurence of Individuals defined as:

Years::Year ≥ IndividualsAlive::YearOfBirth

AND

Years::Year ≤ IndividualsAlive::YearOfDeath

then a portal based on this relationship will show the individuals that were alive in this year, and a calculation of Count (IndividualsAlive::IndividualID) will show how many. This is a relatively simply example - some of your other requirements are more complex.

I am not sure how you want to handle Individuals with no YearOfBirth/YearOfDeath. Some sort of calculated default should be put in place, I think. And, BTW, you really shoudn't have empty records...

[Hellmut]

Thanks, Comment.

Edited May 13, 2006 by Guest

[Hellmut]

I am making some knuckleheaded mistake, Comment, and can't quite get it to work.

I created the new table, defined a computed field and created an additional lay out. I also cleaned up the data according to your suggestions. Nonetheless, I cannot get the annual names to show in the portal.

Leviathan__s_Database.fp7.zip

[LaRetta]

I see no IndividualID Number in any records in your Annual Events table.

Yep, portal fills when you plug in an ID such as 372.

Edited May 14, 2006 by Guest

[Hellmut]

Shouldn't the calculated field import the right individuals?

[LaRetta]

Well. All I can tell you is that the portal is based upon Individuals (and so are the fields). And the Layout is based upon Annual Events. Your graph from Individuals to Annual Events is based upon Individual ID Number on both sides.

Annual Event::Individual ID Number is a number (indexed)

Individuals::Individual ID Number is a number (indexed, auto-enter serial)

And it produces the correct results but you have NO Individual ID Numbers in your Annual Event table!! They are blank. This isn't what you and Comment were discussing as far as I can tell but the relationship certainly isn't working without IDs - not on THIS table occurrence anyway!

Heck, this can't be the file. It doesn't even HAVE the calc Comment mentioned in Individuals. No calcs!

UPDATE: Okay calc is in Annual Events. But it's auto-enter (calc) number... and that relationships is still based upon the IDs.

Edited May 14, 2006 by Guest

[comment]

I meant something like this (attached). Of course, it won't work properly when people are dying before they were born...

Leviathan__s_Database.fp7.zip

[Hellmut]

Thanks, Comment. I am on my way now.