Linking Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with 12 tables. I want to do a simple query where I would
get the count of three different tables.

Count(TableA)
Count(TableB)
Count(TableC)

All three tables have the same primary key. The query fuctions when I only
deal with 2 tables. But when I deal with more than that I get this error:

The SQL statement could not be executed becuase it contains ambiguous outer
joins. To force one of the joins to performed first, create a separate query
that performs the first join and then include that query in your SQL
statement.

Any idea what I'm doing wrong. Thanks.
 
Can not know based on the information provided.

Why did you not post the SQL statement as you knew from the error message it
had a problem?
 
I have a database with 12 tables. I want to do a simple query where I would
get the count of three different tables.

Count(TableA)
Count(TableB)
Count(TableC)

All three tables have the same primary key.
Click to expand...

The hard way (a.k.a. roll your own):

SELECT COUNT(ID) AS CARDINALITY, 'TableA' AS TABLE_NAME
FROM TableA
UNION ALL
SELECT COUNT(ID), 'TableB'
FROM TableB
UNION ALL
SELECT COUNT(ID), 'TableC'
FROM TableC;

An easier way: use the SCHEMA_CATALOG (a.k.a don't reinvent the
wheel):

Set rs = CurrentProject.Connection.OpenSchema(adSchemaStatistics)
rs.Filter = "TABLE_NAME = 'TableA' OR TABLE_NAME = 'TableB' OR
TABLE_NAME = 'TableC'"
? rs.GetString
TableA 1
TableB 3
TableC 0

Jamie.

--
 
Back
Top