Hi,

I am making an invoicing database, So far I have the following tables & fields.

tblInvoices
invoice no (autonumber,primary key)
invoice date (date)
Description (memo)
amount (currency)
tax (calculated field)
total (calculated field)
customerID (foreign key)

tblJobs
Job Id
amount
customer ID (foreign key)

tblCustomers
Customer ID
Company
Billing Email
Billing Address
Active Customer


every month only two things change on the invoices. (1) Description (2) Invoice date
I want to create a form where I put the Description & Invoice Date then press CREATE INVOICES, and it creates invoices for all active customers.
It should pick up customer name & address from Customers table, and pick up total amount from Jobs table (the job that is relevant to that customer only)

I know what I want, but do not know how to do it, what type of queries I need to create and if there is another easier way of accomplishing the same thing.

Please treat me as a newbie as I am still learning Access.

I have Access 2010.

well, your stated goal sounds do-able. a couple questions:

do you want to enter a description in your form, and then apply that description to every invoice that you generate at one time? or does each invoice get a unique description?

do you store the complete "billing address" in a single field? if so, recommend you break that out into separate fields for street address (i often use two fields, one for the number/street, and the other for apt/suite/lot/bldg identifications, as needed), city, state, and ZIP - or, if not USA, whatever "parts" make up an address where you live.

storing calculated data is a normalization no-no. recommend you get rid of the "total" field in tblInvoices, and instead of storing the "tax", store the tax rate that is "current" at the time the record is created. that value, and the "amount" value, gives you the raw data you need to calculate the tax and the total at runtime whenever you need to display and/or print them.

also, i'm guess that the "ActiveCustomer" field in tblCustomers is a Yes/No field. i'd probably get rid of that field and instead create a field called something like "Terminated" or "TermDate", so that i could enter the date when a customer left (you may want to also add a field to track when a new customer is added). to get active customers, query for customer records where the "terminated" field is Null.

hth
tina

hth
tina

Thank you Tina,

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?

Can you post your db?

I'm assuming you want to use a report as your invoice? If so it makes this quite easy

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

Forms!MyFormName!txtDate
Forms!MyFormName!txtDescription

replace MyFormName 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

Forms!MyFormName!txtDate

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.

hth
tina

The reviews were good so I bought "Access 2010 inside out".

Your instructions are really great.
I have managed to create monthly invoices. Now I am working on invoice reports.

I hard-coded the tax to 10% by adding it to the query.

I have been creating each invoice manually for last 6 months. 6 months x 40 clients = 240 - this is how many times I want to thank you for helping me do this. Every time I created invoices I thought there must be an easy way - then I started asking questions and here I am.

Thank you Tina 240 times!

you are welcome for every one of them, hon. :) and good job! i knew you could do it, and you learned a lot by working through it yourself. and now you understand the basics of programming - break down each task into specific steps that will take you from start to finish, and then work through each step until you get it to work. very, very good! tina

I have created the invoicing database. Where one can simply type in the new invoice date & Description (e.g. I.T. services for the month of January) and get invoices created for all the ACTIVE clients in the database.
Attached is the database, Hope it helps someone!

I am now going to try to create something where each invoice gets emailed to each client based on their email address.

Of course I will need help for that - I have posted a new topic Here is the link