Using Count or DCount in a query

[Guest]

I have several tables in a database, tracking membership signups by
generation. Because it is important for us to track that each member gets 2
and only 2 sign-ups underneath them in a particular affialiate program, I
designed the tables by generations of sign-ups, starting with a table labeled
as FrontLine, and then 1stLevel, 2ndLevel, etc. I link the tables by using a
one-to-many link in Front line from MemberId to MemberJoinedUnderId in 1st
level, and so on and so forth. I now need to perform a query that displays
each member that does not have two members signed underneath them. I was
trying to do this using either a count function or a domain count. Do I need
to nest the queries? Could someone please help me with the query design that
will display all records in the memberID's for which there are NOT 2
MemberJoinedUnderId's in the first level table? Thanks so much!

 

[John Spencer]

This query should return just the member IDs with zero or 1 associated
records in the 1stLevel table
SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2

If you want more information then just the MemberID from the FrontLine table
then
SELECT FrontLine.*
FROM FrontLine
WHERE MemberID In
(SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

This query should return just the member IDs with zero or 1 associated
records in the 1stLevel table
SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2

If you want more information then just the MemberID from the FrontLine table
then
SELECT FrontLine.*
FROM FrontLine
WHERE MemberID In
(SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have several tables in a database, tracking membership signups by
generation. Because it is important for us to track that each member gets
2
and only 2 sign-ups underneath them in a particular affialiate program, I
designed the tables by generations of sign-ups, starting with a table
labeled
as FrontLine, and then 1stLevel, 2ndLevel, etc. I link the tables by using
a
one-to-many link in Front line from MemberId to MemberJoinedUnderId in 1st
level, and so on and so forth. I now need to perform a query that displays
each member that does not have two members signed underneath them. I was
trying to do this using either a count function or a domain count. Do I
need
to nest the queries? Could someone please help me with the query design
that
will display all records in the memberID's for which there are NOT 2
MemberJoinedUnderId's in the first level table? Thanks so much!

 

[Guest]

Thank You so Much John, the query worked perfectly, and saved me lots of
time. It has been years since I used any real SQL, so reading books and
researching how to do this would have taken many hours. I can't thank you
enough.

This query should return just the member IDs with zero or 1 associated
records in the 1stLevel table
SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2

If you want more information then just the MemberID from the FrontLine table
then
SELECT FrontLine.*
FROM FrontLine
WHERE MemberID In
(SELECT MemberID
FROM FrontLine LEFT JOIN [1stLevel] as L
ON FrontLine.MemberID = L.MemberJoinedUnderID
GROUP BY MemberID
HAVING Count(MemberJoinedUnderID) < 2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have several tables in a database, tracking membership signups by
generation. Because it is important for us to track that each member gets
2
and only 2 sign-ups underneath them in a particular affialiate program, I
designed the tables by generations of sign-ups, starting with a table
labeled
as FrontLine, and then 1stLevel, 2ndLevel, etc. I link the tables by using
a
one-to-many link in Front line from MemberId to MemberJoinedUnderId in 1st
level, and so on and so forth. I now need to perform a query that displays
each member that does not have two members signed underneath them. I was
trying to do this using either a count function or a domain count. Do I
need
to nest the queries? Could someone please help me with the query design
that
will display all records in the memberID's for which there are NOT 2
MemberJoinedUnderId's in the first level table? Thanks so much!