Ive produced a report using three t

[cantona]

Ive produced a report using three tables;

People
Base
Passes

The report is designed to show passes which have been issued to people at a particular date. I also need to show on the report the base the person was at at the time the pass was issued. I have a problem in that each person may have more than one base, therefore they appear on the report several times. The passes table is not related to the base table so; for example a person that has two passes issued to them and three bases recorded against them in the bases table will come out on the report 6 times!! when it should only be two! One for each pass, with the base name along side it.

I need to write a formula so that the report only displays in the details section the base the person was registered at when a pass was issued to them. (The bases table has start and end date fields)Can anyone point me in the right direction?

 

[IdoMillet]

Assuming the PASS table has a date field, add a record selection formula of:
--------------------------------
{PASS.Date} >= {Base.Start_Date}
AND
( IsNull({Base.Start_Date}) OR
({PASS.Date} <= {Base.End_Date} )
--------------------------------

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com