Hi all,

I am after your advice on the best way to go about something.

The background to it all...

Users have entered data into 6 tables
Tables are: DataHeadings, Year1, Year2, Year3, Year4 and Year5

Each table has 30 fields in which they entered data

DataHeadings is where they give the 30 headings of what data they are
entering into tables Year1 - Year5

Field names for the table Year1 are Yr1C1, Yr1C2... Yr1C30 (Year 1 column x)
This is the same for all years/tables to Year5
So the headings they gave for DataHeadings will match with the data they
provide in tables Year1 - Year5.

Hope that all makes sense so far

Now in another table they select what data they want on a report, they have
9 selections to make

Table = ReportHeadings
Fields = Column1, Column2, Column3... Column9
These fields are drop down lists, the list show the 30 headings they gave in
the DataHeadings table, from this list of 30 headings, they select 9 they
want to show on the report.

So we now have 5 years worth of data and we know the columns they want to
show on the report

The tables are joined by a field named ProductID

Now, the question

I have another 5 tables

ReportYr1, ReportYr2... ReportYr5

Each of these tables have 9 fields
ReportYrC1, ReportYr1C2...ReportYr1C3

These 5 tables need to populate with the data based on what the user has
given so far
So we know the 30 pieces of data they have given for 5 years, we know the
headings of that data, and we know which of those heading they want to see
on the report

What is the best way to populate the 5 tables for the report.
I could do a very large IF calculation for each of the 9 fields in each of
the 5 tables checking what they want on the report, then matching this up
with the years data etc

But I am sure I must be able to do this as a relationship, which will be
quicker to both setup and quicker in the database and also a lot neater.

Any ideas what relationship I need to create here to get these fields to
populate with the correct data? Or am I best going with the calcs.

Thanks
Ed



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/