Counting records between NOT directly linked tables (1 & 3)

[AGilek]

Hi, everyone!

In my database of weekly music charts (FMPro14) there are 4 linked tables:

 

[Hits] - list of the hit singles

[Entries] - list of different entries, remixes, reissues

[Runs] - list of chart placings for every week

[Dates] - list of dates of the weekly charts

——————————————————————————————

[Hits] contains:

<idHits>     index field

<Title>        text field

 

[Entries] contains:

<idEntries>    index field

<Title>        text field

<idHits_fk> number field, a foreign key which links [Hits] table with [Entries] table

 

[Runs] contains fields as below:

<idRuns>       index field

<Place>         number field, position on the chart

<LastWeek> number field, last week position 

<idDates_fk> number field, a foreign key which links [Dates] table with [Runs] table

<Title>           text field, title of a single

<Name>        text field, name of the artists

 

[Dates] contains:

<idDates>    index field

<Date>        date field

——————————————————————————————

Relations are as follows.

 

[Hits] & [Entries] are linked by the relation:

Hits.<idHits> = Entries.<idHits_fk>

 

[Entries] & [Runs] are linked by the relation:

Entries.<idHits> = Runs.<idEntries_fk>

 

[Dates] & [Runs] are linked by the relation:

Dates.<idDates> = Runs.<idDates_fk>

——————————————————————————————

 

Two years ago, on this forum, with a Consultant help, I have defined the self-join as:

Runs::idEntries_fk = Runs 2::idEntries_fk

AND

Runs::idDates_fk ≥ Runs 2::idDates_fk

 

where Runs 2 is a new occurrence of the Runs table. Then define a calculation field (result is Number) = Count ( Runs 2::idRuns )

This will return the number of times the entry has appeared in the Runs table before (and including) the current week.

 

So this allows counting chart weeks for every record in [Entries]. 

Now, I’d like to also count TOTAL number of weeks, for [Hits], not [Entries], something like:

 

Runs.<Place>; Runs.<LastWeek>; Entries.<Title>,  TotalWeeksCount for main record in [Hits]

——————————————————————————————

 

Example. Let’s say there is a hit single titled „Purple Rain”. Looking at its chart history we should get something like:

 

Purple Rain - original entry - 10.10.1984 - 10 (0) 1 week

Purple Rain - original entry - 17.10.1984 - 8 (10) 2 weeks

…

Purple Rain - original entry - 14.12.1984 - 43 (33) 10 weeks

— out of chart —

Purple Rain - re-entry - 08.07.2008 - 56 (Null) 11 weeks *** 11 weeks, NOT 1st week of this new entry! ***

Purple Rain - re-entry - 15.07.2008 - 48 (56) 12 weeks 

 

How can I get such results? Help needed, thanks!

[comment]

I am afraid I got quite lost in your explanation.

Perhaps this general rule can help: given a chain of one-to-many relationships:

Parent -< Child -< Grandchild

the expression =

Sum ( Grandchild::Value )

when evaluated from the Parent table, will return the total sum of all grandchild values related (indirectly) to the current parent record.

Similarly, the expression =

Sum ( Child::cSum )

where Child::cSum is a calculation field with the same formula as the one before, will return the same result.

 

Edited June 7, 2016 by comment

[AGilek]

Hmm, this is probably my fault. I'll try to explain.

My chain of  one-to-many relationships would be:

Hits -< Entries -< Runs

In the Runs table you can find all the weekly chart information: DateOfChart, PositionThisWeek, PositionLastWeek, idEntries_fk (foreign key to Entries).

And for a particular chart I get something like that:

DateOfChart, PositionThisWeek, PositionLastWeek, idEntries_fk (title taken from Entries table), WeeksCount calculated 

11.10.1997, 1, 0 , HITSINGLE, 1

All my WEEKS COUNT calculations are for Entries. If there was another re-entry for HITSIGNLE on the way, weeks count starts from 1 on.

I wrote before how the current weeks count is calculated:

Runs::idEntries_fk = Runs 2::idEntries_fk

AND

Runs::idDates_fk ≥ Runs 2::idDates_fk

where Runs 2 is a new occurrence of the Runs table. Then define a calculation field (result is Number) = Count ( Runs 2::idRuns )

There is no direct info on HITS in Runs table.

But I want my chart to display the weeks count from the very first chart to certain chart in history (not total up-to-date weeks count).

So, as in the example above, if this was a re-mix and there was original entry in 1995 for, say, 6 weeks, the number of total weeks on the chart week ending 11.10.1997 would be 7, not 1. On 18.10.1997 would be 8, not 2, etc.

Edited June 8, 2016 by AGilek