Access/Database Assignment

SELECT DISTINCT ( JobAssignment.Subsidiary_Number), Employees.First_Name, Employees.Last_Name FROM Subsidiary INNER JOIN (Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number) ON Subsidiary.Subsidiary_Number = JobAssignment.Subsidiary_Number WHERE (((JobAssignment.Social_Security_Number)=[Employees].[Social_Security_Number]));

SELECT DISTINCT Employees.Last_Name, JobClassCode.Job_Class_Code FROM Employees INNER JOIN (JobClassCode INNER JOIN JobAssignment ON JobClassCode.Job_Class_Code = JobAssignment.Job_Class_Code) ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Social_Security_Number)=Employees.Social_Security_Number) And ((JobAssignment.Subsidiary_Number)=1)) ORDER BY Employees.Last_Name;

SELECT Count(*) AS NoOfEmployees FROM Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Social_Security_Number)=[Employees].[Social_Security_Number]) AND ((JobAssignment.Subsidiary_Number)=1));

SELECT Employees.First_Name, Employees.Last_Name FROM Employees INNER JOIN SubQuery10 ON Employees.Social_Security_Number = SubQuery10.Social_Security_Number WHERE (((Employees.Social_Security_Number)=[SubQuery10].[Social_Security_Number]));

SELECT Subsidiary.Subsidiary_Number, Employees.Last_Name, Employees.City FROM Subsidiary INNER JOIN (Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number) ON Subsidiary.Subsidiary_Number = JobAssignment.Subsidiary_Number WHERE (((Subsidiary.Subsidiary_Number)=2));

SELECT Employees.First_Name, Employees.Last_Name, (SELECT MAX(JobAssignment.Hourly_Base_Pay) AS MaxHrRate FROM JobAssignment ) FROM Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Social_Security_Number)=[Employees].[Social_Security_Number]));

SELECT TOP 15 Employees.First_Name, Employees.Last_Name, JobAssignment.Hourly_Base_Pay, JobAssignment.Budgeted_Hours_Per_Pay, [JobAssignment].[Budgeted_Hours_Per_Pay]*[JobAssignment].[Hourly_Base_Pay] AS Expr1 FROM Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Social_Security_Number)=Employees.Social_Security_Number) And ((JobAssignment.Subsidiary_Number)=2));

SELECT Subsidiary.Subsidiary_Name, JobClassCode.Job_Class_Code, Count(Employees.Social_Security_Number) FROM Subsidiary INNER JOIN (Employees INNER JOIN (JobClassCode INNER JOIN JobAssignment ON JobClassCode.Job_Class_Code = JobAssignment.Job_Class_Code) ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number) ON (Subsidiary.Subsidiary_Number = JobClassCode.Subsidiary_Number) AND (Subsidiary.Subsidiary_Number = JobAssignment.Subsidiary_Number) WHERE (((Subsidiary.Subsidiary_Number)=JobClassCode.Subsidiary_Number) And ((JobClassCode.Job_Class_Code)=JobAssignment.Job_Class_Code) And ((JobAssignment.Social_Security_Number)=Employees.Social_Security_Number)) GROUP BY Subsidiary.Subsidiary_Name, JobClassCode.Job_Class_Code;

SELECT Employees.First_Name, JobAssignment.Budgeted_Hours_Per_Pay FROM Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Social_Security_Number)=[Employees].[Social_Security_Number]));

UPDATE Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number SET JobAssignment.Budgeted_Hours_Per_Pay = 1.23 WHERE (((JobAssignment.Budgeted_Hours_Per_Pay)=0 Or (JobAssignment.Budgeted_Hours_Per_Pay) Is Null) And ((JobAssignment.Social_Security_Number)=Employees.Social_Security_Number));

SELECT Max(JobAssignment.Hourly_Base_Pay) AS MaxOfHourly_Base_Pay, JobAssignment.Subsidiary_Number FROM JobAssignment GROUP BY JobAssignment.Subsidiary_Number ORDER BY Max(JobAssignment.Hourly_Base_Pay) DESC;

SELECT Min(JobAssignment.Hourly_Base_Pay) AS MinOfHourly_Base_Pay, JobAssignment.Subsidiary_Number FROM JobAssignment GROUP BY JobAssignment.Subsidiary_Number ORDER BY Min(JobAssignment.Hourly_Base_Pay);

SELECT Avg(JobAssignment.Hourly_Base_Pay) AS AvgOfHourly_Base_Pay, JobAssignment.Subsidiary_Number FROM JobAssignment GROUP BY JobAssignment.Subsidiary_Number ORDER BY Avg(JobAssignment.Hourly_Base_Pay);

