Hi all,

I've been struggling with a SQL problem for over a week now. I am using Crystal Reports V8.5. My experience level is 'programmer', and this would be trivial to do in my programming language, but then I don't get the benefits of using reporting tools like Crystal, etc.

I have 3 tables - for simplicity I'll call them Index, Facility, Detail.

Index has 2 fields - {date}, and {pointer to Detail primary key}.
Facility has 2 fields - {Hospital}, and {Clinic}.
Detail has {primary key}, {date}, {clinic}, and {other misc fields}

Selecting by using a date range against the index will yield the least number of detail records.

The end result should be a query that asks the user for a date range, and a multiple selection of hospitals. All clinics associated with the selected hospitals should be included in the output (Each hospital may have several clinics).

Would it be best to join the detail table with the index table first, then use a 'while clinic in (parameter query)' syntax, or try to do the clinic parameter query first, then try to join to the detail and index tables?

I've been successful in doing each part separately, but don't know how to combine them. Any help would be appreciated.

Thank you,
Jere Neal