Selecting unique records from a join query

M

Mark E

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
 
K

Ken Sheridan

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
 
M

Mark E

Thankyou - that worked a treat!!!

Ken Sheridan said:
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
 

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