SELECT Employees.* FROM Employees WHERE (((Employees.Date_Of_Birth)<#1/1/1970#));

SELECT Subsidiary.Subsidiary_Name, Count(JobClassCode.Job_Class_Code) AS CountOfJob_Class_Code FROM Subsidiary INNER JOIN JobClassCode ON Subsidiary.Subsidiary_Number = JobClassCode.Subsidiary_Number WHERE (((Subsidiary.Subsidiary_Number)=[JobClassCode].[Subsidiary_Number])) GROUP BY Subsidiary.Subsidiary_Name;

DELETE Employees.*, Employees.City FROM Employees WHERE (((Employees.City)="Clio"));

SELECT Count(JobAssignment.Job_Class_Code) AS CountOfJob_Class_Code, JobAssignment.Subsidiary_Number, JobClassCode.Job_Class_Name FROM JobClassCode INNER JOIN JobAssignment ON JobClassCode.Job_Class_Code=JobAssignment.Job_Class_Code GROUP BY JobAssignment.Subsidiary_Number, JobClassCode.Job_Class_Name;

SELECT Employees.City, Count(Employees.Social_Security_Number) AS CountOfSocial_Security_Number FROM Employees, Subsidiary GROUP BY Employees.City, Subsidiary.Subsidiary_Number HAVING (((Subsidiary.Subsidiary_Number)=2));

SELECT Employees.Last_Name, JobClassCode.Job_Class_Code FROM Employees, JobClassCode GROUP BY Employees.Last_Name, JobClassCode.Job_Class_Code, JobClassCode.Subsidiary_Number HAVING (((JobClassCode.Subsidiary_Number)=1)) ORDER BY JobClassCode.Job_Class_Code;

SELECT Employees.Last_Name, JobAssignment.Budgeted_Hours_Per_Pay, JobAssignment.Subsidiary_Number FROM Employees INNER JOIN JobAssignment ON Employees.Social_Security_Number = JobAssignment.Social_Security_Number WHERE (((JobAssignment.Subsidiary_Number)=2)) ORDER BY JobAssignment.Hourly_Base_Pay;

SELECT JobClassCode.Subsidiary_Number, JobClassCode.Job_Class_Code, JobClassCode.[Job_Class_code] AS Count FROM JobClassCode GROUP BY JobClassCode.Subsidiary_Number, JobClassCode.Job_Class_Code, JobClassCode.[Job_Class_code];

SELECT Subsidiary.Subsidiary_Name, Max(Quiz2.[Pay]) AS Max, Min(Quiz2.[Pay]) AS Min, Avg(Quiz2.[Pay]) AS Avg FROM Quiz2, Subsidiary INNER JOIN JobAssignment ON Subsidiary.Subsidiary_Number=JobAssignment.Subsidiary_Number GROUP BY Subsidiary.Subsidiary_Name;

SELECT JobAssignment.Social_Security_Number, Count(Subsidiary.Subsidiary_Number) AS CountOfSubsidiary_Number FROM Subsidiary INNER JOIN JobAssignment ON Subsidiary.Subsidiary_Number = JobAssignment.Subsidiary_Number WHERE (((Subsidiary.Subsidiary_Number)=[JobAssignment].[Subsidiary_Number])) GROUP BY JobAssignment.Social_Security_Number HAVING (((Count(Subsidiary.Subsidiary_Number))>1));

SELECT Subsidiary.Subsidiary_Name, Max(JobAssignment.Hourly_Base_Pay) AS Max, Min(JobAssignment.Hourly_Base_Pay) AS Min, Avg(JobAssignment.Hourly_Base_Pay) AS Average FROM JobAssignment INNER JOIN Subsidiary ON Subsidiary.Subsidiary_Number = JobAssignment.Subsidiary_Number GROUP BY Subsidiary_Name;

Assignment 6 CIS/WHAT TO DO.docx

Assignment 6

This is the last assignment for my class. The entire class has been editing the access document in the folder called “Assignment 5”..I really messed up on the actual Assignment 5 so I’m not sure if it’s going to make Assignment 6 (this assignment) more difficult. Because of this, I included what was wrong with Assignment 5 below (you don’t have to fix these problems unless needed to complete the actual Assignment 6)

“There were still a number of problems with this resubmit.  Your naming of the reports was confusing to say the least.  Quiz Report 1?  Also several of the reports were broken and wanted data entered.  Not sure what you did with those... #2) You didn't worry about formatting the calculated column results to currency.  Also you left the default Expr1 title on that column -- would you want to see that on a report you were given every day, or would you expect a more meaningful column title?  Also your header had the name Report spelled incorrectly as Reoprt...  This was pretty sloppy formatting in general. #5) You were to count the number of departments and list their names at each company.  You instead provided a count of the number of employees who worked in each of the departments. #6) There was no report given that printed employees grouped by city. Report 6 claimed to do this with its title -- but it didn't.”

Actual assignment 6:

(1) Create a nice looking data entry form for each of the tables in your database. Allow the forms to view/add/edit/delete records as necessary. Put in validation controls as necessary and ensure that everything is working correctly on the form.

(2) Add a navigation system to the form (chapter 14). Create a custom form that has options to:

Work with records (would then give a list of the available forms you created in step 1)

Reports (would then give a list of the reports that you created in assignment 5)

Exit

Whenever a user clicks on one of the forms in Work with Records, you would show them that form and allow them to add/edit/view/delete records. When they leave that form they should go back to the navigation system.

Likewise, if a user selects Reports, they would pick a report from the list of possible reports and that report would be shown. When they are done viewing the report, they should be put back on the navigation system.

THANK YOU!