Problem with linking tables 1

[DTJeff]

Hi,

I’m currently trying to develop a report using the version of crystal that comes with .Net 2003 (crystal 9?) and I am having some major problems getting the data displayed.

I basically have three tables, one which holds details on Assets (e.g. a monitor) and there locations, one which holds the location details, and one which holds the Assets History (i.e. where it used to be).

I’m trying to make report which shows the assets that have moved, which I have succeeded in doing, but I cant get it to show the location details for the history. I have tried linking the tables using the visual linking, and have also tried linking to an alias of the locations table, but both of these just result in half of the data missing.

I’m not sure what info you would need to better explain this, so if anyone can help please let me know.

Thanks.

 

[kmcginn]

I would try using a subreport. Usually when I have trouble with a link, I can get the desired results by creating a subreport and then linking it to the main report.

Good Luck!

 

[Turkbear]

Hi,
I have tried linking the tables using the visual linking, and have also tried linking to an alias of the locations table, but both of these just result in half of the data missing

By Half the data is missing do you mean that some locations have no history and, for those, no data is returned, even about 'current' location?

If so,try changing to Left Outer Links from Asset ( and/or Location) to History to see if that helps..

 

[DTJeff]

Hi,

OK, I think I've made a little progress.

I can now get 99% of the report to work.

The one bit I still cant get to work, is to display the location details (from tblLocations) for the location reported in tblAssetHistory.

 

[GJParker]

Insert a 2nd (Alias) tblLocations and link thi swith a Left outer Join to tblAssetHistory

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[DTJeff]

Just tried this and I get the following results.

With arrow going from tblAssetHistory to tblLocations_1 (alias) I get a "Query engine error" when I run the report.

If I reverse the link, the report runs but doesnt have any data in it.

Any other ideas?

 

[GJParker]

Make sure that the link beteween assts and asste histor is also a Left outer Join

Asset - LOJ - AssetHistory - LOJ - Loctions
LOJ
Locations


HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[DTJeff]

Just thought I had better say what the problem was in the end since I've now fixed it.

It appears to be a bug in crystal.

I found that if I created a "virtual table" (create a command that just returns the table), and then used this for the second reference it works fine!

Cheers for all the help people gave.

Jeff.