Take a long excel file with too many rows and columns and create a small and simple csv file containing summary data.
The excel file will contain detailed accounts receivable billing data. We need this report to become a .csv file containing a customer number, current amount due, and total amount due.
The deliverable can be a MS Office 2003 product or be written in code. Doesn’t matter except that we don’t want to have to purchase separate software to make it work (like Crystal reports, Filemaker, etc.) or load .dll’s or .ocx’s and the like. Keep it simple.
Please no code to support Office 2007 ??" we don’t own it yet.? OS is Windows.? Access2003 app is fine as long as everything is as automated as possible.
I achieved the small file by taking the original file (see attached) and followed these specific steps:
Converted Customer ID field to number format
Converted Amount due field to currency format
Filter records: eliminate rows or records that have a null field in Invoice/CM field
With the remaining rows or records: create a data field for each Invoice/CM# containing the difference in days between the Date field and the Date Due field.
Next, create a data field for each Invoice/CM# indicating the age of the item ??" the difference between today’s date* and Date. *I may replace today’s date with a forced date off a form or input box…go ahead and code for that option.
If Age +> Terms, flag the item as “currently due??.
Finally, sum the amount of currently due for each Customer ID.
Sum the total due for each Customer ID.
Create one record for each Customer ID indicating the ID#, the total due, and the currently due amount.
Export it as a csv file to a directory location of my choosing (or default to My Documents/ARexport on a windows machine.
You don't have to go through the exact steps as I did as long as you get to the final product. I only added my flow as an example.