Download the Microsoft Access database, Century Engineering Job system.accdb, from the unit website and use it to complete this assessment.

Assessment details for ALL students
Assessment item 1
Due date: Friday of Week 5 (11:45pm AEST) ASSESSMENT
Weighting: 30% 1
Length: NA
Objectives
This assessment item relates to unit learning outcomes 1 and 2 in the unit profile.
Introduction
Students must complete all the following parts of this assessment tasks:
A. SQL Queries
B. Form
C. Report
Important
Download the Microsoft Access database, Century Engineering Job system.accdb, from the unit website
and use it to complete this assessment.
If you get a security warning message when opening the downloaded database, then you need to
open/click the options and then choose the option “enable this content”.
Do not modify the structures of the tables and relationships. Do not create additional tables. It is not
recommended to delete any of the existing records from the downloaded database (hereafter referred as
4/3/2020 54266 – Assessment details for ALL studentsAssessment item 1Due
https://www.australiabesttutors.com/Recent_Question/54266/Assessment-details-for-ALL-studentsAssessment-item 3/7
‘your database’).
You must create SQL queries in your database and make sure those queries produce the desired results as
shown in Part A. Save and name those queries suitably such as Q01, Q02, ……, Q10 respective to the
question that you are answering.
You need to create a MS Access-form and also a MS Access-report that are related to Part B and Part C
and save them in your database.
Century Engineering Job System database
The database, Century Engineering Job system.accdb, contains tables the following tables:
• Customer – this table lists customers of Century Engineering, including prospective customers who have
not yet requested a job
• Job – this table lists jobs that have been requested by customers. The Customer ID indicates the
customer who requested the job.
• Employee – this table lists employees of Century Engineering.
• JobAssignment – this table lists which employees have been assigned to which job. It also specifies how
many hours to date each employee has spent on that job.
• Invoice – this table lists invoices for jobs. One invoice is generated for each job some time after the job
is marked as completed. A job may be completed on one day but the invoice might not be generated until
a few days later. At this stage, an invoice only covers one job. Jobs that are not complete will not have an
invoice.
• Payment – this table lists payments that have been made against invoices (if any).
Part A – SQL Queries
Using your database, write SQL queries to answer all the questions in this part. Each of the following
questions has an information request followed by the expected results when your database has the given
sample data.
Please note:
• Where a column has been renamed in the expected result, you must reproduce that name in your results.
For example, if JobID appears as JobNbr in the expected result, then you must make this column name
appear as JobNbr in your output.
• Simple queries are preferred. You may lose marks if your query is overly complex.
• You are not required to enforce row order on your queries unless specified.
• Be careful of duplicates; remove duplicates if necessary.
• You are not required to use one type of clause or construct (e.g. you are not required to use an explicit
join over an implicit join) unless specifically instructed to do so in the question.
• Your SQL must continue to produce the correct answer even if the data changes. As an example, if asked
to list employees over the age of 18, your query must always list employees who are over the age of 18 at
the time that the query is executed. As time passes, the actual results would change as more employees
age past 18 years.
Questions:
1. List all customers with their address order by customer name.
Expected result using sample data:
ID Customer Address
1 David Tennant 10 East St, Albion, QLD
2 Jodie Whitaker 20 West St, Breakfast Crk, QLD
5 Matt Smith 45 South Ave, Albion, QLD
3 Matt Smith 30 John Cres, Logan, QLD
4 Peter Capaldi 40 Smith Ave, Logan, QLD
2. List all jobs together with the name and mobile number of the customer who logged the job. Order by
date logged.
Expected result using sample data:
JobNbr DateLogged Status FirstName LastName Mobile
4/3/2020 54266 – Assessment details for ALL studentsAssessment item 1Due
https://www.australiabesttutors.com/Recent_Question/54266/Assessment-details-for-ALL-studentsAssessment-item 4/7
1 17-Dec-19 Completed David Tennant 0400 123 456
2 20-Dec-19 Completed Jodie Whitaker
3 22-Dec-19 Completed Peter Capaldi
4 28-Dec-19 Completed David Tennant 0400 123 456
5 03-Jan-20 Completed Jodie Whitaker
7 05-Jan-20 Pending Peter Capaldi
6 05-Jan-20 In progress Jodie Whitaker
9 05-Feb-20 In progress David Tennant 0400 123 456
8 05-Feb-20 Completed David Tennant 0400 123 456
3. List customers who do not have any phone number listed (neither mobile nor telephone).
Expected result using sample data:
FirstName LastName Address Email Mobile Telephone
Peter Capaldi 40 Smith Ave p.Capaldi@bluebox.com.au
4. How many jobs are there for each status? Order the output so that the status with the most number of
jobs appears at the top.
Expected result using sample data:
Status NbrJobs
Completed 6
In progress 2
Pending 1
5. For each completed job, how many hours in total were used?
Expected result using sample data:
ID JobDescription TotalHours
1 Fabricate and fit chequerplate toolbox to suit Toyota LC trayback ute 5
2 Repair crack in Bobcat bucket & load test 16
3 Fabricate steel shelving t/s commercial kitchen 6
4 Repair crack in aluminium tray Toyota LC 4
5 Fabricate roll cage t/s go kart 8
6. Which employees have never been assigned to a job?
Expected result using sample data:
ID Employee Email
5 Erin Johnson erin@century.com.au
7. List all invoices; show the total excluding GST, the GST amount, and the total including GST. Note: the
existing InvoiceTotal is the Total including GST. You can calculate the total excluding GST by deducting
the amount in the GST column. Order by the customer last name and then first name.
Expected result using sample data:
Customer Total excl GST GST Total incl GST
Capaldi, Peter $1,000.00 $100.00 $1,100.00
Tennant, David $100.00 $10.00 $110.00
Tennant, David $50.00 $5.00 $55.00
Tennant, David $500.00 $50.00 $550.00
Whitaker, Jodie $900.00 $90.00 $990.00
8. List all jobs for customer number 1 that are either:
a. completed and have a quoted cost $500, or
b. are in progress.
Expected result using sample data:
ID JobDescription QuotedCost Status CustomerID
1 Fabricate and fit chequerplate toolbox to suit Toyota LC trayback ute $600.00 Completed 1
9 Re-inforce swaybar Toyota LC $550.00 In progress 1
9. Which customers have more than $1000 worth of invoices in total? Use the InvoiceTotal column in
your calculations.
4/3/2020 54266 – Assessment details for ALL studentsAssessment item 1Due
https://www.australiabesttutors.com/Recent_Question/54266/Assessment-details-for-ALL-studentsAssessment-item 5/7
Expected result using sample data:
FirstName LastName Expr1002
Peter Capaldi $1,100.00
10. List jobs where invoice total was more than the quoted cost.
Expected result using sample data:
JobNbr JobDescription QuotedCost InvoiceTotal
2 Repair crack in Bobcat bucket & load test $850.00 $990.00
3 Fabricate steel shelving t/s commercial kitchen $950.00 $1,100.00
11. List all invoices that are outstanding. You must be sure include those invoices which have no payments
at all. Order by the invoice total descending. NOTE: you will need to research the Access function nz() –
this function is used to replace a null with a 0. If you try to sum a column with nulls in it, the result will be
a null.
Expected result using sample data:
FirstName LastName InvoiceNbr InvoiceDate GST InvoiceTotal TotalPayment
Peter Capaldi 3 10/01/2020 $100.00 $1,100.00 0
Jodie Whitaker 2 10/01/2020 $90.00 $990.00 600
David Tennant 5 7/02/2020 $10.00 $110.00 0
Part B – Form
Using the Century Engineering Job System database, develop a form which can be used for data entry for
jobs. You are allowed to use any number of tables and any suitable layout for developing that form. Name
the form as “Job management”.
Part C – Report
Using Century Engineering Job System database, create a report that lists jobs and all assignments to those
jobs. A sample report for this part is shown below. Your report should
• Include a title, date, column headings and page numbering,
• closely resemble the sample report below; you may choose different colours/fonts, but the meaning of
the report must be the same and it must be professional in appearance.
• Have column headings/names named appropriately; for example, do NOT leave your column headings
as Employee.ID or QuotedCost; this would not be professional in appearance.
How and what to submit
You must make an electronic submission for this assessment using the appropriate assessment submission
link on the unit website. Zip your Microsoft Access database file. Your Microsoft Access file must contain
all tables, queries, form and report.
Assessment 1 marking guide:
Part Item Maximum marks Your marks Comments
A Q1 2
Q2 2
Q3 2
Q4 2
Q5 2
Q6 2
Q7 2
Q8 2
Q9 2
Q10 2
Q11 2
B Form can be used for data entry 4
C Report displays appropriate data. Appropriate heading provided. All data are visible and in the correct
sorted order. 4
Late Penalty 5% of available marks per calendar day late

