Mike,
If you can redesign of the system so that all
daily tables are stored in one table with an added date
column, problem is solved.
If however, this is not an option, then create an Insert trigger
on the catalog table so that whenever a new daily table is created
you append the records of the new daily table to a physical COMBO
table or generate the necessary DDL to Drop & Recreate a UNION View
that establishes a virtual version of the physical COMBO table.
For example:
Select column1, 2, 3, Table_Name as 'Date' from Table1
UNION
Select column1, 2, 3, Table_Name as 'Date' from Table2
UNION
Select column1, 2, 3, Table_Name as 'Date' from Table3
etc...
As you can see from the example above, the physical or
virtual (View) table should have an added column
that is the name of the table being appended or UNIONed.
Now, in Crystal you run against this Table or View and you can
add a selection criterion that limits the rows to only those where
the 'date' column matches the daily date parameter.
You can then also generate other reports that constrain data to
a range of dates using the same approach.
hth,
- Ido
