Revised 7/8/04
A database has been devised by the Financial Management Unit (FMU) to allow financial clerks in the branches to enter and balance receipts prior to deposit in the 10008 Client Trust Account. The balanced receipts are then downloaded onto a Microsoft Excel spreadsheet and emailed to the Central Office.
Software requirements are: Microsoft Access 97 or 2000, Microsoft Excel, and access to email.
User knowledge requirements are: Familiarity with Microsoft Access 97 and its entry conventions, ability to attach files to email.
When you receive your copy of the database, save the database to a shared drive on your local area network (LAN). Open the database with Microsoft Access 97 and prepare the database by deleting the table “Change Auto Number” and the append query “Change Start Number”. These objects are there to allow the FMU to set the beginning receipt number for each branch.
Then, enter the design view of the Form “AFS29”. Right click on the “Clerk” field, and choose “Properties. Under the data tab, change the list in the Row Source to the financial clerks at your branch, using semi-colons as separators.
You will also want to set up a special folder in your Windows environment for your exported deposits. It should be on a shared drive. If you have any questions on how to do this, ask your Local Area Expert.
You are now ready to enter data.
Go to the form tab and double-click on “AFS29”. A form will appear, based on the design of the AFS 29 General Receipt. This should give you a blank form to begin entry.
Enter the receipt data as you would on any AFS 29, using ALL CAPS. The Receipt ID number will fill in automatically. The required fields are “Branch,” “Amt,” “Receipt Code,” and “DateRecd.” Please fill out the form as completely as possible, including description. If you know the number of the deposit slip you are using, enter the five-digit number (without the “U”, if you bank at US Bank) now, since you will need this information on the form in order to transfer or print the data.
If you choose to leave the deposit field blank for now, you may add it later by using the update query “Update Undeposited Receipts”. This query will ask you for a deposit number and it will fill in all blank deposit number fields.
If there is an SFMU deposit included in the deposit, you will need to include a receipt for the lump sum amount of your entries in order to balance. The only entries you will need are your branch, the lump sum amount, the receipt code “CEP”, the date of entry for the deposit, and the description “SFMU DEPOSIT”.
You are now ready to balance your deposit.
Prepare an adding machine tape of your deposit. Go to the report tab of the database and double-click on “Deposit Report”. The system will ask you to “Enter deposit you wish to export.” Enter the five-digit deposit number here and click “OK”. A report will be generated consisting of a header sheet with the amount of deposit (it must match the amount you send to the bank) and your receipts, printed three to a page. These receipts may be cut out and used as your hard copies.
Now you are ready to export your receipts to the central office.
Go to the query tab of the database and double click on “Export Deposit Query”. The system will ask you for the deposit number you wish to export. Enter and click “OK.” You will see a copy of your receipts in a spreadsheet format. Now go to the top pull-down menus and select “File”, “Save As/Export”. Change the selection and choose to save “To an External Drive or Database.” In the next window, change the file name to the five-digit deposit number, change the file type to Microsoft Excel 97, and change the file location to the folder you created to store these deposit records. Press enter. The system will ask you again the deposit number you wish to export. Enter the deposit number again and click “OK”.
Now you are ready to email the information to the FMU.
Prepare an email to the client pay in technician with the subject line referencing the deposit number. Attach the Microsoft Excel Spreadsheet you just exported.
Follow up by mailing the pink copy of the deposit slip to the Financial Management Unit.
That's it. If you need assistance, please contact the Receipting and Trust Unit.
PS: Please feel free to add reports, queries or forms as you wish. This is your tool, and I'm sure you can find ways to improve upon it. As long as the spreadsheet created is compatible with the database at the FMU, you can be as creative as you like.