Instructions for Full Version
of the Treeplanting
Accounting Software
These are preliminary instructions which can be used in conjunction with the other Treeplanting Accounting Instructions I sent you at the beginning of the summer (I can send that again) and I will eventually consolidate the two files and put them on the web with pictures etc., preferably after I develop the system more and after consultation with you (fine tuning). This file is intended to explain the latest changes and how to use the accounting file in general.
An explanation of the files found in the Favourites menu:
A breakdown of all the camp costs per planter per day. The future file can show only Active planters. It is the total camp costs charged per day as shown in the Camp Costs column in the “Planter Tallies- input” file, where the camp costs can be edited. They cannot be edited in this form and this is only for viewing the daily totals per planter.
For viewing how the invoices will look. Displays all invoices for all contracts, which you can go through by pressing the page navigation at the bottom left of the window. You can print individual pages, otherwise the Invoice buttons on the Master Contract form are designed for printing out individual invoices. The invoice is a first draft proposal and can easily be changed.
Used in conjunction with the Invoice Sub button on the Master Contract form if you only want to issue an invoice for certain blocks. For example, if it is a long contract and you want to invoice one invoice for the first half and another for the second half of the contract.
To issue an invoice for only some of the blocks on the contract, click on the Invoice Filter Sub file and then press the Design button. Alternatively you can double click on the file and then look at it in design mode by pressing the same button.
When in Design mode, you can set your Criteria in the Block ID column. For example, when viewing a particular contract in the Master Contract form, in the Block Overcount subform you might see Block IDs from 20 to 40. If you only want to issue an invoice for blocks 20 to and including 30, in the Criteria row of the Block ID column of the Invoice Filter Sub file (in Design mode) you would write the criteria
>19 AND <41
Once you punch in that information you may have to save the file, possibly close it. Then, when back in the Master Contract form, press the Invoice Sub button. Otherwise, to print out an invoice for all the blocks, simply press the Invoice button.
To select a particular contract, press the drop down menu at the top left hand side of the screen. Once that is selected much of the information (but not all) on the form will change, relating to the chosen contract.
The subform Block – Overcount will show information based on the contract chosen. <check against original Word file and format accordingly>
The totals for various columns will automatically calculate and be shown at the bottom of that subform. To see the totals for only certain blocks, use one of the form query methods (instructions in the Navigation and Data Manipulation Instructions section of the file Treeplanting Database Instructions). Note that I inputted much of the Delivered and Prescribed data by guessing, just to show how it works. I did not have the actual data at my disposal.
The Invoice button is to print an invoice for all the blocks for that particular contract.
The Invoice Sub button is to print out an invoice for only some of the blocks, as explained in the Invoice Filter Sub file above.
Both these buttons print on the default printer. To change the default printer to something else (for example, if you want to send a PDF file by email instead of a printed invoice by post), go to Start > Control Panel > Printers and Faxes. Select one of the printers and from the window which pops up for that printer, select the Printer menu and select “Set as Default Printer”. To create such PDF files you need the relevant program < .
For invoicing I can also set up a system to easily select different payment methods (such as an address for cheques, or bank transfer details), at your request. Such details would automatically show up on the printed invoice based on how they were selected in a drop-down menu in the Master Contracts form.
If you scroll down the Master Contracts form you will find the Breakdown of Planter Charges subform, which shows the Tallies for the contract you have selected at the top left of the form.
The bottom of this subform shows the Total Trees (planted) and Total Earnings. You can again show these totals for only certain planters or a certain time period by using one of the form query methods < .
At the top of the form but to the right you will find the Master Contract – Input subform, where you can input or change data concerning the contract. All input forms will be changed and developed over time and for now I was mostly focusing on setting things up so that you could compare it against your Excel data. Be careful about changing any data in any of the input forms, as it will change the data of the underlying tables and everything else relating to it (computations and totals etc.).
The Planter Breakdown subform shows certain data (totals and computations) for each planter.
Below that is the Profits per Block subform and includes data for all of the blocks and contracts.
This form is designed to analyse key data about each planter and for when you pay them.
You can select the planter from the drop-down menu at the top left hand corner of the form.
The Payment Input form below that shows all payments made to all planters, including cash advances, equipment sales, beer and so on, which are essentially forms of payment as well. It is important to treat these as forms of payments so that Total Owe calculates automatically whenever you pay a planter in one of these ways. By filling in the How Paid or Comments column you can then sort or organise such payments as you wish.
To make a new payment, scroll down to the bottom of this subform and click in the first empty row in the Planter column to select a planter to pay. As soon as you fill in the How Much Paid column the Total Owe will compute automatically. If you do not see the changes, you need to update/refresh the form <instructions below> .
The Check Written column is for manually writing out the How Much Paid number if you want to print out a cheque (Issue Cheque Query file under Reports).
For your convenience, you can copy the date shown in the Today box at the top right hand corner of the form and paste it into the When Paid column of this subform, when paying people.
If you scroll down in the form you will find the Earning Details of this Planter – by Species Breakdown subform, which shows all the tallies for the planter chosen.
To the right of this form you will find the Tally totals for the same planter but with calculated totals per day and not broken down according to species.
Below that is the How Much Owe subform which shows calculated totals for each planter, with a total at the bottom.
Below that is the Two Week Pay Query subform which shows the same information but for a selected period of time. To select the period of time modify the Two Week Pay Query form as found in the Favourites menu and as explained below.
The For Accountant subform below that shows the same data but in a format you can copy/paste into a Word file, for example, in order to email that to your accountant.
This is a nifty graph comparing earnings between selected planters. On the right of the graph you will find First Name, with which you can choose which planters to compare. Underneath the graph you will find Date, if you want to define which particular dates to compare.
This form is useful to examine each planter separately or to show them their data whenever they ask. As usual, you select the planter from the drop-down menu at the top left hand corner of the form.
Below that is a subform showing the daily totals for that planter.
To the right of that is shown all payments made to that planter, with a total at the bottom.
At the bottom is a form showing the Tallies according to species breakdown, with totals at the bottom of that subform. You can use one of the query methods < to calculate totals according to contract, block, date and any other factors.
On the right of the form you will find the Balance subform, showing the number of days they planted and other important totals, and below that a little graph showing their earnings per day and a trend line.
This is a report if you want to print out details for each planter and distribute it to them so that they can confirm the data against their own records prior to issuing a cheque. Instead of printing on paper you can print to create a PDF or image file and send them the data by email. You can also put the same data into a webpage so that they can confirm their figures online, but this would have to be developed.
If you want to show the data for only a certain period of time, you can set the time frame using the Planter Charges Query file. But many other forms, totals and computations use this same file, so if you make any changes to it make sure to erase the criteria after you are done. Open the file in Design Mode and put your limit in the Criteria row of the Date column. For example, if you want only data for the first two weeks of May, you could put as your criteria:
> 4/30/8 AND < 5/16/8
(after April 30 and before May 16, 2008). You may have to save and possibly close the file for it to work. Refresh/update the printout to see the changes. Note also that some computers are set differently, in which case the above criteria would have to be inputted as “> 30/4/8 AND < 16/5/8”, meaning day/month/year as opposed to month/day/year.
Keep in mind that the Total Owe is not affected by any date limitations. If you want the Total Owe and Total Paid to be limited to such dates, this would need to be developed.
When printing out the forms for individual planters, you will see the page numbers at the bottom left and keep that in mind, if you only want to print out certain pages.
Use this form to input or change the tallies. After careful consideration I decided it would be better to input the data directly here than to try and copy/paste it later from an Excel sheet, as I found this process rather cumbersome and difficult. The whole advantage of Access over Excel is that you only need to punch in the basic data and everything else computes instantly and automatically. The only information which should be punched first into an Excel file is the Reefer, and Deliveries from the reefer to the blocks, including the transfer of trees between blocks. For this I have prepared an Excel file suggesting how to go about it. Once you have finalised the delivery data to each block (delivered from reefer minus transfers to other blocks), you can copy/paste the data into Access to automatically compute Overcounts (Delivered minus actually planted) or Overplants (Delivered minus Prescribed).
You can input new data in a number of ways.
Individually – go to the bottom of the form, press Planter in the last empty row and select a planter. A new record is selected and Camp Costs is automatically assigned a value of 25 (this default value can be changed and instructions to all inputting will be explained in greater detail later – still to be written). Before leaving the record you must also state which species were planted and on which block.
By Group – for faster entry you can copy several records, such as all of the planters from the day before who had been working on a particular block with particular species, and paste append them to create new duplicate records. You would then only change the Date and the number of trees Planted. This too will be explained in greater detail later.
Foremen or day raters should be inputted into this form as well.
This form shows all the tallies, by species breakdown, but for viewing only, as the underlying data cannot be changed through this form. It shows certain totals at the bottom, which can calculate totals for selected criteria, such as certain blocks, dates, date ranges, contracts, planters and so on according to one of the query methods <include sorting instructions later> .
This query is used to compute totals for the accountant for a certain period of time, such as every two weeks. Set your Criteria in the Date column (in Design mode) in the same way as explained in the “Planter Tallies – Print Out For Their Review” section above.
For instructions on how to sort data or on one of the form query methods, refer to the Navigation and Data Manipulation Instructions section of the file Treeplanting Database Instructions. <..
To update or refresh data on a form, either it updates instantly and automatically, or you can do one of the following:
– close and open the form
– from the menu at the top press Records > Refresh
– press the <> button twice
– when punching in new data into a table, or when making changes to data, sometimes you may have to select it (use TAB or CTRL TAB when jumping from one cell to another) and press SHIFT ENTER for the data to update in the underlying table.
You may have to experiment to find out which of these methods works the best for the form you want to update or refresh.
If you like I can change the Master Contracts or other forms to include some picture, such as you planting, in the background. It could have Shakti in a fancy text overlayed on top of the graphic. I can fine tune any of the forms for you and it is not difficult. Just send me the graphic or picture, with basic instructions.
Further Notes
– say how, from July 6 to 8, price not 14 cents as I transferred from his accounting originally (he changed it to 12 and 13 cents), so there will be some discrepancy
– can continue developing it, such as the invoicing etc., later
– as I stated before, when you change species numbers in the column header but such data is not broken down to individual tally rows, it makes it difficult for me when transferring to Access, hence in this way there could be discrepancies between our systems
– item/Excel row 1601 in your Tally sheet is empty for Brad on 13th July, so if you updated this or anything else since I copied the info from you, there will be a discrepancy between our systems here as well (and subsequent totals etc.)
– mention about “common” planters in drop down list?
– assuming charging half camp costs during days off on his camps, and say how can check
– does Patrick pay camp costs when he gets a full day rate? All these issues need to be checked and here there could also be discrepancies. Does Matt pay camp costs? Matt is twice down at 1×250 on July 26 and 23. I corrected it in Access.
– some days for Jim and other day raters seem to be missing
– explain pdf printouts and his older version of Adobe
– repeat question about my own accounting – give Keta’s address
When continue:
– make an input form for Quantity and Prescribed etc.
Add to General Instructions
– can copy data (Block and Species numbers, Planted) from Block Overcount query to Block Species Breakdown table (or Block Species query).
– link to Reefer Excel file!
– submit to CNET etc.?
Back to Treeplanting Accounting Software Intro
Copyright © KENAX, Karel Kosman – All Rights Reserved Worldwide.
