Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unanswered: INNER JOINs and COUNT

    Hi people,

    I have two Access tables, Project and Members. The primary key for both tables is ProjectNumber.

    The Project Table looks like this:

    ProjectName ProjectNumber ProjectInfo
    ..Smith...............22222..........Text Text
    ..Smith...............55555..........Lorem Ipsum
    ..Bradley............77777..........Notes


    The Members Table looks like this:

    ProjectNumber FirstName LastName DateJoined
    ..22222..............David.......Smithy......Feb
    ..22222..............Martin.......Smith.......Feb
    ..55555..............Katy.........Smith.......Feb
    ..77777..............Tony........Bradley.....Feb

    You can see that for the Smith project with the Project Number of 22222, there are 2 members.
    The Smith project with the Project Number of 55555 has only 1 member etc.

    If I search solely on the name 'Smith' via a web-form and ASP, I am trying to return a recordset that has the columns (which hasn't formatted quite right!!)

    ProjectNumber ProjectName No.ofMembers ProjectInfo
    ....22222.............Smith..............2........ .Text Text
    ....55555.............Smith..............1........ ..Lorem Ipsum

    It is the third column, No.ofMembers I am having trouble with. I'm not totally sure it can be done without searching on the actual ProjectNumber, but I have been attempting to use an INNER JOIN to resolve the issue.

    SELECT Project.GroupName, Members.ProjectNumber, Project.ProjectInfo
    FROM Project INNER JOIN Members
    ON Front.ProjectNumber = Members.ProjectNumber
    WHERE Project.GroupName = 'Smith'


    which gets me halfway there - I think I should have a COUNT function in there somewhere to get the No.ofMembers for each separate Project, but I can't fathom it. Or is there a better way around it?

    Thanks,
    Alski
    Last edited by alski; 02-02-03 at 16:44.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here ya go --
    Code:
    select ProjectNumber
         , ProjectName 
         , count(*) as NumberOfMembers
         , ProjectInfo
      from Project 
    inner
      join Members 
        on Project.ProjectNumber 
         = Members.ProjectNumber
     where Project.ProjectName = 'Smith' 
    group
        by ProjectNumber
         , ProjectName 
         , ProjectInfo
    rudy

    p.s. the primary key of the Members table is not ProjectNumber, it is probably FirstName plus LastName

    ProjectNumber in the Members table is a foreign key that links each row of Members to the related primary key in the Project table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •