Report Only Shows Records Where Fields Are Populated

[Mav3000]

Hi, I have a report with three tables, all linked.

tblSite > tblOption > tblNotes

One site can have many options, and many options can have many notes.

I want to list the Options, and have a column showing the latest Note for each Option. I do this by putting in the Detail row the Option fields, and the 'Note' field.

However, when I run the report, Crystal only shows Options which have a Note. If the Option does not have a Note, then the option is not shown on the report.

How can I get Crystal to show all Options, regardless of whether they have Notes or not?

Many thanks for help on this one...

Mav3000

 

[IanWaterman]

YOU have to change your join from tblOption > tblNotes

Currently you will have an equal join you need to make it a left outer.

Ian

 

[Mav3000]

Hi Ian,

Thanks for the reply. Changing the link between tblOptions and tblNotes now shows all records, but brings about another problem.

Where I have a tblOption with 3 records in tblNotes, Crystal is now showing 3 rows, all for the same option. One row for each Note.

I only want to show one row for each Option, and the latest Note.

Is there a way to supress duplicate Options somehow?

Mav3000

 

[Mav3000]

Just to confirm, I have three tables:

tblSites, tblOptions and tblNotes

The link between tblOptions and tblNotes is now an outer-left join, with 'Not Enforced' enforcement, and an '=' link type.

I want to list each record in tblOptions at Detail Level, with the latest record from tblNotes in a column at this level.

Currently, if more than one record in tblNotes exists, the Option is listed multiple times.

How can I restrict this to just one row?

Thanks, Mav3000

 

[IanWaterman]

Group your data by the relevent field in your tblOptions.

Move all the data you want to see from details into this new group footer.

Sort you data so that the notes are inorder you want.

This will ensure that only the last record and note is now displayed.

CAUTION
You can not now use ordinary summaries as actual data is still duplicated. If you want any totals or count you must now use a Running Total and only evaluate on change of this new group.

Ian

 

[Mav3000]

Thanks Ian - I'll give this a go tomorrow morning and look forward to the results.

I''ll create a new group, put the data in the group header, and supress the details section completely. Sounds logical now I think about it!

Cheers, Mav3000