ITEC101 Principles of Business Information Systems Assignment 2 Specification Module ACCESS (Databases) Due For Completion To be completed by the beginning of your class in week 8 Software Used Microsoft Access Module Assignment worth 7% (mark out of 100) In this assignment, you will: • Create a database called 2DAYFMSALES. • Create tables, fields, properties, and primary keys for 2DAYFMSALES. • Create links between the tables. • Enter records into the tables. • Do queries on the database and export the answers to a WORD document. • Do advanced queries. • Create a form from a query. • Update another persons database using their form. VERY IMPORTANT – PLEASE READ! When you see text that is underlined in this assignment, it indicates that you must make a decision based on the instructions in the assignment. If you see a question mark (?) this means that you need to find the data that needs to go in there. It is possible that yours will be different to your practical teachers and other students. Students should not copy other students and those found not making the decisions themselves and just copying other students will lose marks. Task 1: DESIGNING 2DAYFMSALES (Creating TABLES, FIELDS, ENTERING RECORDS, CREATING RELATIONSHIPS) 1. We want to create a database in Access called 2DAYFMSALES. So open Access and select Blank Database and name it 2DAYFMSALES. Then from the menu on the screen select Create – Table. This will allow you to create a new table for Clients. In design view (select View – Design View) fill in the fields and general properties with the following data, and then save the table as CLIENTS. The primary key for the table is ClientID: Field Name Data Type Description General Properties ClientID Text Client Identification Number Size 3 Format > Business Name Text Client's Business Name Size 40 Format > Contact Text Client's Contact Size 40 Format > Address Text Client's address Size 50 Format > Suburb Text Client's Suburb Size 30 Format > State Text Client's State Size 10 Format > Post code Text Client's Post code Size 20 Format > Phone Text Client's Phone Size 14 Input mask ? Type Text Client's Type Size 20 Format > You will need to ensure that the phone number is formatted (use input mask) correctly, with the following format: (xx) xxxx xxxx.   2. Remember to select the ClientID field and press the key button to make the field a primary key. 3. Create a new table again for Orders. In design view fill in the fields and general properties with the following data, and then save the table as ORDERS. You will need to determine what the primary key is for this table. Field Name Data Type Description General Properties OrderNo Text Order Number Size 3 Format > ClientID Text Client Identification Number Size 3 Format > timeID Text Time Identification Number Size 10 Format > Quantity Number Quantity of items Size Integer 4. Create a new table again for Timeslots. This will allow you to create a new table again for the individual time slots. In design view fill in the fields and general properties with the following data, and then save the table as TIMESLOTS. The primary key for the table is timeID:. In this table, you will need to determine what data types are applicable for the field. Based upon the description and the data you will be inputting. Field Name Data Type Description General Properties timeID ??? Time Identification Number Size 10 Format > Description ??? Time's Description Size 50 Format > Cost ??? Timeslot's Cost Available ??? Quantity available Size Integer 5. Create a new table again for Salesreps. This will allow you to create a new table again for Sales Rep who sold the Ad time. In design view fill in the fields and general properties with the following data, and then save the table as SALESREPS. In this table, you will need to determine what field names are applicable for the field. Based upon the description and the data you will be inputting. The primary key should be the field that has the description 'Sales Reps's Identification Number' Field Name Data Type Description General Properties ??? Text Sales Reps's Identification Number Size 3 Format > ??? Text Time Identification Number Size 10 Format > ??? Currency Sales Rep's commission amount ??? Text Region Size 2 Format > 6. Now you will have to fill in the tables. Start with CLIENTS – open the table in 'Datasheet view'. CLIENTS ClientID BusinessName Contact Address Suburb State Post Code Phone Type Client 1 AAA BIG W Peter 21 George St Liverpool NSW 2121 0255699688 Retail Client 2 BBB Ford Australia Peter 24 Market St Lakemba NSW 2122 0222534344 Manufact Client 3 CCC IKEA Tomi 67 Pitt St Banksia NSW 2121 0244998598 Retail Client 4 DDD DCS Tania 21 Clarence Ln Ryde NSW 2113 0285001122 Govern Client 5 EEE Energy Australia Sandy 98 Adam St BellevueHill NSW 2132 0255689787 Services Client 6 FFF Endeavour Health Paula 99 Fast St Penrith NSW 2147 0222589897 Medical For ORDERS Order1 Order2 Order3 Order4 Order5 Order6 OrderNo 1 2 3 4 5 6 ClientID AAA BBB AAA CCC BBB AAA timeID Breakfast Morning Afternoon Breakfast Hot30 Drive Quantity 15 20 30 40 50 60 Order7 Order8 Order9 Order10 OrderNo 7 8 9 10 ClientID AAA EEE DDD BBB timeID Afternoon Evening Afternoon Hot30 Quantity 70 20 30 60 For TIMESLOTS Item1 Item2 Item3 Item4 Item5 Item6 timeID Breakfast Morning Afternoon Drive Hot30 Evening Description 30 second ad between 6am and 9am 30 second ad at 8:30am 30 second ad at 12:30pm 30 second ad at 6pm 30 second ad at 7pm 30 second ad at 9pm Cost 250 180 200 350 400 90 Available 4000 2050 1350 1800 4500 6600 For SALESREPS Fields SalesRep 1 SalesRep 2 SalesRep 3 SalesRep 4 SalesRep 5 SalesRep 6 ?? V01 V02 V03 V04 V05 V06 ?? Breakfast Morning Afternoon Drive Hot30 Evening ?? 12.5 18 20 10 16 22 ?? NE W NE S NE SW Fields SalesRep 7 SalesRep 8 SalesRep 9 SalesRep 10 ?? V07 V08 V09 V10 ?? Breakfast Hot30 Afternoon Drive ?? 10 12.5 10 22 ?? NW N SW N Task 2: Creating Relationships Now you have created the tables for the database. The next step is to create the links for the tables. Go to Database Tools - Relationships, and under Show tables select all 4 tables to be shown. You will need to ensure that clientID is linked to clientID and timeID is linked to timeID. There should be 3 relationships in total. For each link make sure you have selected ONE to MANY button and ENFORCE REFERENTIAL INTEGRITY in the properties section on the screen. Task 3: QUERIES (40 marks – deduct 5 marks for each wrong query) You are now going to CREATE QUERIES through Query - new - design view: 1. Find all the Clients Business Name and Contact Name(sort in ascending order) that have a postcode of 2121 (do not show postcode in answer). Save the query as POSTCODELOOKUP. 2. Find all the Clients Business name (sort in descending order) and Contact name that live in a suburb that begins with the letter L. Save the query as BEGINWITHL. 3. Find all the Clients' Business Name (sort in ascending order) and Contact name that have their offices in NSW and their telephone number starts with "(02) 5" or "(02) 2". Save the query as TELEPHONELOOKUP. 4. Find all the Ads (timeID (in ascending order), description, quantity, and cost) that Client AAA or BBB have ordered. Combine the quantity for orders with the same ClientID. Save the query as SELECTEDORDERS. 5. What is the Sales Rep's ID that sold Afternoon? Include the cost and region of the Sales Rep in the query. Save the query as AFTERNOON. 6. Find all the Clients ID, Contact, suburb, and quantity ordered of those Clients who ordered more than 10 HOT30 (does not show "HOT30" in answer). Combine the quantity orders with the same ClientID. Save the query as HOT30. 7. Find Client's Business Name, Contact, and address that purchased Ads from a Sales Rep in the NE region. (Make sure that the relationship is intact). You must decide what to call this query based on what it is looking for. Task 4: Presentation (10 marks) Now produce a Word document with 2cm margins top bottom right and left. Put a header with your name and date and footer with page numbers. Now for each query which you have saved, I want you to copy-paste the results into this word document. To do that you will have to do Queries - Open for each query and select the answer and copy-paste it into Word.   Task 5: Form (20 marks) For this task you will need to create a form for the table Orders. You can use any colours and formatting you like, as well as any design. Please ensure the field "orderNo" is locked so that it cannot be altered by any user. You will be adding a new order into the database with the following details: Order No = 011 ClientID = EEE timeID = Morning Quantity = 20 You will need to take a screenshot of the new (using Snipping Tool) and copy and paste that into your Word Presentation document. Task 6: Advanced Queries (30 marks – 10 marks each) You are now going to CREATE ACTION and TOTAL QUERIES and USE EXPRESSIONS through Query - new - design view: Create the following queries: A. The total (SUM) of all the Ads in the 2DAYFMSALES database (use total query and find the SUM of the cost of the ads). i.e Available*Cost and sum the final total cost. Save the query as Adv A B. Update the cost of all ads by 8.1% (use an update query with the cost of the stock items). Save the query as Adv B C. Create a Query with the field name "Discount Cost". We are looking at having a discount cost query for our valued Clients. The discount will be 18.5% for all ads from the new inflated cost in query B (use expression builder if needed and create it the field name of a query; it will not appear in the table if you try to open the table). Save the query as Adv C Please have you assignment ready to be marked at the start of Week 8.