2 short projects

Lesson 9 Project: Access: Generate Word Memo from Access Data

 

            This Lesson Project supports Student Learning Outcome 2 from your Course Syllabus.

 

Purpose:

 

            The purpose of this project is to work individually or in a group to use a database provided to you to prepare a written report based on results of queries to retrieve data.

 

Requirements:

 

1.    Overall Process:

a.    Your instructor will let you know whether this will be an individual or group process.

b.    You will be provided a link that contains a Census database you will use to find information

c.    You will then be asked some questions that require you to find answers in the database using queries.

d.    Finding answers will require reports and queries that you have to design. You will save these items in the database and turn it back in.

e.    Lastly, you will turn your results into Memo using Microsoft Word to present the answers in a straightforward and professional manner for "management".

 

2.    Starting Details

a.    Find the Census Database link below and download the file to your computer. It is named Project9Access.accdb

b.    Rename the file to Project9LastNameLastNameLastName.accdb immediately - prior to opening the file in Access

                                          i.    Use the last name of everyone in the group. As an example, the filename might be Project9SmithJonesFlintstone.accdb

c.    Open the database using Microsoft Access software.

d.    Create a new table and name it using your Team Members last names.

e.    Create 3 fields:

                                          i.    FullName 

                                        ii.    CourseAndSection

                                       iii.    DayOfBirth (only the day). If you were born on Dec 18th, then enter a value of 18.

f.     Generate an input form for this new table

g.    Be sure to save the form

h.    Enter the data for yourself and your group members, if you are in a group.

i.      Verify the fields are sized reasonably for the amount of data entered. Nothing should be cut off.

j.      You should see the tables listed on the left side of the window, double click on the CensusData table

k.    Have a look around to get an overview of the contents of the fields

                                          i.    The WhatState field is a number assigned by US Census. This is the primary key for the database

                                        ii.    The State field is the full name of the state

                                       iii.    The columns 2000, 2001, 2002, etc represent the number of people reported living in that state at the start of that year

                                       iv.    The field labeled Census is the official census taken in the year 2000

                                        v.    Switch to Design View to see the structure of the table “CensusData” along with the field sizes

l.      Select a State Code that is the same as the day you (or one of you) were born.

                                          i.    If you were born on Dec 18th, then select the state with a value of 18 in the whatstate field.

 

3.    At this point, you will need to get into the database and find some information to answer some questions. There are 3 ways to do this:

a.    You can pick through the entire table looking for answers. That takes a lot of time, and will give you zero points in this exercise.

b.    You can develop fully automatic queries or reports that just search the database and pop the answer right out for you. Something an Access expert could do. Not necessary.

c.    You can take the middle ground - make a query to have the database reduce the data to what you need.

 

This (option c) is what you will do. It's called data reduction, and it's VITAL to modern business operations. No one has the time to look through 500 pages of raw data, so you have the database reduce it down to your required information.

 

You must produce, and save (with an appropriate name, not "Query1") a query or a form for each item where information is needed.

 

We must be able to look at your query, form and report to see how you found your answers. This way we know you didn't just pick through the main table!

 

This query or form or report must contain the way you got your answer.

 

Examples:

 

                                          i.    If we want to know the state with the highest population in 2007, you could do a query of all states population for the year 2007 and note the state with the highest for the memo.

                                        ii.    If we wanted to find the population difference between 2 states in 2002, generate a query with just those two states for that year, to quickly determine the difference

                                       iii.    You can use queries with math functions - like min( ), or count(), or logic like AND or OR.  Remember, you can also sort by field.

 

4.    Here are the items you must find (with a form or query for each one):

a.    For your selected state, find the population in 2002 and 2007.

b.    For your selected state, find the population in the years 2006 and Census field

c.    Now, for ALL states:

                                          i.    How many states had over 5,000,000 people in 2000? (hint: use a function)

                                        ii.    What 5 states had the smallest number of people in 2004?

                                       iii.    What state had the highest population in 2009?

                                       iv.    How many states have the same first letter as your state? (hint: use a “like” search criteria)

                                        v.    What was the average population of the 5 smallest states in 2008?

 

(You should have a table, a form, and seven queries)

 

5.    Do a little math with your calculator (or maybe Excel or even an Access query):

a.    Find the increase (+ number) or decrease (-number) in population for your selected state between 2002 and 2007.

b.    Find the percent increase or decrease in population of your selected state between Census (field) and 2006. (Hint: find the difference between the 2 years and divide difference by population in 2000 and then multiply by 100)

 

6.     Your work with the database is done! Now, you have to put this in a memo for easy reading by the "boss" stating all your findings from above (as if this were the only thing you gave him … not the database).

a.     Use Microsoft Word.

b.    The report should be business memo format.  You can find several memo templates for this within Word itself.

c.    Summarize all your findings using full sentences (not just answers) and your conclusions from the database (questions 4 and 5) as if the memo was all you were giving your boss (not the database).

d.    Type on the memo the names of all your group members.

e.    Use your Instructor's Name in the salutation.

f.     Add charts or graphs for at least two queries generated from the database

g.    Name the file Project9MemoLastNameLastNameLastName.docx

                                          i.    Use the last name of everyone in the group. As an example, the filename might be Project9MemoSmithJonesFlintstone.docx

h.    Spell and grammar check, of course.

 

7.    The following Grading Rubric will be used to evaluate your work. Use it to help maximize your score!

 

Requirements	Points
Generate the Team Member Table, Form and enter your name(s), etc.
Renamed Access DB according to instructions 2B	1
Created and Named Table correctly 2D	5
Created and Named Fields correctly 2E	6
Created the Input Form for data into new table 2F	6
Entered the correct data 2H	4
Generate the required queries or reports
Query for 4A 2002 & 2007	4
Query for 4B 2006 & Census	4
Query for 4C1 states over 5 mil pop in 2000	4
Query for 4C2 5 smallest pop in 2004	4
Query for 4C3 state with largest pop in 2009	4
Query for 4C4 number of states start with same letter	4
Query for 4C5 average population of the 5 smallest states in 2008	4
Access items (queries, etc..) have meaningful names to identify purpose	10
Word Memo format used in a professional manner	10
Details and query results are included in Word memo
List results of all the queries from Access DB Questions done in part 4.	10
Give answer, show calculation, & a chart/graph for one query	5
Give answer, show calculation, & a chart/graph for another query	5
Spelling and grammar are correct throughout Word memo	10
Total	100