G O V E R N M E N T S E R V I C E S
Latarsha Hall
Account Manager, Defense Agencies
Efficient Data
Efficient Data
Management in
Management in
Agenda
Agenda
Introduction of Accounting Code Management Valid Values
Layout of the Valid Values File Upload Process
Questions
Introduction of Excel “Pivot Tables” Demonstration
G O V E R N M E N T S E R V I C E S
Valid value List (VVL) – List of values for an Accounting Code Segment
Example
– Segment: Appropriation
– Value: 9720082008XXXXXXXXXX
Upload process - Automated approach to loading large VVL files
– Adding values to a Child Account Validation Control (AVC)
– Creating a custom Valid Values List
Introduction
Upload File Requirements
Upload File Requirements
Required File Format
– Tab Delimited File (.txt)
– Start Menu Programs Accessories Notepad
Required Layout of Text
G O V E R N M E N T S E R V I C E S
Creating a Text File
Creating a Text File
Type them into the text file
If you have values list in electronic format already – copy and paste the row into the text file
G O V E R N M E N T S E R V I C E S
Creating a Text File
Copy & Paste
Creating a Text File
Creating a Text File
MAKE SURE:
No spaces with values
– (Helpful Tip: Use “Replace” function)
When you save the file – no spaces in the name of file (use underscore)
– OC Valid Values List (incorrect) – OC_Valid_Values_List (correct) – OCValidValuesList (correct)
Make sure file is closed before starting the auto-load process
G O V E R N M E N T S E R V I C E S
Notes
Notes
“File Format is invalid. Please select another file.”
Make sure there are no spaces in the name of the file Make sure there are no spaces with the values
– (Helpful Tip: Use “Replace” function)
Make sure right number of characters for the segment Make sure you’re using the correct format in your text file
Uses for Upload Process
Uses for Upload Process
Fiscal Year-End Cleanup
Activity Changes
EDI Conversion
G O V E R N M E N T S E R V I C E S
Using Excel Pivot Tables
(Version 2003)G O V E R N M E N T S E R V I C E S
What is a Pivot Table?
What is a Pivot Table?
A Pivot Table is a reporting tool that enables you to summarize and analyze data in lists and tables. Pivot Tables help you to quickly
rearrange the position of fields to give you different views of the table Pivot Tables:
Enable you to create different views of data in seconds
Organize and summarize your data
Compare and analyze your Access Online reports in many ways, turning data into meaningful information
Easily take the place of filtering, sorting, subtotaling, and function (sum, count, average)
How to Create a Pivot Table
How to Create a Pivot Table
Step 1: Select Entire Worksheet Step 2: Data - Pivot Table
G O V E R N M E N T S E R V I C E S
Keep in Mind!
Keep in Mind!
If your Field List disappears – just click on the Pivot Table again
If you try and it doesn’t work – right-click and choose “Show Field List”
If you want to e-mail - copy and paste into a new spreadsheet (Paste – Special)
If you add more data – don’t forget to update the pivot table by clicking the “Refresh Data” icon on the toolbar
Preparing your data (For non-Access Online Reports)
Preparing your data (For non-Access Online Reports)
Make sure the first row contains column labels since Excel will use this data for the field names in the report Check that each column contains only one sort of data—
(i.e. include text in one column and numeric values in a separate column)
There should be no empty rows or columns within the
range of data used for the report. (i.e. blank rows used to separate one block of data from another should be
removed)
G O V E R N M E N T S E R V I C E S
Pivot Table Quick Reference
Report Filter
G O V E R N M E N T S E R V I C E S
