Crystal Reports pulling blank reports

[ribbons]

I am using CR XI and utilizing an Access database with linked tables. The report has a parameter field that allows me to select records to show, specifically reports based on individual lab numbers. Each record and all accompanying information will be represented on one report.

The report will pull some of the records in this database and not others. I found online a line of sample code to test if the report was functioning properly and it was. I have looked at the raw data and can find nothing obvious that might cause this. The finished report contains some subreports, but I have removed them and still the report won't show information from just the primary table of some database records. I have exhausted my ideas and hope someone has had this same experience.

Ribbons

 

[Turkbear]

Hi,
Just so I am clear on this..

If you remove all the linked tables and just have the main one, you still get no records?

If any of the linked tables have no matching records for the query, no record ( even from the main table) will be returned unless you are using Left Outer Joins.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ribbons]

Hi Turkbear,

I have three tables: Labnumber, Purity and Germination

You're right, they're not joined with Left Outer Joins, they're joined with inner joins. But, if I DO use a left outer join between labnumber(main table) and Purity (all the records from labnumber and ONLY those records from Purity where the field: labnumber matches, I still get no records. I changed this inside Access in the relationships between tables. It does come over to CR in that manner, right?

 

[Turkbear]

Hi,
I do not believe CR will retain the links defined in Access unless you are using a view in Access that has them defined..

Use CR's Database expert to create/check the links..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ribbons]

I inherited this database, but the creator has links defined in Access, Inner joins mostly. I can see them in the Relationships window. If I create a new form in Crystal, the links that appear in Database Expert seem to be coming over from Access or else CR defines them as best it can as the tables are added. If I go into Database Expert and define the joins between the three tables I mentioned, the join does not hold if I close DB Expert and then open it back up. I'm at a loss where the joins in CR are coming from and how to change it. There's nothing in any of my books on CR, so if anyone has any ideas, they would be welcome.

 

[Madawc]

Since it's not your report, check out the following:

a) Report Selection Formulas > Group

b) Section Expert Suppression. (Right-click on the detail section and choose Format Section to see these options).

You could also put a summary count on a field, to see if records are not being selected or just not being shown

Also try Database > Show SQL Query, if that will work with your set-up.

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

 

[Turkbear]

Hi,
In the Database expert, click on each link ( the blue line) and set it to Left-Outer

It is possible that your connection to Access does not allow Left-outer or any join other than Inner..In which case you will have a problem with those missing records..

Does an Access report correctly return all the expected records..If so, try creating a view in Access ( I think it supports those, not sure) and using that in CR..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ribbons]

Thanks, all. I will try all of your suggestions and let you know the resolution.

Ribbons

 

[ribbons]

I can create a report in Access using left joins and successfully pull all the information I need. I'm not sure I know what you mean by "view" in Access. Do you know how to get Access to allow me to create left joins in CR? Should I repost this question in another thread?

 

[Turkbear]

Hi,
View was a bad choice of words ( in Oracle that is what I would use) - In Access, I believe, you create a Query and use that in CR as the DataSource..

I am not sure you can get CR to do Left-Outers when using Access, it is probably a limitation of the driver used to connect...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ribbons]

Thanks, turkbear. I'll try using the query to pull the records.

Ribbons