This paper circulates around the core theme of 1.We want to create a database in Access called 2DAYFM SALES. together with its essential aspects. It has been reviewed and purchased by the majority of students thus, this paper is rated 4.8 out of 5 points by the students. In addition to this, the price of this paper commences from £ 99. To get this paper written from the scratch, order this assignment now. 100% confidential, 100% plagiarism-free.
Task 1: DESIGNING 2DAYFM SALES (Creating TABLES, FIELDS, ENTERING RECORDS, CREATING RELATIONSHIPS)
1.We want to create a database in Access called 2DAYFM SALES. So open Access and select Blank Database and name it 2DAYFM SALES. 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:
2.Remember to select the ClientID field and press the key button to make the field a primary key.
.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.
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
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.
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.
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
