If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automate Invoice Number Assignment
I have a very simple database (2003) that I use to input time and materials
for invoicing. I have queries and reports that generate invoices, but I haven't figured out a way to have it autonumber an invoice "on the fly." Here are the issues: * There may be clients that are not billed in a given month, so you can't generate an invoice number based on client. * Somehow, based on the date of the time/materials billed, it will have to pick those up within that period to include in the invoicing. * Invoices are usually generated monthly, but there may be instances where "special" invoices may be generated mid month. * There may also be other invoices that may be outside of the normal invoicing that could have a special invoice number (like 999). Any thoughts on how to approach this so that as time is input, it is linked to an invoice number that is generated from the database? Right now, the query asks for the company name, a range of time (usually monthly) and then an invoice number, which I track manually in a log, to place the invoice number within the invoice. Then I save as a pdf and I am done. I just worry I will duplicate invoices and it just takes time to sequence and determine invoice numbers every month. Thanks! |
#2
|
|||
|
|||
Hi Karl
Presumably you have something like a Job table that you fill out when you do some work for a client, and a JobDetail table which contains the line items for the job. You could add an InvoiceDetailID field to the JobDetail table. It is blank until an invoice has been created for that line item. At the end of the month, you could then run some code to find all the customers who have jobs that have items that have not been invoiced. The code then creates an invoice for each customer, copies the JobDetail line items into the InvoiceDetail for this invoice, and updates the JobDetail record with the InvoiceDetailID so that next month these JobDetail line items won't be invoiced again. The end result is a fully verifiable history: you can see when each job detail became part of an invoice. In a very simple system, it may even be possible to use the JobDetail table as the InvoiceDetail table, i.e. the same items end up being foreign keys to both your Job table and your Invoice table. The approach does require some ability with code, recordsets, and action query statments. For any code like this that automatically creates records, it is a good idea to assign a batch number. You can then reverse the automated process if necessary, because you know which items were involved in the batch. HTH. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Karl Burrows" wrote in message ... I have a very simple database (2003) that I use to input time and materials for invoicing. I have queries and reports that generate invoices, but I haven't figured out a way to have it autonumber an invoice "on the fly." Here are the issues: * There may be clients that are not billed in a given month, so you can't generate an invoice number based on client. * Somehow, based on the date of the time/materials billed, it will have to pick those up within that period to include in the invoicing. * Invoices are usually generated monthly, but there may be instances where "special" invoices may be generated mid month. * There may also be other invoices that may be outside of the normal invoicing that could have a special invoice number (like 999). Any thoughts on how to approach this so that as time is input, it is linked to an invoice number that is generated from the database? Right now, the query asks for the company name, a range of time (usually monthly) and then an invoice number, which I track manually in a log, to place the invoice number within the invoice. Then I save as a pdf and I am done. I just worry I will duplicate invoices and it just takes time to sequence and determine invoice numbers every month. Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
invoice number | J.J. | Running & Setting Up Queries | 2 | August 4th, 2004 06:10 PM |
Naming a report | Martin Racette | Setting Up & Running Reports | 9 | May 29th, 2004 10:51 PM |
Unique "Invoice" Number | sara | Worksheet Functions | 1 | May 13th, 2004 07:50 PM |
make a function that add number 1 in another number | Manos | Worksheet Functions | 3 | February 3rd, 2004 01:37 PM |
What formula would show me I already entered an invoice number once before? | Marvin Hlavac | Worksheet Functions | 4 | November 18th, 2003 01:40 AM |