If you are looking to search expert that help in your database access assignment, project and homework - we are ready to provide help as per your expectation. We have group of experienced experts and professionals that deal with large amount of projects and homework related to any business case analysis, database design, entity relationships diagram creation, querying, and creating all the necessary forms and reports in Microsoft Access.
If you are interested to start work as a IT professional then database knowledge is necessary. Microsoft Access helps students with a relatively easy start - first learn basic of MySQL and Oracle.
Our experts can help you with Microsoft Access assignments as:
Design and implementation of database
Form design Using a ccess
Data Analysis Using access
ERD using MS Visio (professional) or draw.io
Querying Using access
Reports Writing Using access
Filtering data
Sample Question Paper
Instruction
To complete this assignment, download the provided database file from Nexus.
Provide the answer for question 9 and 10 in a Assign2.sql file
Submit your modified MS Access database (.accdb file) and the Assign2.sql file together in Assign2.zip file via Nexus.
Part A (Queries)
Save the queries as Query1, Query2… Note that you may need to modify the SQL view for some of these queries.
1. Design a query to list the ID, first name, and last name of employees who earn more salary than their supervisor and the amount by which the employee’s salary is higher.
2. Design a query to enable the user to enter the name of a product and list the ID and product names of products having a different Quantity Per Unit from that of the product entered.
3. Design a query to enable the user to enter the first name and last name of an employee and list the ID, first name, last name, and hire date of all employees hired within two years after the employee entered was hired. For example, if the name entered was hired on March 20, 2019, all employees hired from then up until March 20, 2021, must be listed.
4. Design a query to list all Customer IDs, the Order ID of the orders they have submitted (if any), and the employee ID that handled the order. All employees (including those who have never handled an order) and all customer IDs (including those who have never submitted an order) must also be listed. The list must be ordered in ascending order of the employeeID, and then in ascending order of the orderID (if the employee is listed more than once). A sample of the expected result from a different dataset is provided below: Customer ID ABCD has never submitted an order and employee ID 55 and employee ID 4 have never handled an order.
5. Design a query to list each product name, the number of units in stock for the product, and the total number of products that have the same units in stock as the product. The list must be in ascending order of the units in stock values and then in ascending order of the product name (if more than one product has the same units in stock). A sample of the expected result is provided below.
6. Design a query that enables the user to enter the name of a country and list the names of products and the total number of times the product has been ordered by customers in that country. Also, display the quantity of the product sold in the country. The products with the highest quantity sold in the country must be listed first. For example, entering Germany may produce the following result:
7. Design a query to list all employees who earn more salary than their supervisor. Display the following fields
8. Design a query to list the products(ProductId, ProductName) that have never been bought in Western Europe. Countries in Western Europe include Austria, Belgium, France, Germany, and Switzerland. Hint: think of how you could use the ‘Find Unmatched Query Wizard’ in MS Access. Employee ID First Name Last Name Employee Salary Supervisor Salary … … … … .. 3 Provide the answers for Questions 9 and 10 in a Assign2.sql file. Include your name and student number as comment in the file.
9. Given the following query design in MS Access, provide the corresponding SQL statement for the query. The properties of the join between rental and inventory is also shown.
10. Given the following query design in MS Access, provide the corresponding SQL statement for the query.
11. Use the GUI to add a new column called salePrice to the Order Details table. Given that each product listed in order Details table may have been sold at a discount, the new column is expected to hold the actual sale price of each product in order details. Note that the original prices of products are listed in the product table. Design the query to update the salePrice field so that it contains the actual sale price of each product in the Order Details table. For example, if a product’s unit price is $10, and it was sold at 20% discount, then the sale price is $8.
Note: no other changes must be made to structure of the provided database besides adding the salePrice column.
Part B
Create the hierarchy of forms shown below in your database. The details about each form are provided below the hierarchy.
1. The main menu form must open automatically when your database opens.
The main menu form has 5 controls:
- 1 label to hold the name of the form
- 3 buttons to open the Queries, Reports, and Data Management forms
- 1 button to close the application
2. The queries form allows the user to run the even-numbered queries Query 2/4/6/8 (labeled, Q2, Q4, Q6, and Q8 above).
Queries form has 6 controls:
- 1 label to hold the name of the form
- 4 buttons to run each of the queries Q2, Q4, Q6, and Q8
- 1 button to close the Queries form
3. Reports: generated by the Reports Wizard.
Sales Per Product in Countries: Design a report that is based on a query. The query lists the names of products, the total number of times the product has been ordered by customers in different countries, and the name of the country. The report should list the product names in ascending order and show the number of times it has been ordered in each country. A sample of the beginning of the report is shown in Figure 1.
Customer Orders Summary: design a report that is based on the relationships in the database. The report shows each customer's name, the order ID of the orders the customer has submitted, and the ID and name of the employee who handled the order. For each customer, it lists the order ID in ascending order. A sample of the expected result is provided in Figure 2.
The report form has 4 controls:
- 1 label to hold the name of the form
- 2 buttons to open each of the reports; provide meaningful names for the reports.
- 1 button to close the Reports form
4. Data Management form.
The Data Management form has 8 controls
- 1 label to hold the name of the form
- 6 buttons to open each of the table forms
- 1 button to close the Data Management form
5. Table forms: generated by the Forms wizard for each table.
Each of the Categories, Customers, Employees, Order Details, Orders, and Products forms has 3 buttons
- 1 button to add a record
- 1 button to delete a record
- 1 button to close the form
Comments