Question

1 Approved Answer

Posted on Jul 17, 2024

I am not sure what this question is wanting me to do... Required Transform the data in accordance with the AICPA Base Standard, except as

I am not sure what this question is wanting me to do...

Required

Transform the data in accordance with the AICPA Base Standard, except as noted:
- For this transformation, you should import all of the data from each of the six files into a single repository. That means, regardless of the agency to which the data pertains, you should combine it into a single file.
- Because of this stipulation, the quantity of data is more than Excel can handle in a single sheet (i.e., there will be more than 1,048,576 lines of data). Thus, you should use Microsoft Access to create a database that lists all data in a single table.

As you prepare your Microsoft Access database, here are some important tips to keep in mind:
- Although the AICPA data standards suggest pipe-delimited files (that is, files that use a vertical line delimiter instead of a comma or other delimiter), use a comma-delimited or .csv file for all parts of this case.
- You should only use a single table for the data import.
- For the TransactionDate and PostedDate, use the format MM/DD/YYYY to make importing into Access easier. For example, May 4, 2002, would be formatted as 05/04/2002 (it is OK if the zeros preceding the month and date do not show up in Access).
- Not all fields are labeled as discussed in Task 2. Relabel data in all files using the labels that described the information in Task 2, but remove the spaces between the words (e.g., Agency Number should be AgencyNumber). Perform this step in Excel before importing the information into Access.
- Data might look different from year to year. You need to make it consistent across time.
- If data is missing for a particular field, leave it blank in your database.
When you finish the transformation, answer the following questions:
- How many rows of data are contained in your final file?
- What is the total of the amount column?
Upload a copy of your Access file for your professor for grading.

image text in transcribed

