Reversing Links Ignoring Data

[basil3legs]

I have 2 Foxpro (DOS) tables, one has Part Details, the other has Invoices which include the Part Numbers to link to the other table. If I link from the Invoice table to the Part table this is a one-to-one relationship and I can group by part to give the total of each part sold.
However, this will only show parts which have been sold and I need to list all parts. If I reverse the link to create a one-to-many relationship from the parts table to the invoices, I get a list of all the parts but the totals sold are incorrect. It appears that Crystal Reports 8.5 is only seeing half of the records this way round.

Does anyone have any suggestions how to overcome this?

 

[synapsevampire]

A Left Outer Join should be fine, if you're using a direct connection, this may not be an option.

Perhaps if you change to an ODBC connection you can request a LO join?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[basil3legs]

synapsevampire, thanks for the reply.

I do not have an option to Left Outer Join as I am using a direct network UNC connection. I have tried setting up ODBC links in the past to the databases but as they are not on the report server, I have never got them to work when the report is run through Crystal Enterprise 8, only when run locally through Crystal Reports (If you can suggest how to set this up, any help is greatly appreciated). It appears that, although Crystal say they support Foxpro databases in Reports, it is very lacking, (e.g. you can't see memo fields at all unless you use ODBC) and this is probably the cause of the problem.

Alo, I can't put the databases on the reports server as they are our main operational databases and are currently running on a Novell server and I really wouldn't want them on a server that is accessable over the internet however well it appears to be locked down!!