Access Marketing Assignment

This assignment is team-based and accounts for 15% of your overall course grade.

Marketing departments are among the most intense users of computing power in today’s information-rich world. Firms collect data on all sorts of different transactions in the hopes that analyzing these data will yield a treasure trove of useful facts and observations. These facts will allow firms to better understand their current/future markets with the ultimate goal of finding new ways of boosting their bottom line.

Part of the challenge facing marketing departments is in knowing what data to collect: garbage in, garbage out is a persistent issue. You will discover this challenge first hand in the data collection portion of this exercise since you are limited to ten (10) questions - so you cannot waste valuable space asking irrelevant/repetitious questions. Besides allowing you to appreciate the task of data collection and the role that IT plays in this task, we want you to use basic database tools (MS Access) to analyze the data from your survey.

Learning Objectives

You will learn about setting up a database using entity relationship diagrams (ERDs) and normalization and to then run a series of queries against the data to determine answers to some basic questions and to hopefully find hidden trends and patterns in the data that you might not have known in advance.

Project Choices

Your group may select one of the following projects. Alternatively, you are free to discuss with me your ideas for an alternative database project – I am open to any and all ideas.
  • Table Turnover: variables affecting the time spent in dining experiences. That is, you are a restaurant owner and you know that you make more money if you can server more customers. So you want to understand how to increase the number of people you can serve in an evening.
  • Impact of brand awareness on consumer/brand loyalty: A study using a specific product (ex. milk brands).
  • Effects of technology change on information seeking habits of individuals. That is, you want to examine how people find information and perhaps how they decide which information to trust.
  • Brand awareness and brand attitude as predictors of market share

NOTE: These are just suggestions for topics. There are several directions you could take any one of these suggestions. You may also use them as inspiration for a topic of personal/group interest.

There are some interesting ideas out there on the web. For example, I used this site in generating my options. Not all of the topics posted there will lend themselves to this assignment, but it may spark some ideas for your team.

Completing the Project (100 pts)

Access I - Survey Outline(15 pts)

Due: Sept. 26, 11:59 p.m. through Moodle

Access I.i - Release Survey

Once I have reviewed the first deliverable, you will be given the all-clear to activate and distribute your survey. Note that you may be asked to make changes to your survey questions before releasing it to the public.

Due: Oct. 3 Publish link in Wiki and distribute through other channels as appropriate.

Access I.ii - Close Survey

You require at least 100 responses, after cleaning up the data, so be sure to collect at least 110. The more responses you can collect, the better the results will be for your report. So do not stop just because you've gathered the minimum number of responses. However, in order to proceed to the report stage, we will need to cut off at some point.

Target Due date: Oct. 31

Access II - Clean Data (10 pts)

Due: Nov. 14, 11:59 p.m. through Moodle

Access III - Final Report(75 pts)

Due: Dec. 5, 11:59 p.m. through Moodle

Additional Information

As you think about database design, you should also think about what sorts of questions you would like to ask people on your survey so that their answers will help you to give a good explanation for what is going on. For example, in a coffee store example you may want to ask why people go to a store: prices, location, noise range, free Wi-Fi, menu options, etc. The demographic data questions we create are NOT included as part of the 10 survey questions you have to work with. Think about collecting demographic data also: age, gender, etc.?

Once the database is up and running, you will be expected to design and run some basic queries against the data. These queries will vary from one project to the next but, in each case, the objective is for you to present some form of report to a user. For example, why do people go to Starbucks (what’s the attraction?), do all Loyola students consider Loyola to be their first choice? These queries are what drive the database to produce knowledge and insights.


Some answers to common questions that students have asked.

What does an ERD look like?

An ERD or Entity Relationship Diagram, on paper, looks like two tables. The line linking the tables links the foreign key in Table 1 to the primary key in Table 2. Each table is like a spreadsheet when you look at it in what is called Datasheet View in Access. When you look at it in Design View in MS Access, it just shows the column headings. When two tables are linked, the foreign and primary keys are the same. The data in Table 1 is the data you collected via survey. The data in Table 2 is data that you researched. It’s up to you to figure out where the data in Table 2 should come from. The example below is based on: Why Loyola and not some other school?


NOTE: The textbook draws ERDs differently from what we'll be using for this class. You will want to refer to the lecture slides for help with this component rather than relying on the text.

How many variables do I need to have in my second table?

There is no magic number of variables. More variables allow you to potentially learn more interesting things about your sample… so more is better. You decide how many.

It’s 11pm and the project is due tomorrow and we can’t get our queries to run. What should we do?

There’s ample opportunity for you to learn how to use Access but you should not leave it until the last minute. There will be a full class session on how to use Access and a Friday afternoon tutorial to help you to run the actual queries. The only help I can offer for this situation is to consider the online learning tool Lynda.com accessible from Inside Loyola.

Should we stop at 8 queries or can we run more?

You can absolutely run more than 8 queries if you wish and in fact I would encourage you to do this. If you do, just present the results from the 8 most interesting queries in your final report. By extension, this means you do not need to stick to presenting just the original 8 queries from Part 1 of the assignment (even if I didn’t ask you to make changes).

What should the final report contain?

You should look at the specific required elements above for guidance here. When it comes to your discussion of the queries, though, I want you to tell me in English what the query was asking. Tell me in a table form, ideally, what you found, and tell me how you would interpret the result. Come at it from the point of view of having a hunch about something. You don’t know the answer so you run a query and the query results tell you whether what you suspected is true or false. Here’s an example of a table from: Why Loyola and not some other school?

Gender|Sample Size|Loyola 1st choice|Loyola 2nd choice|Loyola 3rd choice
Male 200 110 (55%) 70 (35%) 20 (10%)
Female 300 210 (70%) 70 (23%) 10 (7%)

Sometimes it is easier (and better) to interpret percentages rather than absolute numbers as the above example shows.

What does this SQL query statement thingy look like?

While I’ll show you how to find this information in Access, here’s an example of what the statement may look like:

SELECT Research.Name, Results.Gender, Count(Results.Gender) AS CountOfGender
FROM Research INNER JOIN Results ON Research.ID = Results.Med_Ins_Co_ID
GROUP BY Research.Name, Results.Gender;

To produce the data used in this table showing the number of respondents who hold medical insurance with each company, broken down by gender:
Gender Aetna Blue Cross Kaiser Permanente Medicare Other USAA Grand Total
Female 10 15 2 6 16 3 52
Male 7 16 3 9 15 4 54
Grand Total 17 31 5 15 31 7 106

No, you will NOT have to write SQL statements manually (unless you really want to). You don’t even need to understand the statement – it’s so that I can check on how you’ve used your data.

Contributors to this page: mlyeo .
Page last modified on Monday 02 of September, 2013 11:49:59 EDT by mlyeo.