MAN 4322
Spring 2018
MS Access Project Instructions
Please note the MS Access only works on Windows machines. You will need access to a machine with
Microsoft Office installed and will use both Microsoft Excel and Microsoft Access.
Helpful video to get you started on how to import Excel data into MS Access.
1. Download the three files provided in Canvas.
Ethnic_info.xls, personal_data.xls and phone_info.xls
2. Create a new MS Access Database. Name the database “PID_msproject” where “PID” is your
PantherID. If your PID is 1234567 then the database name will be “1234567_msproject”.
3. Import the three files into MS Access and name the tables the same names as the file names.
Validate that each emplid key is of type “Short Text”.
You do that by right clicking on the table and selecting “Design View
Then checking on the Data Type as shown below.
4. Create the following queries:
a. Query_Ethnic_Info ( Join personal_data with ethnic_info – Inner Join)
b. Query_Phone_Info ( Join personal_data with phone_info – Inner Join)
c. Query_All_EE_Phone_Info ( Join personal_data with phone_info – Outer Join)
Useful video on how to do an inner join and outer join.
At this point you show have the following in your database:
5. Export Queries to Excel.
Questions to answer
How to create a Pivot Table
1. How many tables did you import?
2. How many records for each table? (i.e. Table name and number of rows)
3. How many rows for Query_ethnic_Info?
4. How many rows for Query_phone_Info?
5. How many rows for Query_All_EE_Phone_Info?
6. Using the Query_Ethnic_Info excel spreadsheet create a Pivot Table with Sex as a Row and
Count of Emplid as Sigma Values to answer the following:
a. How many Females?
b. How many Males?
c. How many Unspecified?
7. Using the Query_Ethnic_Info excel spreadsheet create a Pivot Table with City as a Row, Sex as
Columns and Count of Emplid as Sigma Values to answer the following:
a. How many Employees live in Miami?
b. How many are female?
c. How many are male?
8. Using the Query_Phone_Info excel spreadsheet create a Pivot Table with Phone_Type as a Row
and Count of Emplid as Sigma Values to answer the following:
a. What are the top three Phone Types?
b. How many Home numbers are there?
c. What is the second largest City for Phone_Type that equal HOME?
i. (Hint: Need to use Filters)
d. How many Home numbers do we have for that City?
9. Using the Query_All_EE_Phone_Info excel spreadsheet create a Pivot Table with a Filter using
only Area_Code (305, 786 and 954) with Sex as a Row, Phone_Type as a Column and answer the
following:
a. How many Home nu
Sheet 1
EMPLID ETHNIC_GRP_CD DESCR50
phghggg BLACK Black/African American
jighggg HISPA Hispanic/Latino
mighggg HISPA Hispanic/Latino
ijghggg HISPA Hispanic/Latino
njghggg HISPA Hispanic/Latino
gkghggg WHITE White
klghggg WHITE White
gmghggg WHITE White
imghggg HISPA Hispanic/Latino
nmghggg HISPA Hispanic/Latino
pmghggg HISPA Hispanic/Latino
inghggg HISPA Hispanic/Latino
mnghggg HISPA Hispanic/Latino
loghggg WHITE White
moghggg WHITE White
poghggg WHITE White
kpghggg WHITE White
lpghggg HISPA Hispanic/Latino
npghggg HISPA Hispanic/Latino
lghhggg HISPA Hispanic/Latino
mghhggg HISPA Hispanic/Latino
nghhggg BLACK Black/African American
hhhhggg HISPA Hispanic/Latino
jhhhggg WHITE White
khhhggg HISPA Hispanic/Latino
ohhhggg ASIAN Asian
ojhhggg HISPA Hispanic/Latino
hlhhggg HISPA Hispanic/Latino
ilhhggg HISPA Hispanic/Latino
klhhggg WHITE White
nlhhggg WHITE White
hmhhggg HISPA Hispanic/Latino
mmhhggg HISPA Hispanic/Latino
hnhhggg WHITE White
jnhhggg HISPA Hispanic/Latino
lnhhggg WHITE White
mnhhggg HISPA Hispanic/Latino
pnhhggg WHITE White
kphhggg WHITE White
pphhggg HISPA Hispanic/Latino
ggihggg HISPA Hispanic/Latino
hgihggg BLACK Black/African American
jgihggg HISPA Hispanic/Latino
kgihggg BLACK Black/African American
mgihggg WHITE White
ogihggg WHITE White
ihihggg WHITE White
jhihggg HISPA Hispanic/Latino
lhihggg HISPA Hispanic/Latino
nhihggg HISPA Hispanic/Latino
hiihggg ASIAN Asian
liihggg HISPA Hispanic/Latino
miihggg HISPA Hispanic/Latino
ojihggg HISPA Hispanic/Latino
jkihggg HISPA Hispanic/Latino
mkihggg HISPA Hispanic/Latino
gmihggg ASIAN Asian
imihggg HISPA Hispanic/Latino
jmihggg HISPA Hispanic/Latino
pmihggg HISPA Hispanic/Latino
knihggg HISPA Hispanic/Latino
ggjhggg BLACK Black/African American
jgjhggg WHITE White
gijhggg WHITE White
lijhggg HISPA Hispanic/Latino
ljjhggg HISPA Hispanic/Latino
gkjhggg BLACK Black/African American
jkjhggg HISPA Hispanic/Latino
okjhggg HISPA Hispanic/Latino
gmjhggg WHITE White
pmjhggg HISPA Hispanic/Latino
gnjhggg HISPA Hispanic/Latino
hnjhggg HISPA Hispanic/Latino
jnjhggg HISPA Hispanic/Latino
lnjhggg HISPA Hispanic/Latino
mnjhggg HISPA Hispanic/Latino
nnjhggg HISPA Hispanic/Latino
kojhggg HISPA Hispanic/Latino
lojhggg HISPA Hispanic/Latino
mojhggg HISPA Hispanic/Latino
nojhggg HISPA Hispanic/Latino
oojhggg HISPA Hispanic/Latino
npjhggg HISPA Hispanic/Latino
opjhggg ASIAN Asian
ppjhggg WHITE White
hgkhggg HISPA Hispanic/Latino
kgkhggg HISPA Hispanic/Latino
lgkhggg HISPA Hispanic/Latino
mgkhggg HISPA Hispanic/Latino
ngkhggg HISPA Hispanic/Latino
ogkhggg HISPA Hispanic/Latino
pgkhggg HISPA Hispanic/Latino
ghkhggg HISPA Hispanic/Latino
hhkhggg HISPA Hispanic/Latino
ihkhggg WHITE White
likhggg HISPA Hispanic/Latino
mikhggg HISPA Hispanic/Latino
nikhggg BLACK Black/African American
oikhggg HISPA Hispanic/Latino
Sheet 1
EMPLID LASTNAME COUNTY COUNTRY CITY STATE POSTAL SEX MAR_STATUS
pkjhnmi da58zwC MIAMI DADE USA CUTLER BAY FL 33189 F M
giklghg .zJ z5aa1M VOLUSIA USA ORMOND BEACH FL 32174 M S
glmmmih s8a12W BROWARD USA FT LAUDERDALE FL 33311 F S
iomjhkh ywb5L BROWARD USA MIRAMAR FL 33025 F S
nnmgglh daa9G DADE USA MIAMI FL 33177 F S
jjngglh d693a5D DADE USA HIALEAH GARDEN FL 33018 F S
nnjhglh f56c9cz5H f56c9cz5H BROW USA FT LAUDERDALE FL 33311 F S
pnhjoii y9w356dzz9C MIAMI DADE USA HIALEAH FL 33012 F S
jighggg f5z9wS MIAMI DADE USA MIAMI FL 33185 M S
mpmkgmi fwzC 9L 5D MIAMI DADE USA MIAMI FL 33165 F S
njilhgh 5a5271M 5D MIAMI DADE USA MIAMI FL 33185 F S
gkjgoih d6z9P DADE USA MIAMI FL 33135 M M
lolgoih 917z9G DADE USA MIAMI FL 33150 F S
nhngoih f5z5P DADE USA HIALEAH FL 33012 F M
iojgngh t1aA DADE USA MIRAMAR FL 33027 F S
inngngh 189cdB DADE USA MIAMI FL 33196 M S
pojnlmi a58z9eS MIAMI DADE USA DAVENPORT IA 52804 F S
ggloiki y5z9v1aO ORANGE USA ORLANDO FL 34743 F S
noojnoi 51zbdC MIAMI DADE USA LAUDERHILL FL 33319 F M
gkioloi 51fc5K7M BROWARD USA TAMARAC FL 33321 F S
hjgikhh z5bz9F DADE USA MIAMI FL 33156 M M
nghikhh 54a97x5M DADE USA MIAMI FL 33155 M S
jpkikhh f5edL DADE USA MIAMI FL 33186 F S
mhojkhh z98dL BROW USA PLANTATION FL 33324 F M
gmikkhh f5w31z6dR DADE USA MEDLEY FL 33166 F M
jjmjnkh y5c9a1M DADE USA MIAMI FL 33177 F S
lgphpjh d3z9b9C BROWARD USA DAVIE FL 33328 F S
ijmomlh z527x5aF DADE USA NORTH MIAMI FL 33181 F S
hipighg xx5yydzC MIAMI DADE USA NORTH MIAMI FL 33160 F S
jgjlgjh f5c1xz9M 935xzO DADE USA MIAMI FL 33182 M M
gmolgjh 5zwD BROW USA PLANTATION FL 33322 F S
hnkgnjh 93c5M DADE USA AVENTURA FL 33160 F S
nolgnjh y9ad71N c9S DADE USA MIAMI FL 33165 F S
gjghnjh 9fda9c5P DADE USA MIAMI FL 33173 M S
mophjih 905aa9C f5edL DADE USA MIAMI FL 33156 M M
njiijih -ddC DADE USA MIAMI FL 33155 F S
ipkijih wW USA SAN JOSE CA 95132 M M
pjloiji yc1aa1B BREVARD USA PALM BAY FL 32909 F S
kjkhjjh dz9B MIAMI DADE USA MIAMI FL 33015 F M
pkpgnmh 1yd-dT MIAMI DADE USA MIAMI FL 33172 F S
poihhih c93dH PALM USA LAKE WORTH FL 33467 M M
nljhhih 9xzO DADE USA MIAMI SPRINGS FL 33166 M H
pokhhih cdyz56cA DADE USA MIAMI FL 33150 F S
lhnhhih y1da9V DADE USA MIAMI FL 33182 F S
migihih f91D DADE USA MIAMI FL 33144 F S
jmgjmii 5c1C DADE USA MIAMI FL 33179 F S
jnhjmii fx59u9L DADE USA MIAMI FL 33138 F S
mnhjmii s5aydM DADE USA MIAMI FL 33189 M S
jlkpijh 9c165M DADE USA MIAMI FL 33174 M S
joopijh 3c1K DADE USA MIAMI FL 33186 M M
ihggjjh f5a9fcdG DADE USA MIAMI FL 33184 F M
nolgjjh z92-z9-52S DADE USA MIAMI FL 33125 F M
hjghogh fdcwM DADE USA MIAMI FL 33155 F S
mpghogh 9z5z89C DADE USA MIAMI FL 33196 F S
khplnli d8da9aa1V DADE USA MIAMI FL 33193 M M
njpmnli 935xzO DADE USA MIAMI FL 33175 M S
jomnnli 8bd7x12W NFLA USA ANNAPOLIS MD 21403 M M
momnnli 5x19u2x9zB DADE USA MIAMI FL 33174 M S
jkpglph 1w35xy5zA d05czdC MIAMI DADE USA MIAMI FL 33172 F M
gjjo
Sheet 1
EMPLID PHONE_TYPE AREA_CODE
gjpkglh HOME 786
gjpkglh BUSN 305
hjpkglh HOME 954
hjpkglh BUSN 305
imjlglh HOME 305
imjlglh BUSN 305
khnlglh BUSN 305
onhmglh HOME 954
onhmglh BUSN 305
mlgnglh HOME 305
mlgnglh BUSN 305
hiomkhh HOME 305
hiomkhh BUSN 305
gjgojjh HOME 305
gjgojjh CELL 305
lghhhki CELL 786
hkinkmi HOME 305
hkinkmi BUSN 305
hkinkmi CELL 786
mnmoipi BUSN 305
mnmoipi CELL 786
kjmjghg HOME 305
ommggli HOME 440
ommggli CELL 440
oipoghg CELL 604
mpmloih HOME 305
mpmloih CELL 305
oknjpii HOME 305
mpgomjh CELL 305
nlpoghg CELL 301
mommplj HOME 404
oglkppi HOME 305
oglkppi CELL 305
lhmoglh HOME 305
kmmihkh CELL 305
lhgpghg CELL 305
kilpgmi HOME 305
hpkliih HOME 305
hpkliih CELL 786
hpkliih BUSN 305
nnjiggg HOME 305
kgiiiih HOME 305
kgiiiih BUSN 305
npjiiih HOME 305
npjiiih BUSN 305
nipiiih HOME 954
nipiiih BUSN 305
koioigh HOME 305
koioigh BUSN 305
iljoigh HOME 561
iljoigh BUSN 305
ngmpnjh HOME 305
ngmpnjh CELL 305
koggojh HOME 305
koggojh BUSN 305
khkpkji HOME 305
khkpkji BUSN 305
jogngii HOME 954
jogngii CELL 954
jogngii BUSN 305
pkkilji HOME 786
pkkilji BUSN 305
gipkpji HOME 305
nlgnpji HOME 954
nlgnpji CELL 954
nlgnpji BUSN 305
onnjgjh HOME 305
lohgmgi HOME 305
nkjhghg HOME 305
ignikli HOME 305
pnnikli HOME 754
pnnikli BUSN 305
lojghjh HOME 305
lojghjh BUSN 305
oplghjh HOME 305
ogigghi HOME 786
ogigghi CELL 786
ogpiipi HOME 352
ogpiipi BUSN 305
ogpiipi CELL 352
nolopgh HOME 954
nolopgh BUSN 305
nolopgh CELL 202
hkignli CELL 305
kipmjhh HOME 305
kipmjhh BUSN 305
njhnjhh HOME 954
njhnjhh BUSN 305
imngkih HOME 786
imngkih BUSN 305
hkipghg HOME 305
hkipghg CELL 305
jlipghg CELL 305
jpomoki CELL 305
hommjki HOME 239
hommjki CELL 786
ppgipnj HOME 305
ppgipnj CELL 305
inipghg HOME 847
inipghg BUSN 305
hjjmjgj CELL 786
lpipghg HOME 917
kmnkkgi CELL 469
gmjkkmi CELL 954
kkojnpi CELL 786
jpnoohj HOME 305
jpnoohj CELL 786
lpmoohj HOME 786
jlhlpjj HOME 786
gjignli HOME 305
nhiimmh HOME 954
nhiimmh CELL 786
olognlh HOME 954
linlmmh HOME 954
linlmmh CELL 954
phijgjh HOME 305
phijgjh BUSN 305
kkljgjh HOME 954
kkljgjh BUSN 305
khophlh HOME 305
khophlh BUSN 305
jppgilh HOME 305
jppgilh BUSN 305
hoghilh HOME 305
hoghilh BUSN 305
olmgoih HOME 305
jgljjji HOME 813
goknpgi HOME 305
kknlggj HOME 505
gjoinoi HOME 305
nnhnnli CELL 786
mnnighg HOME 305
mnnighg CELL 305
jigllgi HOME 305
jigllgi BUSN 305
hiojghg HOME 954
hiojghg CELL 954
ikoonmh CELL 305
ikoonmh BUSN 305
hhphjkh HOME 561
pnihnih HOME 305
gogigkj HOME 608
hplphjh HOME 305
hplphjh CELL 305
kojlggj CELL 717
jihlipi HOME 954
jihlipi CELL 954
mgiihgj HOME 757
mggnmlh HOME 954
mggnmlh BUSN 305
ngojngi HOME 786
mjjnlmi HOME 305
hnppljh HOME 786
hnppljh BUSN 305
hnhlhph HOME 305
jnlhghg HOME
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.