Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting tables and related Record Selection based on Date?

Status
Not open for further replies.

jaybar48

Technical User
Feb 16, 2002
61
US
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

 
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.
 
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.



[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top