Counting Child Records

BrianWren · May 30th, 2008, 02:47 PM

I have (in MS Access):

Code:

[u]HULL table</u>
HULL HULT      HULLKEY(PK)
123  'First'     1
345  'Second'    2


[u]PROJ table</u>
PROJ      HULLKEY(FK)  PROJKEY(PK)
'Proj1'    1             10   
'Proj2'    1             11
'Proj3'    2             20
'Proj4'    2             21
'Proj5'    2             22

I want output like:

Code:

HULL HULT      HULLKEY  COUNT(PROJ_RECs_FOR_THIS_HULL)
123  'First'     1         2
345  'Second'    2         3

How can this be done?

I was unsuccessful with

Code:

SELECT [HULL].[HULL],
       [HULL].[HULT],
       [HULL].[HULLKEY],
       [THEPROJ].[PROJCOUNT]
FROM   HULL,
       (SELECT COUNT(PROJ.PROJ) AS PROJCOUNT 
        FROM   PROJ 
        WHERE  PROJ.HULLKEY = HULL.HULLKEY
       ) AS THEPROJ;

I am prompted for HULLKEY (the bold one, above). The number I enter causes the count for that HULLKEY to be in each rowâitâs not dynamic.

BrianWren · June 2nd, 2008, 04:32 PM

Code:

SELECT HULL.HULL,
        HULL.HULT,
        HULL.HULLKEY,
        (SELECT COUNT(PROJ.PROJ) 
         FROM   PROJ 
         WHERE  PROJ.HULLKEY = HULL.HULLKEY
        )
FROM   HULL;

This will fail though if the subquery returns more than one record per HULL.HULLKEY. (Count() never returns more than one record, of course.)