Query Help

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>