A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking Problem



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2007, 06:28 PM posted to microsoft.public.access.tablesdbdesign
Chinny03
external usenet poster
 
Posts: 23
Default Linking 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. 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.


  #2  
Old June 11th, 2007, 07:47 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Linking Problem

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?
--
KARL DEWEY
Build a little - Test a little


"Chinny03" wrote:

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.


  #3  
Old June 12th, 2007, 09:33 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins[_2_]
external usenet poster
 
Posts: 118
Default Linking Problem

On Jun 11, 6:28 pm, Chinny03
wrote:
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 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(adSchemaStati stics)
rs.Filter = "TABLE_NAME = 'TableA' OR TABLE_NAME = 'TableB' OR
TABLE_NAME = 'TableC'"
? rs.GetString
TableA 1
TableB 3
TableC 0

Jamie.

--


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.