The Description is going to be the same for each month. e.g. I.T. Services for January 2012
The date the invoices are created each month is going to be the same (e.g. for January it will be 01-01-2012)
I will get rid of the total field as it can be calculated and make changes to the address field as well.
How do I go about doing it though. Do you have any example databases that do this sort of thing so I can see how it's done?
ok, hon, it won't be too hard. start by creating a form, and adding two unbound textbox controls, name one txtDate
and name the other txtDescription
. you'll use these controls to enter the date and description each month when you create your invoices.
next, create a SELECT query, using tblJobs, and setting criteria to limit the records to active customers. once you're pulling only the customer records you want to see, then change the SELECT query to an APPEND query. you should be able to do this in query Design view, probably from the ribbon. (i say probably because i've never used A2010 and am not familiar with the interface.) look in Access Help for instructions on how to create an Append query. or look in a reference book; there are tons of books available for basic to advanced development, might be a good idea to get one if you haven't already done so. you want to append
records to tblInvoices. use the customerID and amount fields from tblJobs to populate the corresponding fields in tblInvoices. populate the invoicedate and description fields from the form, using the following references, as
with the actual name of your form. if you're going to store the actual tax amount, you can do the calculation in the query, and append the result; if you're going to store the tax rate instead, you can hard-code that value in the query as long as it will be the same rate for every customer. the tax calculation would be something like
Tax: tblJobs.amount * .775
when you run the APPEND query (which you can do from a command button on the form), the selected records from tblJobs will be added to tblInvoices - just the fields you've set up to append, and each new record will be populated with the date and description from the form, and with the tax rate - or the tax amount from the "Tax" expression i posted just above.
once the records have been added to tblInvoices, you're ready to create invoices to print and send to your customers. first, create a SELECT query using tblInvoices and tblCustomers, joined on the CustomerID field found in both tables; and set criteria to pull the invoices for a specific date. use the form control as criteria on the invoice date field from tblInvoices, as
and again using the real form name in the expression. when the SELECT query is pulling the records you want to see, then create a report based on the query.
you can write code or macros to run from the command button to do the following automatically: run the Append query you created, and then print the report of invoices.
this may sound like quite a lot, but it really is do-able. just work your way through each step, using Access Help and a reference book to learn how to build each object - the form, query, the report - and come back to the forums for specific help if you run into a wall at any point.