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

here ya go --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