Crystal Reports XI and access

[Former Member]

I have an access database that is used for residential inspections. The main table is the situs information while a linked subtable is the inspection information. The subtable is linked via situs ID. My problem is that when trying to create a report that list each address and all inspection information associated with that address only one inspection will print. The inspection fields are v-ditch, slope, manuer, date, insp by, and letter sent. The record will only print in crystal if ALL feilds from the inspection table have data. There are many instances where there is only a letter sent so the v-ditch and slope feilds are left null. How can I get all inspections to print for each address.

Thanks

Derek

[former_member230846 · Accepted solution]

Hi Derek,

In Crystal Reports, try going to Database|Database Expert. Select the Linking tab. If the two tables are only linked on one field, SitusID, I would suggest right-clicking on the link and change the linking to be Left Outer Join.

You may need to test with other joins such as Right Outer if the first one doesn't meet your needs.

Regards,

Wallie

[Former Member · Accepted solution]

Thank you for your reply Wallie, I tried both the outer join and inner join and neither changed anything. More info might be required by me.

Have 7 tables in this database as follows:

tblInspBy

tblInspSlope

tblInspVDitch

tblResInsp

tblResInspLetter

tblResInspManure

tblResSitus

As I said the tblResInsp and the tblResSitus are linked by situs ID. All the other tables are linked to the tblResInsp. So basically when the user has to enter an inspection they open the form, enter the situs information (or lookup situs info if the site has been inspected previous), and click to open a new inspection form. Each address can and do have multipal inspections performed. What is happeneing is that when i try to run a report that includes all the inspections for each address, only the addresses that have all the fields populated are printing. So if I enter an inspection that says the vditch is dirty and leave everything else blank this inspection will not print on my report.

When creating the report if the only field I have printing is the v-ditch field, all inspections with the v-ditch field populated will print, if I add the slope field, ONLY the fields with slope AND vditch populated will print.

Hopefully this extra info helps as my head is about to explode!

Derek

Edited by: Derek Williamson on Sep 23, 2008 4:55 PM

[Former Member · Accepted solution]

THANK YOU WALLIE!

This problem is actually solved. The join from the situs to the inspections was not the problem, it was the join from the inspections to the various inspection options. Made them all left outer join and all is well.

Thanks for the point in the right driection.

Derek