access database

https://specialty-pharmacy.com/images/content/veterinary.jpg , Specialty Pharmacy Services, Melbourne, Florida.

Your firm’s Systems Analyst has developed a database structure that has been determined to be suitable to create a database to store Dr Little’s customer, item, supplier and order data for the business, which has been provided as follows:

Table Specifications

Dr Little has provided to us a partial list of the business’s customers, stock items, and suppliers and a partial list of the business’s postal orders placed between June and August 2018 to use as test data in four comma delimited text files which can be found on your firm’s Intranet (CIS1000 Study Desk).

Dr Lisa Chimes, your manager, has set up the following tasks for you to complete for this phase of the project:

Create a new blank Access Database (using any version from Access 2007 to current) and name it [lastname] [initial] _ [student number] _ [course code] _ assign1.accdb (eg. genrichr_0050051005_cis1000_assign1.accdb).

· Use the above table specifications, checking that you have the correct table names and field names.

· Check that all fields have the appropriate Data Type from the following:

· Text, Number, Date/Time, Currency, Yes/No, Hyperlink.

· Add appropriate Field Descriptions to ALL data fields with a well-structured description, eg. for CustID an appropriate description would be:

· Field Size – set appropriate logical fields sizes for all Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double)

· Caption – set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)

· Format – set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Hyperlinks data fields that should be stored in lower-case (EmailAddress); and numbers that contain currency or percentages

For example:

Create the following relationships between the four (4) tables:

Check that the correct table names and field names are listed in the ‘Edit Relationships’ screen.

Note: This must be done after you have established the primary keys

See the Beskeen et al textbook Access Chapter 5 and the YouTube videos on the StudyDesk for more details on creating relationships.

3. Using www.whereis.com.au look up the distance between your current address and Toowoomba QLD 4350 – change the Freight Distance to this new value.

4. Change the Mobile Number to your student ID eg. 0061 099999

· Do not use the u1099999 version.

5. Change the Email Address to your own details.

6. Select that you would like to be on the Business’s Mail List.

Task 6: Create Data and Update Queries

Create the following Five (5) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).

· You must create the Queries using Access Design View , if you create the Queries using SQL this will result in ZERO marks for the Query.

· Query names must conform to the Leszynski Naming Convention (LNC) , as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials – including the Query Number and Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx .

· Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query’s output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results

See the Beskeen et al textbook Access Chapter 8, Access Chapter 9 and Access Chapter 10 for more details on creating queries

· Query 1: Unfulfilled Light July Dog Bowls and Feeding Accessories Orders:

Dr Little would like to see how many Dog Bowls and Feeding Accessories orders placed in July weighing half a kilogram (0.5 kg) or less were unfulfilled due to the items being out of stock.

Create a Select Query that will display the full Customer’s Name (including Title, Given Names and Family Name), Full Item Details (including Item Type, Item Description, Item Size and Freight Weight), Order Date and Order Qty.

Limit the data to all Dog Bowls and Feeding Accessories orders, placed in the month of July (using the BETWEEN criteria tool), weighing less than or equal to 0.5 Kg, and that are currently on order but not in stock.

Display the results sorted by Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Note : This Query will display 5 rows of data if done correctly.

· Query 2: Southern Australian Cat related Orders from a Supplier:

Dr Little would like to have a query that the business can reuse with a manually entered Supplier Name, to see how many customers living in Southern Australia (South Australia, Victoria or Tasmania) have purchased a Cat related product obtained from the Supplier as specified.

Create a Select Query that will display the full Customer’s Name (including Title, Given Names and Family Name), and Full Customer’s Address, and the Item Type, Item Description, Item Size, Dispatch Date and Order Qty.

Limit the data to customers who live in South Australia, Victoria or Tasmania (using the IN criteria tools), who have purchased an Item Type containing the word “Cat” (using the LIKE criteria tool) obtained from a user specified Supplier.

Note: As no supplier name has been given, this query must use a Parameter in place of the supplier name.

Display the results sorted by Item Type and Item Description, check that have you included all necessary Fields and ensure that you only display the required Fields and test using Supplier’s Name of "Fila Brasileiro International".

Note: This Query will display 17 rows of data if done correctly (with CustID 112 living in QLD).

· Query 3: Import Duty Romania (RO) Cost Price Increase:

