cannot get data from two unlinked tables using odbc - Help! 1

[Amjid]

I have been using crystal reports and and access database for a while and have recently rewritten my
software to use ODBCDirect. I have tried creating a report in crystal reports. I take data from two
tables in this database. I have logged onto the server correctly and add the first table when I create
the report. I then add the second table. I can browse the fields in both tables but when I preview
the report only data from the first table is visible. If I try to look at the SQL query being used
by crystal reports then details of the second table are missing from the query. If I try to manually
amend the query when I close and reopen the query my changes have gone again!!
I have resorted to creating a stored query in my database and basing the report on this query rather
than tables (this seems to work) but I'm sure I'm doing some thing wrong - any ideas?
I'm using VB5 and the version of crystal reports that came with VB5 (4.6).
Amjid Riaz

 

[balves]

You cannot manually make changes to the list of fields in the SELECT. Crystal will revert to the original query when it closes the window (a long time feature).

Are you sure that you are placing fields from the second table in the report? Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[kenhamady]

Why are the tables unlinked? Crystal won't know how to make a resultset from both without a linke, and will ignore the second one. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[balves]

You can perform a FULL OUTER JOIN if that's what you really want to do (perhaps not - it doesn't come up too often).

Link the 2 tables by a common type field (Like integer to integer, or char to char). Put a field from each table on the report. Running the report after this join will get you no records.

Choose the "Show SQL Query" menu choice and edit the WHERE clause, changing "INNER JOIN" to "FULL OUTER JOIN". What you will get is:

Table 1 Field Table 2 Field
------------- -------------
record 1 data NULL
record 2 data NULL
...
NULL record 1 data
NULL record 2 data
... Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[kenhamady]

I didn't know that. I have now tested that in SQL Server. Does it also work in Oracle? It doesn't seem to work in MS Access. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[MalcolmW]

The ODBC driver for Access doesn't support full outer joins. You can do a left outer join instead, and then union it to the same SQL statement that has a right outer join. But, as Brian says, this doesn't come up that often... Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net