Hi,
I'm a lowly electrician trying to make my life a bit easier.  I have an 
access 2003 database that contains details of electrical installations.  Each 
Installation has a number of Distribution Boards, each containing circuits.  
I am trying to implement a query that will tell me how many circuits are in 
each installation.  Trouble is, I can only get the Count function to work on 
a single table, not on a joined query.

Could somebody please help?

The tables are implemented like this:

InstallationTable
InstallationID
InstallationName
others...


BoardTable
BoardID
InstallationID
others...


CircuitTable
CircuitID
BoardID
others...

What I am trying to achieve is a query that will give simply

InstallationName   NoOfCircuits

Could anybody please help save me from imprinting my forehead further into 
the kitchen wall?

Regards,
Mark  

Mark:

Join the three tables, group by the InstallationName column and count the 
rows like so:

SELECT InstallationName, COUNT(*) AS NoOfCircuits
FROM CircuitTable INNER JOIN 
(InstallationTable INNER JOIN BoardTable
ON InstallationTable.InstallationID = BoardTable.InstallationID) 
ON CircuitTable.BoardID = BoardTable.BoardID
GROUP BY InstallationName;

If you do it in query design view it doesn’t allow you to count on the 
asterisk (which is how its normally done in SQL when counting all returned 
rows per group), so count on the CircuitID column instead.  The result will 
be the same.

Ken Sheridan
Stafford, England

"Mark E" wrote:

> Hi,
> I'm a lowly electrician trying to make my life a bit easier.  I have an 
> access 2003 database that contains details of electrical installations.  Each 
> Installation has a number of Distribution Boards, each containing circuits.  
> I am trying to implement a query that will tell me how many circuits are in 
> each installation.  Trouble is, I can only get the Count function to work on 
> a single table, not on a joined query.
> 
> Could somebody please help?
> 
> The tables are implemented like this:
> 
> InstallationTable
> InstallationID
> InstallationName
> others...
> 
> 
> BoardTable
> BoardID
> InstallationID
> others...
> 
> 
> CircuitTable
> CircuitID
> BoardID
> others...
> 
> What I am trying to achieve is a query that will give simply
> 
> InstallationName   NoOfCircuits
> 
> Could anybody please help save me from imprinting my forehead further into 
> the kitchen wall?
> 
> Regards,
> Mark  
> 
> 
> 

Thankyou - that worked a treat!!!

"Ken Sheridan" wrote:

> Mark:
> 
> Join the three tables, group by the InstallationName column and count the 
> rows like so:
> 
> SELECT InstallationName, COUNT(*) AS NoOfCircuits
> FROM CircuitTable INNER JOIN 
> (InstallationTable INNER JOIN BoardTable
> ON InstallationTable.InstallationID = BoardTable.InstallationID) 
> ON CircuitTable.BoardID = BoardTable.BoardID
> GROUP BY InstallationName;
> 
> If you do it in query design view it doesn’t allow you to count on the 
> asterisk (which is how its normally done in SQL when counting all returned 
> rows per group), so count on the CircuitID column instead.  The result will 
> be the same.
> 
> Ken Sheridan
> Stafford, England
> 
> "Mark E" wrote:
> 
> > Hi,
> > I'm a lowly electrician trying to make my life a bit easier.  I have an 
> > access 2003 database that contains details of electrical installations.  Each 
> > Installation has a number of Distribution Boards, each containing circuits.  
> > I am trying to implement a query that will tell me how many circuits are in 
> > each installation.  Trouble is, I can only get the Count function to work on 
> > a single table, not on a joined query.
> > 
> > Could somebody please help?
> > 
> > The tables are implemented like this:
> > 
> > InstallationTable
> > InstallationID
> > InstallationName
> > others...
> > 
> > 
> > BoardTable
> > BoardID
> > InstallationID
> > others...
> > 
> > 
> > CircuitTable
> > CircuitID
> > BoardID
> > others...
> > 
> > What I am trying to achieve is a query that will give simply
> > 
> > InstallationName   NoOfCircuits
> > 
> > Could anybody please help save me from imprinting my forehead further into 
> > the kitchen wall?
> > 
> > Regards,
> > Mark  
> > 
> > 
> > 
>