A new Australian import duty of 2.5% has been added for all products being imported from Eastern Europe including Romania (RO) and Dr Little has asked you to develop a query that will update the Cost Price from Romania of all items that are currently on order. As it would be considered profiteering, it is important that the query does not update the Cost Price of items already in stock.

Create an Update Query that will raise the Romania (RO) Cost Price for all items on order that are not in stock by 2.5%. It is always good practice to backup any data before running an update or a delete query. The following steps should be followed to achieve this Query:

· In the List of Tables, copy the table called tblItems and paste it with the new name tblBackupItems .

Use tblItems for this Update Query, not your back up table tblBackupItems. If you have difficulties with this query and need to restore your data to the original values, you can copy columns as necessary from tblBackupItems to tblItems and start again.

· Create an Update Query that will increase the Cost Price (RO) in tblItems by 2.5% for all items currently not in stock but on order.

· Include rounding to 2 decimal places with the update calculation by placing the following function around the update formula: ROUND( update_formula ,2)

Hint: ROUND(Cost Price (RO) + Cost Price (RO) * 2.5% ,2)

Note: Percentages must be converted to decimals in Update Queries.

Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.

Make sure you DO NOT change the Query Type – keep it as an Update Query and do not run the Update Query more than once.

Note: This Query will update 31 rows of data if done correctly – compare the following three Items (ItemIDs 164, 186, & 239) between the tblItems and tblBackupItems to see if the update has been applied successfully.

· Query 4: Late Dispatched Orders Form Query:

Dr Little would like to have a single query that the business can display within a form (Task 7: Form 1) to see within this single query the customers who are either:

i) Living in Queensland, have had items dispatched late (between 15th August and today) and where the item was ordered after the 1st August and the item is in stock

Or

ii) Living in New South Wales, have had items dispatched late (between 20th August and today) and where the item was ordered after the 5th August and the item is in stock

Or

iii) Living in Victoria, have had items dispatched late (between 25th August and today) and where the item was ordered after the 10th August and the item is in stock

Create a Select Query that will display the CustID, Full Customer’s Name (including Title, Given Names, and Family Name), Full Customer’s Address, Date of Birth, eMail Address, MailingList, ItemID, Full Item Details (including Item Type, Item Description, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers.

Limit the data in the single query to either customers (using the OR Criteria):

i) living in Queensland, who have placed Orders after the 1st August 2018 and where these orders were only dispatched between the 15th August 2018 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

ii) OR living in New South Wales (using the OR criteria row), who have placed Orders after the 5th August 2018 but where these orders were only dispatched between the 20th August 2018 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

iii) OR living in Victoria (using the OR criteria row), who have placed Orders after the 10th August 2018 but where these orders were only dispatched between the 25th August 2018 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

Display the results sorted by State and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Note: This Query will display 24 rows of data if done correctly (with CustID 112 living in QLD). 8 NSW records, 3 QLD records and 13 VIC records.

· Query 5: Invoice Query with Calculated Fields:

Dr Little would like to have a query that the business can use to produce customer invoices (Task 8) to see how many customers on our Mailing List ordered either Cat Toys or Dog Toys, but only those dresses that are not made of Nylon, and where the item was in stock. This query should also calculate the selling price (based on goods purchased from the Liberia) and order total, both of which will be needed on the customer invoices.

