accessing a series of parameters/rates

[Aussie John]

Im struggling with this. I have a series of about 50 parameters which are used as a multiplier to a field in another table. A simplified example would be I have a number of persons in a "Staff Table". Some staff get an office and some get an open plan workspace. Each staff member would have a designation type to determine office or workstation. The "parameter" table would show 12  sq m for an office and 6 sq m for a workstation.

Basically the number of staff * the area rate is required (in each category).

My problem is I believe I have too many parameters to have a single record and the alternative is to have 50 records each with a small number of fields but how to get the sq m rate is alluding me (apart from a plethora of relationships). Maybe I need to break down the parameter into separate table categories to reduce the number of fields but it is still messy.

I would like the solution to be live.

Any ideas would be appreciated.

John

Edited July 18, 2022 by Aussie John

[comment]

23 minutes ago, Aussie John said:

Basically the number of staff * the area rate is required (in each category).

If you want the total area by category, then do the calculation in the Categories table (the one you call the "parameter" table), and make it =

Area * Count ( Staff::WorkpaceType )

Then every record in Categories will display the subtotal area for its category - and if you want, you can add a summary field to total the results.

Note that this method summarizes all related records in each category. If you wanted to summarize only the found set in Staff, you would need an unstored calculation in the Staff table to "copy" the area from the related record in Categories and then a summary field to sub-summarize the sorted groups. Or, if you prefer, you could use a summary field to count the records first and then a calculation to do =

GetSummary ( sCount ; WorkpaceType ) * Categories::Area