How to create same monthly invoices automatically

[snehal0909]

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.

 

[DavidAtWork]

Re table design, if the only the date and description change, then I would suggest putting the amount in the customer table as a customer attribute, if tax is always fixed and will never change then you only need to display on the invoice as it is calculated, but if it can change, then you'd need to store that value in the invoice table along with the total. Also unless you write essays in the description field I would advise to change the memo data type to a text field 255 as memo fields can cause problems.
How is the invoice desription used, is it going to be same for all invoices for that month or will it change from customer to customer, if the former then you'll need a text box to enter your description also a text box (set to a date format) to enter the invoice date and use a button to "Create Invoices", using the onClick event, run a query to append new invoices to the invoice table, so for each active customer, it will append a record which hold values of date, desc, custID, amount (will come from customer table) , tax & total only need to be displayed on the final invoice and don't need to be stored. You can query these records (along with customer and job details) to create a report, which when printed will be your invoices.
Going back to the description, if it will be different for each customer then that will require a different approach as you will have to create invoice records manually for each customer.
David

 

[snehal0909]

Yes the tax is fixed 10%.
The description is the same for each month e.g."I.T. services for January 2012"
But I do need to store the total & tax amount so I can later modify the invoices later for tax free items.

How do I do the following? Do you have any example that I can look up & try to do the same with my db?

Create Invoices", using the onClick event, run a query to append new invoices to the invoice table, so for each active customer, it will append a record which hold values of date, desc, custID, amount (will come from customer table) , tax & total only need to be displayed on the final invoice and don't need to be stored.

Thank you for the reply.

 

[DavidAtWork]

OK, so you will need to store the tax and total values, description & date can come from text boxes as above and all these invoices can be created by running a simple append query to the invoice table. I'm sorry I can't steer you towards an example, maybe others from the forum might be able to point you in the right direction
David

 

[snehal0909]

Thank you Pat.
Trying to do it now.