Count Function

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.
 
G

Guest

How are your tables related to each other. It sound like you have one join as
let's say a left join and the other as an inner join. Or simply stated one
with an arrow and one not. The joins should be either both inner or both left
or right joins.

hth
 
G

Guest

I have all the other tables looking at TableA and the primary key. I'm still
lost. Am I linking the tables wrong?
 
G

Guest

Not sure what you are trying to do, if you just want to display the count in
each table using one query then you can use union query

SELECT Count(*) As CountOfRecordTable From TableA
UNION ALL
SELECT Count(*) As CountOfRecordTable From TableB
UNION ALL
SELECT Count(*) As CountOfRecordTable From TableC

If you join the tables the query will return the count of records that the
query return, and not a different resault for each table
 
G

Guest

I tried that but Access won't let me put in the SQL. I keep getting the same
error message.
 
G

Guest

Can you post the SQL?

Also, if the count is on a query and not on the table, check the query (not
the UNION) mybe it return that error.
 
G

Guest

SELECT count(*)
from lam_man
union all
select count(*)
from panel_ledge
union all
select count(*)
from panel_back;


In Design view, I can't do queries with more than 2 tables. I'm thinking my
relationships are wrong or something.


I have 12 tables. All of them look at TableA and the primary key. They are
one-to-one relationship. On the join type option I chose the 2nd one so my
main table (TableA) would show all records and only records from the other
table where the joined fields are equal.

I'm totally lost.
 
J

John W. Vinson

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.

Please post the actual SQL view of your query. I suspect your table structure
IS WRONG - one to one relationships are *quite* uncommon, and having twelve
tables related one to one sounds like a problem crying out for a different
solution! What are these tables, and why the peculiar relationship?

John W. Vinson [MVP]
 
G

Guest

I cant get the actual SQL that Access give me because of that error that I
posted in previous posts. The way the tables are set up is that the user
"takes steps through the tables" For example, they would have to enter data
in TableA, the primary key(panel_num) would then be used for the rest of the
11 tables. The other tables serve as different "jobs" to make a product. So
the user would start at TableA and work its way down B,C,D,etc. I have no
idea how to set up the relationships because each table uses the same primary
key.

I can do basic queries to get results, but I can't get simple functions
(count) to work. I get the wrong numbers. So I changed the join type to the
second option so my main table (TableA) would show all records and only
records from the other
table where the joined fields are equal. This works but just for 2 tables.
Everytime I try and do something with more than 2 tables I get that error.
 
M

Michel Walsh

You can also try:

SELECT COUNT(*) AS firstCount,
(SELECT COUNT(*) FROM table2) AS secondCount,
(SELECT COUNT(*) FROM table3) AS thirdCount
FROM table1


And you will get all the counts in one record...



Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top