MIS Access

ScienceClub-SOLUTION.accdb

Home >Business & Finance homework help >Operations Management homework help >MIS Access

TeamID	ProjectName	Budget	Judged	ProjectID
T001	Lego	¤ 55.00	true	P001
T001	Red Skunk	¤ 78.00	true	P002
T002	Pointless Dagger	¤ 12.00	true	P003
T003	Nervous Gravel	¤ 44.00	true	P004
T004	Hollow Wrench	¤ 98.00	true	P005
T005	Rhinestone Brave	¤ 35.00	false	P006
T001	Lego Tower	¤ 88.00	true	P007
T006	Brown Torpedo	¤ 85.00	true	P008
T008	Ivory Temple		true	P009
T008	Accidentally Pottery	¤ 35.00	true	P010
T002	Streaming Logbook	¤ 81.00	true	P011
T005	City Lego	¤ 46.00	false	P012
T007	Harsh Storm	¤ 67.00	true	P013

TeamID	TeamName	TeamType	Established	Active
T001	The Curious Rabbits	Diploma	9/15/15	true
T002	Demolition Sharks	Exploring	11/11/14	true
T003	American Blasters	Learning	12/11/15	false
T004	Red Wild	Competitive	9/30/15	true
T005	Awesome Planets	Diploma	8/5/15	true
T006	Head Busters	Exploring	6/16/15	true
T007	Black Miners	Learning	5/10/15	false
T008	Urban Shinobi	Competitive	3/14/15	true
T009	Pink Bombers	Diploma	10/5/15	true
T010	Ace Breakers	Learning	4/19/15	true
T011	Happy Dogs	Learning	8/19/15	false

MemberID	TeamID	Name	Gender	Awards
M001	T001	Teresa Patton	Female	0
M002	T001	Shaun Bush	Male	0
M003	T002	Sylvester Gilbert	Male	1
M004	T003	Jody Stephens	Female	0
M005	T004	Neil Graves	Male	0
M006	T005	Curtis Larson	Male	2
M007	T006	Anna Sutton	Female	2
M008	T007	Wilbert Marsh	Male	0
M009	T008	Nancy Owen	Female	0
M010	T009	Renee Gardner	Female	0
M011	T010	Tyler Higgins	Male	2
M012	T001	Virgil Mcdaniel	Male	3
M013	T002	Evelyn Cook	Female	0
M014	T003	Rochelle Owens	Male	0
M015	T001	Johnny Hogan	Male	0
M016	T002	Lula Torres	Female	0
M017	T003	Nicolas Day	Male	1
M018	T004	Bernard Little	Male	2
M019	T005	Mercedes Perkins	Female	3
M020	T006	Bradford Wheeler	Male	1
M021	T007	Andre Armstrong	Male	0
M022	T008	Sonia Cain	Female	0
M023	T009	Loren Carlson	Male	2
M024	T010	Ernesto Maxwell	Male	0
M025	T001	Hugh Miller	Male	1
M026	T002	Marion Parsons	Female	0
M027	T003	Alan Vargas	Male	0

SELECT DISTINCTROW * FROM ScienceTeams;

PARAMETERS __TeamID Value; SELECT DISTINCTROW * FROM Projects AS ScienceTeams WHERE ([__TeamID] = TeamID);

SELECT Projects.Budget, Projects.ProjectID, Projects.ProjectName FROM Projects WHERE (((Projects.Budget)<50)) ORDER BY Projects.Budget DESC, Projects.ProjectID;

SELECT Projects.ProjectName, Projects.Budget FROM Projects WHERE (((Projects.Judged)=Yes)) ORDER BY Projects.ProjectName;

SELECT TeamMembers.MemberID, TeamMembers.Name, TeamMembers.Awards, ScienceTeams.TeamID, ScienceTeams.TeamName, ScienceTeams.TeamType, ScienceTeams.Established FROM ScienceTeams INNER JOIN TeamMembers ON ScienceTeams.TeamID = TeamMembers.TeamID WHERE (((TeamMembers.Awards)=0) AND ((ScienceTeams.TeamType)="Diploma")) OR (((TeamMembers.Awards)=0) AND ((ScienceTeams.Established)>#7/1/2015#)) ORDER BY TeamMembers.MemberID;

SELECT Projects.ProjectID, Projects.ProjectName, Projects.Budget, Projects.Judged, ScienceTeams.TeamType FROM ScienceTeams INNER JOIN Projects ON ScienceTeams.TeamID = Projects.TeamID WHERE (((ScienceTeams.TeamType)=[Enter Desired Team Type])) ORDER BY Projects.ProjectID;

SELECT ScienceTeams.TeamID, ScienceTeams.TeamName, ScienceTeams.TeamType, Projects.ProjectID, Projects.ProjectName, [Budget]+20 AS AdjustedBudget FROM ScienceTeams INNER JOIN Projects ON ScienceTeams.TeamID = Projects.TeamID WHERE (((ScienceTeams.TeamType)="Competitive")) ORDER BY ScienceTeams.TeamID;

SELECT ScienceTeams.TeamType, Sum(Projects.Budget) AS TotalBudget FROM ScienceTeams INNER JOIN Projects ON ScienceTeams.TeamID = Projects.TeamID GROUP BY ScienceTeams.TeamType;

SELECT ScienceTeams.TeamID, ScienceTeams.TeamName, ScienceTeams.Established FROM ScienceTeams LEFT JOIN TeamMembers ON ScienceTeams.[TeamID] = TeamMembers.[TeamID] WHERE (((TeamMembers.TeamID) Is Null));

SELECT ScienceTeams.TeamName, ScienceTeams.TeamType, Projects.ProjectName FROM ScienceTeams INNER JOIN Projects ON ScienceTeams.TeamID = Projects.TeamID WHERE (((Projects.ProjectName) Like "Lego*")) ORDER BY ScienceTeams.TeamType;

SELECT ScienceTeams.TeamName, ScienceTeams.TeamType, Projects.ProjectName FROM ScienceTeams INNER JOIN Projects ON ScienceTeams.TeamID = Projects.TeamID WHERE (((ScienceTeams.TeamType)="Learning") AND ((Projects.ProjectName) Not Like "*Lego*")) ORDER BY ScienceTeams.TeamType;

SELECT DISTINCTROW * FROM Projects;