Why can't I join these two tables??

[Steve in Atlanta]

I have two link tables that I am trying to join based on the one and only column they have in common. Whenever I try to join these two tables and try to 'count distinct' I get the following error:

[IBM][CLI Driver][DB2] SQL0111N The column function "" does not include a column name. SQLSTATE = 42901 (#-111)

Both of the tables are linked to DB2 tables.

Does anyone know what might be causing this and how to fix it??

 

[SydneyGeek]

Are you trying to join them using the Relationships interface, or just in a query design? As far as I know you can't link external tables in the Relationships view -- Access can't enforce referential integrity under those conditions. I think you can link them in a query, though. I have definitely linked external tables to internal tables in this way but maybe you can't do it wit 2 external tables. If so, try this:
1. Create a Union query to get a unique list of ID's from the 2 external tables.
2. Use a Make-table query to create an internal table with the ID's.
3. Use this as a join table to link the others.

Unless the data changes rapidly, you may be able to get by with periodic refreshes of the link table. Note: DON'T create reationships in the Relationships window if you will be trashing and rebuilding the link table. Just create the joins in query design view.

Hope that helps

Denis