AnsweredAssumed Answered

How to lookup in a large reference-table

Question asked by tkemmere on Jan 25, 2016
Latest reply on Jan 25, 2016 by tkemmere

Dear all,


I have a database the keeps track of rental contracts and should be able to calculate indexation of the rent at some point in the year. There is a table with the CONTRACT-INFO (Including a couple of parameters that affect the indexation-ratio), and I made a related table that will list various YEARS and their correpondig rent-amounts.


Now the indexation options are varied, to say the least. With four different parameters that affect the indexation-ratio and a large numbder of years it comes to about 600 different of indexation-figures. So I made a separate table with INDEXATION-FIGURES.


I would like to look up the right pair of figures, so I can devide one by the other to get the rent-increase-factor.


So far the table with the INDEXATION-FIGURES is unrelated, but I did a web-search on this and conclude that is not the way to go. I thought, there is no relationship... ...or actually, the truth is, there is a relationship from every YEAR-record to every INDEXATION-FIGURES-record. For any YEAR, any INDEXATION should be "lookuppable".


I also found out that many-to-many relationships need to have an INTERMEDIATE table, with linking records.


My questions:

  • Is this the right way to go?
  • And am I right that for every new YEAR-record I start, I will have to run a script that populates 600 related records in the INTERMEDIATE table with their F-ID's pointing at all these INDEXATION-FIGURES?


Regarding the last one, I don't think so.


Is any one available to get me going? Thanks in advance.

Regards, Thomas.