ODBC & Access Linking Issue

[Bunting]

I have an Access table I wish to link to an ODBC database in Crystal 11. For the project I'm working on I need to be able to use a full outer join but this option is not available. Is it in any way possible to alter my tables or report to allow the full outer join?

 

[IdoMillet]

You can achieve the same thing by using a UNION in the SQL of a Command as the data source. Better yet, why not link to the ODBC DBMS from Access? You can then create a query in Access and use that query in Crystal.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I agree with Ido's second solution, select New while in the Table tab in Access and select LINK, then choose ODBC datasources as the file type and point to you ODBC datasource.

Now you can use the linked table in Access to build out a query (which will be faster than Crystal), and expose the query as the datasource for the Crystal Report.

-k

 

[Bunting]

Many thanks to you both, the link/query solution seems to be working exactly as required.

 

[Bunting]

I may have jumped the gun a little there, I have linked into my ODBC database and created an Access query to bring back a new table from it. However neither Crystal nor Access will allow me to do a full outer join between my original table and this new table. Any ideas why this might be?

 

[Turkbear]

Hi,
Not all database/ODBC combinations allow for Left Outer ( or anything other that Equi) Joins - Particularly between disparate database types..

Try, as a test, actually importing the ODBC based database into Access ( instead of just linking) and try the LO Join between those 2 ( now Access ) tables.








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

 

[Bunting]

Hi,

I made it that far - my problem is that I want to do a full outer join, as the action I will take on entries varies dependent on whether it appears only in table A, only in table B, or both.

Access doesn't appear to offer a full outer join at all, and not being at all proficient in SQL I have no idea whether I can force Access to do this join anyway.

I'm not sure whether this will affect your response but I'll mention now my linking is on two of the fields in each table (though even linking on one I can't make a full outer join).

Thank you for all of the help.

 

[synapsevampire]

Give an example of what you need.

Don't bother importing the table, use a Link so that it works in the future.

Anyway, right click the join line between the two tables in Access and select Properties, then select All rows from A and All rows from B.

You might also use a Union Query.

You can overide the SQL in Access by manually typing in the SQL, which is the case with UNIONS.

-k

 

[Bunting]

That's really the crux of the problem - there is no option to select all rows from A and all rows from B. Should this option be available? If so I can enquire with IT to see what can be done.

As an example of what I need, each of table A and table B is a list of accounts, with account and customer identifiers each stored as text. Neither of these are unique within either of the tables. Linking on these I need to have all of the data for all accounts in both tables with blanks where data from either table A or table B is not available.

I think I may end up using Crystal to create a report with a left outer join, then add a subreport with a right outer join selecting out matching records to avoid duplicates. If you have a better idea I'd be very grateful.

-C