Pass level Tasks
ALL tasks in this section MUST be completed for you to Complete the Pass Level Tasks
Pass Task 1.
Open the Movie4567 (or similar) Table in datasheet view (this is NOT a query)
Use multiple filters and sorting to show
Movies that meet all of these criteria simultaneously o Release year >= 1990
o Runtime is 90 and 99 minutes o Rating code is G or PG
o TMDB score is between 6.5 and 7.5
o The list must be in Ascending Title sequence.
Use a screen capture tools (such as the Snipping Tool in Programs / Accessories within Windows)
Capture the few rows that are displayed after applying the above filters and sorting.
This is an example of how your partial screen capture should look (the row values will differ). Note that the table name must contain the final 4 digits of your student ID.
Pass Task 2.
Create a Query that displays the following:
the MovieNo & title & relyear of all movies from the year 2014. The list must appear in Ascending MovieNo sequence
Save the query. Call it W1PT2
Screen capture the Query Design Grid. It will be similar to this: (the fields will differ).
Screen capture all of the rows that are displayed once this query is executed:
Pass Task 3.
Create a Query called W1PT3
It must display the relyear, title and TMDB_SCORE for all movies that were made in 2010 or later and that have a rating score > 7.5
Screen Capture all of the rows that are displayed. E.g.
Pass Task 4.
Create a single Query called W1PT4
It must display the movieno, title ratingcode, relyear and runtime for all movies that were made in the period 2000-2009 (inclusive) and have a runtime less than 100 minutes and are rated "G" or "PG".
Screen Capture the Query Design Grid.
Screen Capture all rows displayed by this query.
Paste the screen captures in the appropriate position in the document named W01P.DOCX
INF10002 / INF60009 Database Analysis and Design Week 1 Submission Tasks
Pass Task 5.
Create a single Query called W1PT5
It must display all fields for movies that meet all of these criteria
The movie must have the phrases woman or girl or miss or mrs within the title The movie must have fewer than 1000 TMDB_Votes
Screen Capture the Query Design Grid.
Screen Capture all of the rows that are displayed.
Paste the screen captures in the appropriate position in the document named W01P.DOCX
Pass Task 6.
Create a new table named Member.
Add the last four digits of your student ID e.g. Member4567
The table must include a 4 columns
o Membid, Surname, Givenname and Birthdate.
o Memberid must be Numeric (not Autonumber) type. o Surname and Givenname are short text type.
o Birthdate is a data/time type.
Memberid must be the primary key.
Add the following three rows that match the data below
Add one additional row which:
includes your name & birthdate.
Use your student ID as the memberID (If your ID contains an "X" character, then omit the "X" from the member id.
Screen Capture the table design
Screen Capture all four rows in the Member4567 table
Credit level Tasks
ALL tasks in this section MUST be completed for you to Complete the Credit Level Tasks
Download the Customer database named cust.accdb from Blackboard.
Open the file using Microsoft Access.
Important: Before you begin, you must rename the CUSTOMER table so that it now contains the word CUSTOMER plus the final 4 digits of your student ID. E.g. If your id is 100234567 then the name must be CUSTOMER4567.
Credit Task 1.
Create a single query named W1CT1
The query must bispay the Customer ID, surname, gender, loyalty pts, rating code and state for all records that meet all of these criteria:
The gender must be female
The loyalty points must be greater than or equal to 9600 The Rating code must be 1
The state must be Victoria
Screen Capture the table design
Screen Capture all rows displayed by this query.
Credit Task 2.
Create a single query named W1CT2
The query must bispay the Customer ID, surname, gender, loyalty pts, rating code and state for all records that meet all of these criteria:
Sales this year must be less than 190 The Rating code must be 1
The state must be Victoria
The loyalty points must be in the range 500 to 999 OR
Sales last year must be greater than 210 The Rating code must be 2
The state must be New South Wales
The loyalty points must be in the range 9900 to 9999
Screen Capture the table design
Screen Capture all rows displayed by this query.
Credit Task 3.
Create a single query named W1CT3
The query must bispay the Customer ID, given name, surname, date of birth for all records that meet all of these criteria:
The person must be within these dates: 30th Nov 2000 to 30th June 2002
Screen Capture the table design
Screen Capture all rows displayed by this query.