The post Download the Microsoft Access database, Century Engineering Job system.accdb, from the unit website and use it to complete this assessment. appeared first on Versed Writers.

Acemyhomework
Calculate your paper price
Pages (550 words)
Approximate price: -
Acemyhomework

Quality Research Papers

We always make sure that our academic writers follow all your instructions precisely. You can choose your academic level and we will assign a writer who has a respective degree.

Acemyhomework

Best Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Acemyhomework

Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document.

Acemyhomework

On time Delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. Otherwise a 100% refund is guaranteed.

Acemyhomework

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. We also promise maximum confidentiality in all of our services.

Acemyhomework

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Homework Market

Place your order

Fill in the order form and provide all details of your assignment.

Homework Market

Proceed with the payment

Choose the payment system that suits you most.

Homework Market

Receive the final file

Once your paper is ready, we will email it to you.

Academic Writing Services

Whether you have an urgent deadline or those that have time. You can take some time and relax after trusting us with your paper. We make sure that we conduct the academic writing services diligently.

Acemyhomework Acemyhomework

Essays

Essay Writing Service

Among the wide variety of academic work, essay writing is one of the simplest a student can ever come across. Usually, it is a task which students encounter and learn how to write whilst in high school. However, the case is quite different when it comes to university and college.

Acemyhomework Acemyhomework

Term Papers

Term Paper Writing

Are you looking for an online writing firm that can offer you reliable custom term paper writing help? Is your wish and desire to get someone who can guide you throughout the process of writing term papers? If yes, then you have come to the right place.

Acemyhomework Acemyhomework

Coursework

Coursework Writing Help

Coursework is essential for every student in order to graduate from college. However, most of it is deadline-centric, and that becomes a challenge to most learners. With the amount of work, learners are receiving every day, finding time to work on every task is not easy.

Acemyhomework Acemyhomework

Homework

Online Homework Help

Online homework help services are an answer to every challenge that students go through. Despite the difference in the needs and levels of learning, all students can benefit from these services. Acemyhomework is one of the best online homework help service companies you can find on the internet.