ACT557FilePCard TransactionsFY2010csv (Protected View] - Excel A kaylajcurtiss@gmail.com X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Share i PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X A1 X fx Agency Number D Document Recovery First Initial D Excel has recovered the following files. Save the ones you wish to keep. D D D x - ACT557CTOption1CloudyCo... Version created last time the... 10/25/2020 9:23 PM D C C C C A B 1 Agency Number Agency Name Last Name 2 2000 OKLAHOMA AC GRAVES 3 2000 OKLAHOMA ACI GRAVES 4 2000 OKLAHOMA ACI GRAVES 5 2000 OKLAHOMA ACI GRAVES 6 2000 OKLAHOMA AC GRAVES 7 2000 OKLAHOMA ACI AUTIN 8 2000 OKLAHOMA ACI AUTIN 9 2000 OKLAHOMA ACI AUTIN 10 2000 OKLAHOMA ACI AUTIN 11 2000 OKLAHOMA ACI AUTIN 12 2000 OKLAHOMA ACI AUTIN 13 2000 OKLAHOMA ACI AUTIN 14 2000 OKLAHOMA ACI AUTIN 15 2000 OKLAHOMA ACI AUTIN 16 2000 OKLAHOMA ACI AUTIN 17 2000 OKLAHOMA ACI AUTIN 18 2000 OKLAHOMA ACI AUTIN 19 2000 OKLAHOMA ACI AUTIN 20 2000 OKLAHOMA ACI AUTIN 21 2000 OKLAHOMA ACI AUTIN 22 2000 OKLAHOMA ACI AUTIN 23 2000 OKLAHOMA ACI AUTIN 24 2000 OKLAHOMA ACIAUTIN 25 2000 OKLAHOMA ACI AUTIN ACT557_File_PCard_Transactions F Item Description Amount AT&T SERVICE $289.89 GENERAL TRAN $79.95 FOLDER HANGII $77.95 GENERAL TRAN $19.95 GENERAL TRAN ($375.00) GENERAL TRAN $395.95 GENERAL TRAN $188.14 GEFORCE 8400 $102.57 GENERAL TRAN $75.98 GENERAL TRAN $14.99 GENERAL TRAN$84.90 GENERAL TRAN ($6.56) GENERAL TRAN $23.75 BINDING PLAS. $56.33 GENERAL TRAN $20.40 GENERAL TRAN$59.99 BINDING CVR L1$93.72 GENERAL TRAN $184.00 GCR2077311104 $368.00 256MB ATI RADI $80.29 GENERAL TRAN $109.00 GENERAL TRAN $200.20 4GB CF EXTREN $158.97 GCR2077311420 $396.00 G H 1 J Merchant Transaction Date Post Date Purchase Description ATTM *82787372 5-Aug-09 6-Aug-09 APPLE STORE 11-Aug-09 13-Aug-09 OFFICE DEPOT 11-Aug-09 13-Aug-09 WALKER STAMI 13-Aug-09 14-Aug-09 COUNCIL ON LIC 19-Sep-09 21-Sep-09 CALUMET CART 9-Nov-09 11-Nov-09 ULINE *SHIP SU 5-Nov-09 5-Nov-09 DMI* DELL K-12 19-Nov-09 19-Nov-09 STAPLES 00 19-Nov-09 20-Nov-09 DRI*WWW.ELEN 18-Nov-09 20-Nov-09 WAL-MART #280 16-Dec-09 17-Dec-09 WM SUPERCEN 16-Dec-09 17-Dec-09 WALKER STAMI 11-Dec-09 14-Dec-09 STAPLESCONTF 29-Dec-09 31-Dec-09 SOUTH CENTRA 7-Jan-10 11-Jan-10 STAPLES 00 12-Jan-10 13-Jan-10 STAPLESCONTE 30-Dec-09 4-Jan-10 THE JOURNAL 13-Jan-10 14-Jan-10 STAPLESCONTE 7-Jan-10 11-Jan-10 DMI* DELL K-12/ 19-Jan-10 19-Jan-10 SOURCEMEDIA 30-Dec-09 4-Jan-10 STAPLES ADVA 13-Jan-10 14-Jan-10 BEST BUY 01 1-Feb-10 2-Feb-10 STAPLESCONTE 16-Feb-10 18-Feb-10 C C c ? Which file do I want to save? C Close + B + 100% w X 9:44 PM 11/22/2020 D ACT557FilePCard TransactionsFY2010csv (Protected View] - Excel A kaylajcurtiss@gmail.com X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Share i PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X A1 X fx Agency Number D Document Recovery First Initial D Excel has recovered the following files. Save the ones you wish to keep. D D D x - ACT557CTOption1CloudyCo... Version created last time the... 10/25/2020 9:23 PM D C C C C A B 1 Agency Number Agency Name Last Name 2 2000 OKLAHOMA AC GRAVES 3 2000 OKLAHOMA ACI GRAVES 4 2000 OKLAHOMA ACI GRAVES 5 2000 OKLAHOMA ACI GRAVES 6 2000 OKLAHOMA AC GRAVES 7 2000 OKLAHOMA ACI AUTIN 8 2000 OKLAHOMA ACI AUTIN 9 2000 OKLAHOMA ACI AUTIN 10 2000 OKLAHOMA ACI AUTIN 11 2000 OKLAHOMA ACI AUTIN 12 2000 OKLAHOMA ACI AUTIN 13 2000 OKLAHOMA ACI AUTIN 14 2000 OKLAHOMA ACI AUTIN 15 2000 OKLAHOMA ACI AUTIN 16 2000 OKLAHOMA ACI AUTIN 17 2000 OKLAHOMA ACI AUTIN 18 2000 OKLAHOMA ACI AUTIN 19 2000 OKLAHOMA ACI AUTIN 20 2000 OKLAHOMA ACI AUTIN 21 2000 OKLAHOMA ACI AUTIN 22 2000 OKLAHOMA ACI AUTIN 23 2000 OKLAHOMA ACI AUTIN 24 2000 OKLAHOMA ACIAUTIN 25 2000 OKLAHOMA ACI AUTIN ACT557_File_PCard_Transactions F Item Description Amount AT&T SERVICE $289.89 GENERAL TRAN $79.95 FOLDER HANGII $77.95 GENERAL TRAN $19.95 GENERAL TRAN ($375.00) GENERAL TRAN $395.95 GENERAL TRAN $188.14 GEFORCE 8400 $102.57 GENERAL TRAN $75.98 GENERAL TRAN $14.99 GENERAL TRAN$84.90 GENERAL TRAN ($6.56) GENERAL TRAN $23.75 BINDING PLAS. $56.33 GENERAL TRAN $20.40 GENERAL TRAN$59.99 BINDING CVR L1$93.72 GENERAL TRAN $184.00 GCR2077311104 $368.00 256MB ATI RADI $80.29 GENERAL TRAN $109.00 GENERAL TRAN $200.20 4GB CF EXTREN $158.97 GCR2077311420 $396.00 G H 1 J Merchant Transaction Date Post Date Purchase Description ATTM *82787372 5-Aug-09 6-Aug-09 APPLE STORE 11-Aug-09 13-Aug-09 OFFICE DEPOT 11-Aug-09 13-Aug-09 WALKER STAMI 13-Aug-09 14-Aug-09 COUNCIL ON LIC 19-Sep-09 21-Sep-09 CALUMET CART 9-Nov-09 11-Nov-09 ULINE *SHIP SU 5-Nov-09 5-Nov-09 DMI* DELL K-12 19-Nov-09 19-Nov-09 STAPLES 00 19-Nov-09 20-Nov-09 DRI*WWW.ELEN 18-Nov-09 20-Nov-09 WAL-MART #280 16-Dec-09 17-Dec-09 WM SUPERCEN 16-Dec-09 17-Dec-09 WALKER STAMI 11-Dec-09 14-Dec-09 STAPLESCONTF 29-Dec-09 31-Dec-09 SOUTH CENTRA 7-Jan-10 11-Jan-10 STAPLES 00 12-Jan-10 13-Jan-10 STAPLESCONTE 30-Dec-09 4-Jan-10 THE JOURNAL 13-Jan-10 14-Jan-10 STAPLESCONTE 7-Jan-10 11-Jan-10 DMI* DELL K-12/ 19-Jan-10 19-Jan-10 SOURCEMEDIA 30-Dec-09 4-Jan-10 STAPLES ADVA 13-Jan-10 14-Jan-10 BEST BUY 01 1-Feb-10 2-Feb-10 STAPLESCONTE 16-Feb-10 18-Feb-10 C C c ? Which file do I want to save? C Close + B + 100% w X 9:44 PM 11/22/2020 D