One-to-many logic problem...

February 9, 200917 yr

Hello,

I'm stuck and need some pointers please.

Here's an outline...

I have two tables.

Table 1 details Tax rules and percentages, by country.

Table 2 is a series of records detailing income and on which some calcs are performed on the basis of table 1 info.

The tables are related by a short-form selectable country-code; table 1 country-code is defined as unique, so the relationship is one-to-many.

[Table 1 code = data specific to this code]

[Table 2 = includes multiple records based on occasionally the same codes, utilising the same rules.]

Two problems - at least...

1) I need to store the country-code specific formulae in text form [table 1] so I can see how the rule is contructed. [relatively easy]

2) and I want the (table 2) calc to reside here [table 1] as well [very difficult!]

Why?

Firstly, my table 2 calcs are getting extremely complicated so I'd prefer to locate them off-table for easier management.

Difficult? So far I can perform a look-up (from table 2) which gets the correct value in record 1, table 2, but not record 2, where the same record 1 answer appears when the country-code is the same...

I suspect that placing the table 2 calc in table 1 makes the calc happen once and the look-up only get's the first value...

My ideal solution is to somehow convert my text based table 1 calc into a useable calc in table 2...

It's possible that my logic is shot - and it's equally possible that I've explained myself badly. Sorry!

All help appreciated!

MU

February 9, 200917 yr

You could keep the FORMULA in the countries table, and have the calculation field in the transactions table evaluate it. It will be rather slow, though.