+1443 776-2705 panelessays@gmail.com

Download the Fall_2019 database posted in the Access and Data Analytics Test Module under CONTENT on BeachBoard. 2.Ensure that primary keys are set and establish appropriate relationships among the tables: Stores, Vendors, Purchases, and Purchase_Items. Stores and Vendors should be linked to Purchases. Purchases should be linked to Purchase_Items. 3. Prepare the following queries, naming the queries qa, qb, qc, qd, corresponding to the identifying letters below: 

a. Use the purchase_items table to calculate the dollar amount of each item purchased in an extension query; name your new calculated field purchase_item_amount and format it appropriately. 

b. Use qa and the purchases table to sum the purchase item amounts for each purchase in an accumulation query; include all fields from the purchases table and the purchase_item_amount field from qa; name your summed field purchase amount and format it appropriately. 

c. Use qb and the vendors table to sum the purchase amounts from each vendor in another accumulation query; include vendor number, name, city, and state; name your summed field vendor purchases and format it appropriately. d. Use the qb query. Keeping all fields from qb, calculate the month of the purchase; name that field purchase month.  

ACCOUNTING INFORMATION SYSTEMS Access and Data Analytics Test

General Instructions.

This exam has four parts. Part 1 is in class. Parts 2, 3, and 4 are take-home. Submit all parts to the designated dropbox folder. I expect your individual effort on all parts. Parts 2 to 4 are described in a separate document.

Part 1 – Access (50 points).

To get full credit, you must set up appropriate relationships among the tables and enforce referential integrity for each link. Your queries must produce the correct values, the fields must by labeled and formatted appropriately, and query designs must not include extraneous tables. In other words, you should follow the list of fundamental rules for Access posted on BeachBoard and included at the end of this document for reference.

1. Download the Fall_2019 database posted in the Access and Data Analytics Test Module under CONTENT on BeachBoard.

2. Ensure that primary keys are set and establish appropriate relationships among the tables: Stores, Vendors, Purchases, and Purchase_Items. Stores and Vendors should be linked to Purchases. Purchases should be linked to Purchase_Items.

3. Prepare the following queries, naming the queries qa, qb, qc, qd, corresponding to the identifying letters below:

a. Use the purchase_items table to calculate the dollar amount of each item purchased in an extension query; name your new calculated field purchase_item_amount and format it appropriately.

b. Use qa and the purchases table to sum the purchase item amounts for each purchase in an accumulation query; include all fields from the purchases table and the purchase_item_amount field from qa; name your summed field purchase amount and format it appropriately.

c. Use qb and the vendors table to sum the purchase amounts from each vendor in another accumulation query; include vendor number, name, city, and state; name your summed field vendor purchases and format it appropriately.

d. Use the qb query. Keeping all fields from qb, calculate the month of the purchase; name that field purchase month.

BEFORE SUBMITTING, ask me to review your work. After I say that you are done, then submit your file to the BeachBoard DROPBOX. Be sure to close Access before you upload your results.

1

Some Fundamental Rules for Access

1. Look at your tables and think about what information those tables provide before you start linking tables and creating queries.

2. Make sure each table has a primary key designated. 3. Always establish relationships between tables first, before starting queries. 4. Always enforce referential integrity (or understand why you can’t). 5. No “expr1” field names. 6. Do not click on the big sigma to produce totals if the query doesn’t require totals (i.e., an

extension query). 7. Avoid “SumOf…” field names in accumulation queries. 8. Include identifying information in addition to the primary key in accumulation queries that

provide subtotals. 9. Always format new fields properly. 10. Always link every table and query used in your query. 11. Don’t include a table or query in your query unless you are using a field from that table or query. 12. Don’t include tables and queries in your query that contain basically the same information, e.g.,

you would not include a table and a query that extends that table in the same query. 13. Build on previous queries; don’t make the same calculation twice unless you have to. 14. Check the results from your queries to see if they seem reasonable.

2

,