Create a Select Query that will display the CustID, Full Customer’s Name (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (including Item Type, Item Description, Item Colour, Item Size, Freight Weight and Supplier Name), OrderDate, and OrderQty, including two calculated Fields - Selling Price (AU) and Order Total (AU).

Limit the query to only those customers on our Mailing List who ordered a product of type Cat Toys or Dog Toys (using the IN criteria tools), where the Item Description DOES NOT contain the word “nylon” (using the NOT criteria tools and the LIKE criteria tool), and where the business currently has that item in stock.

Note:

· SellingPrice(AU) – CostPrice (LR) x Exchange Rate x (1 + Recommended MarkUp)

· OrderTotal(AU) – SellingPrice(AU) x Ordered Qty

Look up Exchange Rate LRD to AUD: www.xe.com

Include rounding to 2 decimal places for the Selling Price Calculated Field by placing the following function around the formula:

ROUND( calculated_Field ,2)

SellingPrice and OrderTotal must be formatted to the appropriate currency and provided with meaningful captions in the properties for these two calculated Fields.

Display the results sorted by CustID, ItemID and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.

Note: This query will display 19 rows of data if done correctly.

Task 7: Create Data Display Forms

Create the following two (2) data entry forms using the Form Wizard and Form Design.

· Form names must conform to the Leszynski Naming Convention (LNC) , as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials – including the Form Number and Two Meaningful Words to describe the form eg. frm1XxxxxxXxxxxx .

· Form 1: Late Orders Form Display with Calculated Fields:

Using all Fields from Task 6 Query 4, create a professional user-friendly Display Form (using either the Columnar or Justified Form Wizard Templates) that will allow users to view order details stored in the Database Management System (DBMS).

Include two calculated Textbox Fields that will calculate:

· Customer’s Age in whole years (using the DATEDIFF function - see video Assignment 1 Extra Resources).

· Dispatch Delay in whole days (Dispatch Date – Order Date).

Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.

The form should demonstrate the following form controls:

· Image –an appropriate logo at the top of the form

· Label –an appropriate Form Title and appropriate Section Headings

· Combo Boxes (using a Value List in the Properties) – demonstrate for Title, State and Item Type Fields

· Check box – demonstrate for MailingList Field

· Buttons – demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

· Form 2: Supplier Items Display Form with Subform:

Create a professional user-friendly Display Form (using either the Columnar or Justified Form Wizard Templates) and a sub-form that will allow users to view each supplier record’s details stored in the Database Management System (DBMS).

The main form should contain All Supplier details, the sub-form should contain a Datasheet view of the items sold by that particular Supplier (including at least Item Type, Item Description, and Freight Weight) – name the subform: frmItemsSub .

The form should demonstrate the following form controls:

· Image – an appropriate logo at the top of the form

· Label – an appropriate Form Title and SubForm Heading

· List Box (using a Value List in the Properties) – demonstrate for State Fields

· Buttons – demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

Note: Adjust the data Field width on the subform so that all columns fit on the main form using the Property Sheet – Format width option.

See the Beskeen et al textbook Access 2016 Chapter 11 and Access 2016 Chapter 12 and the Videos provided in the Course Content section for more details on creating forms

Dr Little has provided you with the following sample Invoice. The client requires you to develop a similar invoice for their business.

Using all Fields from Task 6 Query 5, create in the Report Wizard (using the Block Report Wizard Template) a single Report called rptCustomerInvoice that produces a professional looking invoice for the business.

· The report must be grouped by CustID.

· Each Customer’s invoice must be on a separate page.

See the Beskeen et al textbook Access 2016 Chapter 13 and Access 2016 Chapter 14 and the Videos provided in the Course Content section for more details on creating Reports

Note: It is expected that you will use the Baltzan et al and Beskeen et al textbooks to answer the topics Dr Lisa Chimes has given you for the presentation, as well as reputable online sources of information. You need to include a brief Bibliography formatted using the Harvard AGPS style on a separate page (no in-text referencing is required for the presentation) – see the following USQ website for referencing help: http://www.usq.edu.au/library/referencing/harvard-agps-referencing-guide .

Submission Guidelines

Attach the Access (any version from Access 2007 to current) & PowerPoint (any version from PowerPoint 2007 to current) Files using the naming convention below, to your online assignment submission in the Assignment 1 area on the CIS1000 StudyDesk before midnight Australian Eastern Standard Time (AEST) on the day the assignment is due.

1. [lastname] [initial] _ [student number] _ [course code] _ assign1.accdb (eg. genrichr_0050051005_cis1000_assign1.accdb).

2. [lastname] / [initial] _ [student number] _ [course code] _ assign1.pptx (eg. genrichr_0050051005_cis1000_assign1.pptx).

Note: Simply changing the File extension to .accdb or .pptx on an Access 2003 or PowerPoint 2003 File will not result in a suitable submission. If the assignment Files cannot be opened by the marker, it may be treated as late until a suitable replacement is received.

Upon completion of the submission process, check your uConnect email account for an automatically generated confirmation email (if you do not have an email account, print out the Submission Complete screen before exiting the Submission System). You must check that the File name and File size are listed correctly - if there is a problem with either, please email the course leader immediately.

If you have difficulties submitting through the StudyDesk Assignments submission tool, please review the Student Instructions document listed also on the CIS1000 StudyDesk. As a last resort only, email the course leader for instructions on an alternative course of action.

Page 3 | 13