Selecting tables and related Record Selection based on Date?

[jaybar48]

Hi

We have a very large table of patient visits to Hospitals (40 million records) covering 2005 through 2008. In order to improve on how long it takes the reports to run through all this data, we are thinking of segmenting the data into four separate tables (one for each year), since we generally look at data for one year at a time.

If we do this, is there any way of selecting the table "on the fly" so to speak, based on the date range and have corresponding record selection statements? We would like to have to avoid re-writing the report every time we need to run it for a different year.

The tabels will eb called
2005_visits
2006_visits
2007_visits
2--8_visits

Thanks in advance.

Jay

 

[pvidya]

Hi

Not sure about doing this in Crystal, but you could effect this by using a stored procedure as the basis for this report.

Thanks.

 

[Madawc]

You could do a dummy main report and four subreports, each accessing a different year and in a different section. Use a parameter to suppress the unwanted sections.

Write one of the subreports first, as a stand-along. Then keep importing it into the main report and then modifying it.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you have version 9 or above, I think you could potentially set up the table name as a parameter within a command (to use as the entire datasource for your report), and set up the command like this:

select {?tablename}.`field1`, {?tablename}.`field2`
from {?tablename}
where
//etc.

-LB