flower_name purchase_price Inventory# qty_purch purchase#
Iris ¤ 6.12 F1010 62 FS001_P90001
Hydrangeas ¤ 6.06 F1008 126 FS001_P90002
Roses Red ¤ 6.65 F1019 71 FS001_P90003
Baby's Breath ¤ 1.58 F1001 118 FS001_P90004
Carnations White ¤ 3.12 F1005 31 FS001_P90005
Delphinium ¤ 4.41 F1007 126 FS001_P90006
Hypericum ¤ 5.61 F1009 153 FS001_P90007
Orchids ¤ 3.75 F1015 128 FS001_P90008
Roses Pink ¤ 5.39 F1018 82 FS001_P90009
Roses Red ¤ 6.79 F1019 43 FS001_P90010
Snapdragons ¤ 6.24 F1021 106 FS001_P90011
Hypericum ¤ 5.50 F1009 54 FS001_P90012
Orchids ¤ 3.79 F1015 42 FS001_P90013
Hypericum ¤ 5.61 F1009 107 FS001_P90014
Roses Yellow ¤ 6.12 F1020 54 FS001_P90015
Carnations White ¤ 3.00 F1005 62 FS001_P90016
Hydrangeas ¤ 5.76 F1008 113 FS001_P90017
Peonies ¤ 3.03 F1016 94 FS001_P90018
Roses Red ¤ 7.07 F1019 106 FS001_P90019
Peonies ¤ 2.94 F1016 43 FS001_P90020
Carnations Pink ¤ 2.40 F1003 158 FS001_P90021
Hypericum ¤ 5.72 F1009 118 FS001_P90022
Peonies ¤ 2.85 F1016 93 FS001_P90023
Roses Yellow ¤ 5.94 F1020 32 FS001_P90024
Carnations Red ¤ 2.94 F1004 82 FS001_P90025
Iris ¤ 6.24 F1010 86 FS001_P90026
Lilies Asiatic ¤ 6.44 F1011 180 FS001_P90027
Baby's Breath ¤ 1.49 F1001 52 FS001_P90028
Delphinium ¤ 4.41 F1007 119 FS001_P90029
Hydrangeas ¤ 6.12 F1008 63 FS001_P90030
Iris ¤ 6.30 F1010 82 FS001_P90031
Lilies Asiatic ¤ 6.57 F1011 127 FS001_P90032
Lisianthus ¤ 4.95 F1014 124 FS001_P90033
Snapdragons ¤ 6.18 F1021 41 FS001_P90034
Carnations Red ¤ 2.97 F1004 82 FS001_P90035
Carnations White ¤ 3.15 F1005 108 FS001_P90036
Lisianthus ¤ 4.90 F1014 54 FS001_P90037
Roses Mixed ¤ 5.00 F1017 31 FS001_P90038
Hypericum ¤ 5.61 F1009 106 FS001_P90039
Snapdragons ¤ 6.24 F1021 113 FS001_P90040
Tulips ¤ 6.83 F1023 95 FS001_P90041
Roses Mixed ¤ 4.85 F1017 86 FS001_P90042
Chrysanthemum ¤ 4.68 F1006 65 FS001_P90043
Carnations Red ¤ 2.97 F1004 43 FS001_P90044
Hypericum ¤ 5.23 F1009 53 FS001_P90045
Roses Yellow ¤ 5.70 F1020 73 FS001_P90046
Carnations White ¤ 2.85 F1005 71 FS001_P90047
Chrysanthemum ¤ 4.32 F1006 162 FS001_P90048
Baby's Breath ¤ 1.52 F1001 96 FS001_P90049
Carnations Pink ¤ 2.63 F1003 127 FS001_P90050
Lilies Asiatic ¤ 6.63 F1011 85 FS001_P90051
Roses Mixed ¤ 5.05 F1017 43 FS001_P90052
Sunflowers ¤ 3.15 F1022 103 FS001_P90053
Carnations Red ¤ 2.94 F1004 107 FS001_P90054
Carnations White ¤ 2.88 F1005 138 FS001_P90055
Chrysanthemum ¤ 4.28 F1006 42 FS001_P90056
Hypericum ¤ 5.45 F1009 82 FS001_P90057
Snapdragons ¤ 6.24 F1021 118 FS001_P90058
Carnations Pink ¤ 2.55 F1003 74 FS001_P90059
Carnations Red ¤ 3.09 F1004 63 FS001_P90060
Sunflowers ¤ 3.15 F1022 83 FS001_P90061
Tulips ¤ 6.24 F1023 53 FS001_P90062
Tulips ¤ 6.24 F1023 130 FS001_P90063
Baby's Breath ¤ 1.50 F1001 75 FS001_P90064
Carnations Mixed ¤ 2.16 F1002 76 FS001_P90065
Carnations Mixed ¤ 2.14 F1002 42 FS001_P90066
Carnations Red ¤ 2.94 F1004 106 FS001_P90067
Carnations White ¤ 3.12 F1005 52 FS001_P90068
Chrysanthemum ¤ 4.73 F1006 41 FS001_P90069
Lilies Colored Calla ¤ 7.35 F1012 74 FS001_P90070
Orchids ¤ 3.94 F1015 95 FS001_P90071
Lisianthus ¤ 5.05 F1014 128 FS001_P90072
Roses Yellow ¤ 5.88 F1020 54 FS001_P90073
Snapdragons ¤ 5.76 F1021 86 FS001_P90074
Baby's Breath ¤ 1.56 F1001 74 FS001_P90075
Carnations Mixed ¤ 2.16 F1002 106 FS001_P90076
Carnations Pink ¤ 2.60 F1003 31 FS001_P90077
Carnations White ¤ 2.91 F1005 31 FS001_P90078
Chrysanthemum ¤ 4.73 F1006 86 FS001_P90079
Lilies Asiatic ¤ 6.70 F1011 54 FS001_P90080
Lisianthus ¤ 4.75 F1014 41 FS001_P90081
Roses Pink ¤ 5.23 F1018 74 FS001_P90082
Lilies Asiatic ¤ 6.63 F1011 151 FS001_P90083
Peonies ¤ 3.06 F1016 53 FS001_P90084
Hydrangeas ¤ 5.70 F1008 42 FS001_P90085
Roses Mixed ¤ 4.90 F1017 118 FS001_P90086
Baby's Breath ¤ 1.49 F1001 315 FS001_P90087
Carnations Mixed ¤ 2.14 F1002 224 FS001_P90088
Delphinium ¤ 4.59 F1007 471 FS001_P90089
Lisianthus ¤ 4.95 F1014 242 FS001_P90090
Orchids ¤ 3.64 F1015 108 FS001_P90091
Tulips ¤ 6.70 F1023 211 FS001_P90092
Lilies Oriental ¤ 6.12 F1013 263 FS001_P90093
Lisianthus ¤ 4.75 F1014 201 FS001_P90094
Lisianthus ¤ 4.75 F1014 100 FS001_P90095
Baby's Breath ¤ 1.55 F1001 226 FS001_P90096
Carnations Red ¤ 2.91 F1004 110 FS001_P90097
Chrysanthemum ¤ 4.59 F1006 483 FS001_P90098
Hydrangeas ¤ 6.24 F1008 83 FS001_P90099
Hypericum ¤ 5.67 F1009 168 FS001_P90100
Lilies Asiatic ¤ 6.44 F1011 311 FS001_P90101
Lilies Oriental ¤ 6.06 F1013 156 FS001_P90102
Roses Mixed ¤ 5.10 F1017 116 FS001_P90103
Roses Red ¤ 7.07 F1019 140 FS001_P90104
Roses Yellow ¤ 6.12 F1020 312 FS001_P90105
Sunflowers ¤ 2.88 F1022 282 FS001_P90106
Tulips ¤ 6.83 F1023 328 FS001_P90107
Hydrangeas ¤ 6.00 F1008 97 FS001_P90108
Orchids ¤ 3.83 F1015 102 FS001_P90109
Carnations White ¤ 2.97 F1005 204 FS001_P90110
Lisianthus ¤ 4.75 F1014 21 FS001_P90111
Carnations White ¤ 3.00 F1005 95 FS001_P90112
Hypericum ¤ 5.56 F1009 22 FS001_P90113
Carnations Red ¤ 2.94 F1004 43 FS001_P90114
Chrysanthemum ¤ 4.68 F1006 86 FS001_P90115
Roses Yellow ¤ 6.06 F1020 103 FS001_P90116
Roses Mixed ¤ 5.05 F1017 104 FS001_P90117
Carnations Red ¤ 2.91 F1004 63 FS001_P90118
Lisianthus ¤ 5.20 F1014 63 FS001_P90119
Roses Yellow ¤ 5.70 F1020 107 FS001_P90120
Carnations Mixed ¤ 2.21 F1002 107 FS001_P90121
Lilies Asiatic ¤ 6.63 F1011 73 FS001_P90122
Lilies Oriental ¤ 6.12 F1013 130 FS001_P90123
Peonies ¤ 3.06 F1016 65 FS001_P90124
Roses Mixed ¤ 4.95 F1017 147 FS001_P90125
Roses Red ¤ 6.86 F1019 31 FS001_P90126
Tulips ¤ 6.18 F1023 122 FS001_P90127
Baby's Breath ¤ 1.44 F1001 83 FS001_P90128
Carnations Mixed ¤ 2.16 F1002 104 FS001_P90129
Roses Red ¤ 7.21 F1019 252 FS001_P90130
Sunflowers ¤ 3.09 F1022 62 FS001_P90131
Carnations Pink ¤ 2.48 F1003 75 FS001_P90132
Carnations Red ¤ 3.09 F1004 61 FS001_P90133
Delphinium ¤ 4.41 F1007 86 FS001_P90134
Hypericum ¤ 5.23 F1009 53 FS001_P90135
Lilies Asiatic ¤ 6.76 F1011 82 FS001_P90136
Roses Pink ¤ 5.34 F1018 42 FS001_P90137
Carnations Red ¤ 3.00 F1004 62 FS001_P90138
Chrysanthemum ¤ 4.37 F1006 125 FS001_P90139
Delphinium ¤ 4.73 F1007 86 FS001_P90140
Hypericum ¤ 5.34 F1009 74 FS001_P90141
Iris ¤ 5.76 F1010 42 FS001_P90142
Lisianthus ¤ 5.10 F1014 41 FS001_P90143
Orchids ¤ 3.56 F1015 103 FS001_P90144
Sunflowers ¤ 3.06 F1022 83 FS001_P90145
Carnations Mixed ¤ 2.25 F1002 175 FS001_P90146
Chrysanthemum ¤ 4.32 F1006 43 FS001_P90147
Hypericum ¤ 5.45 F1009 104 FS001_P90148
Lilies Asiatic ¤ 6.37 F1011 53 FS001_P90149
Lilies Oriental ¤ 5.82 F1013 52 FS001_P90150
Lisianthus ¤ 4.90 F1014 64 FS001_P90151
Orchids ¤ 3.71 F1015 133 FS001_P90152
Carnations Red ¤ 2.91 F1004 53 FS001_P90153
Baby's Breath ¤ 1.43 F1001 95 FS001_P90154
Carnations Red ¤ 2.97 F1004 93 FS001_P90155
Lilies Asiatic ¤ 6.50 F1011 96 FS001_P90156
Lilies Oriental ¤ 5.70 F1013 104 FS001_P90157
Roses Mixed ¤ 4.75 F1017 32 FS001_P90158
Delphinium ¤ 4.73 F1007 86 FS001_P90159
Lilies Oriental ¤ 5.76 F1013 106 FS001_P90160
Lisianthus ¤ 5.15 F1014 71 FS001_P90161
Peonies ¤ 3.06 F1016 43 FS001_P90162
Roses Red ¤ 6.93 F1019 117 FS001_P90163
Baby's Breath ¤ 1.50 F1001 130 FS001_P90164
Carnations Red ¤ 3.06 F1004 82 FS001_P90165
Chrysanthemum ¤ 4.55 F1006 43 FS001_P90166
Lilies Oriental ¤ 6.30 F1013 53 FS001_P90167
Lilies Asiatic ¤ 6.31 F1011 74 FS001_P90168
Roses Yellow ¤ 6.00 F1020 21 FS001_P90169
Peonies ¤ 2.91 F1016 52 FS001_P90170
Sunflowers ¤ 2.85 F1022 73 FS001_P90171
Lilies Oriental ¤ 6.12 F1013 42 FS001_P90172
Lilies Asiatic ¤ 6.24 F1011 64 FS001_P90173
Roses Red ¤ 6.93 F1019 139 FS001_P90174
Roses Yellow ¤ 6.06 F1020 75 FS001_P90175
Lisianthus ¤ 4.80 F1014 134 FS001_P90176
Carnations Pink ¤ 2.50 F1003 61 FS001_P90177
Chrysanthemum ¤ 4.68 F1006 43 FS001_P90178
Iris ¤ 6.24 F1010 119 FS001_P90179
Lilies Asiatic ¤ 6.50 F1011 117 FS001_P90180
Lilies Oriental ¤ 6.30 F1013 63 FS001_P90181
Peonies ¤ 3.03 F1016 85 FS001_P90182
Roses Mixed ¤ 5.25 F1017 92 FS001_P90183
Carnations Red ¤ 3.06 F1004 107 FS001_P90184
Delphinium ¤ 4.41 F1007 64 FS001_P90185
Lilies Oriental ¤ 5.82 F1013 21 FS001_P90186
Roses Pink ¤ 5.28 F1018 133 FS001_P90187
Carnations Pink ¤ 2.38 F1003 64 FS001_P90188
Carnations Pink ¤ 2.58 F1003 106 FS001_P90189
Orchids ¤ 3.60 F1015 71 FS001_P90190
Roses Yellow ¤ 5.76 F1020 52 FS001_P90191
Snapdragons ¤ 6.00 F1021 113 FS001_P90192
Carnations Pink ¤ 2.55 F1003 62 FS001_P90193
Carnations White ¤ 3.12 F1005 119 FS001_P90194
Delphinium ¤ 4.55 F1007 21 FS001_P90195
Peonies ¤ 3.06 F1016 53 FS001_P90196
Roses Yellow ¤ 6.30 F1020 62 FS001_P90197
Baby's Breath ¤ 1.53 F1001 74 FS001_P90198
Carnations Pink ¤ 2.53 F1003 106 FS001_P90199
Lilies Colored Calla ¤ 7.20 F1012 43 FS001_P90200
Lilies Oriental ¤ 5.70 F1013 93 FS001_P90201
Hypericum ¤ 5.50 F1009 31 FS001_P90202
Lilies Asiatic ¤ 6.37 F1011 103 FS001_P90203
Lilies Colored Calla ¤ 7.20 F1012 43 FS001_P90204
Orchids ¤ 3.79 F1015 21 FS001_P90205
Sunflowers ¤ 2.88 F1022 93 FS001_P90206
Snapdragons ¤ 5.88 F1021 61 FS001_P90207
Chrysanthemum ¤ 5.88 F1006 83 FS002_P90002
Hydrangeas ¤ 5.50 F1008 117 FS002_P90003
Iris ¤ 6.18 F1010 92 FS002_P90004
Carnations Pink ¤ 2.43 F1003 86 FS002_P90005
Chrysanthemum ¤ 3.09 F1006 63 FS002_P90006
Sunflowers ¤ 4.64 F1022 75 FS002_P90007
Lilies Asiatic ¤ 6.44 F1011 95 FS002_P90008
Orchids ¤ 3.94 F1015 113 FS002_P90009
Roses Pink ¤ 5.61 F1018 21 FS002_P90010
Sunflowers ¤ 3.00 F1022 51 FS002_P90011
Carnations Mixed ¤ 2.34 F1002 139 FS002_P90012
Carnations Red ¤ 3.12 F1004 92 FS002_P90013
Lilies Oriental ¤ 5.76 F1013 54 FS002_P90014
Roses Yellow ¤ 4.90 F1020 54 FS002_P90015
Lilies Asiatic ¤ 6.30 F1011 114 FS002_P90016
Roses Mixed ¤ 6.70 F1017 74 FS002_P90017
Lilies Oriental ¤ 5.76 F1013 21 FS002_P90018
Orchids ¤ 3.71 F1015 61 FS002_P90019
Carnations Mixed ¤ 2.27 F1002 53 FS002_P90020
Lilies Colored Calla ¤ 7.50 F1012 31 FS002_P90021
Lisianthus ¤ 5.10 F1014 32 FS002_P90022
Snapdragons ¤ 6.18 F1021 64 FS002_P90023
Carnations Pink ¤ 2.38 F1003 32 FS002_P90024
Delphinium ¤ 4.55 F1007 32 FS002_P90025
Lilies Colored Calla ¤ 7.73 F1012 128 FS002_P90026
Roses Mixed ¤ 5.20 F1017 31 FS002_P90027
Roses Red ¤ 7.07 F1019 32 FS002_P90028
Chrysanthemum ¤ 5.70 F1006 103 FS002_P90029
Roses Yellow ¤ 2.85 F1020 133 FS002_P90030
Carnations White ¤ 4.32 F1005 97 FS002_P90031
Delphinium ¤ 4.37 F1007 86 FS002_P90032
Hydrangeas ¤ 5.76 F1008 117 FS002_P90033
Hypericum ¤ 5.45 F1009 203 FS002_P90034
Lilies Oriental ¤ 6.06 F1013 171 FS002_P90035
Carnations Mixed ¤ 3.75 F1002 31 FS002_P90036
Orchids ¤ 2.16 F1015 84 FS002_P90037
Carnations White ¤ 3.03 F1005 32 FS002_P90038
Lilies Asiatic ¤ 4.41 F1011 62 FS002_P90039
Lilies Oriental ¤ 6.76 F1013 93 FS002_P90040
Carnations White ¤ 6.06 F1005 54 FS002_P90041
Chrysanthemum ¤ 2.94 F1006 31 FS002_P90042
Chrysanthemum ¤ 4.59 F1006 112 FS002_P90043
Delphinium ¤ 4.46 F1007 41 FS002_P90044
Roses Yellow ¤ 5.94 F1020 61 FS002_P90045
Lilies Oriental ¤ 6.24 F1013 62 FS002_P90046
Roses Pink ¤ 5.23 F1018 117 FS002_P90047
Carnations Mixed ¤ 2.18 F1002 74 FS002_P90048
Hydrangeas ¤ 5.94 F1008 116 FS002_P90049
Hypericum ¤ 5.23 F1009 94 FS002_P90050
Lilies Colored Calla ¤ 7.65 F1012 65 FS002_P90051
Peonies ¤ 3.12 F1016 147 FS002_P90052
Roses Pink ¤ 5.50 F1018 166 FS002_P90053
Tulips ¤ 6.18 F1023 42 FS002_P90054
Snapdragons ¤ 6.06 F1021 41 FS002_P90055
Sunflowers ¤ 2.91 F1022 43 FS002_P90056
Snapdragons ¤ 6.18 F1021 83 FS002_P90057
Sunflowers ¤ 3.09 F1022 52 FS002_P90058
Lilies Oriental ¤ 5.50 F1013 95 FS002_P90059
Snapdragons ¤ 6.00 F1021 105 FS002_P90060
Delphinium ¤ 5.82 F1007 94 FS002_P90061
Hypericum ¤ 4.32 F1009 71 FS002_P90062
Hypericum ¤ 7.07 F1009 104 FS002_P90063
Roses Red ¤ 5.72 F1019 82 FS002_P90064
Iris ¤ 5.88 F1010 104 FS002_P90065
Lilies Colored Calla ¤ 7.58 F1012 73 FS002_P90066
Snapdragons ¤ 3.83 F1021 63 FS002_P90067
Delphinium ¤ 5.88 F1007 31 FS002_P90068
Orchids ¤ 4.73 F1015 180 FS002_P90069
Hydrangeas ¤ 6.30 F1008 114 FS002_P90070
Roses Pink ¤ 6.00 F1018 41 FS002_P90071
Lilies Colored Calla ¤ 5.45 F1012 86 FS002_P90072
Lilies Oriental ¤ 7.58 F1013 97 FS002_P90073