Assigning numbers in batches

[John]

Hi

I have a jobs table in which a client can be assigned to multiple jobs. Now
I need to assign invoice numbers to jobs such that all jobs belonging to one
client get the same first number, all jobs belonging to second client get
the second invoice number and so on. Basically I need to batch jobs by
client so they all appear on the same invoice rather than individual invoice
for each job. How can I achieve this?

Thanks

Regards

 

[tony]

Since you probably have a client ID or account number you could use
that number as the prefix for the invoice number and then use a
sequential number as the end of the invoice
For example if the client ID was RS1009
and you were writing the 150th invoice for the year the invoice number
could look like this RS1009-00150 or RS100900150
to find the invoices for the particular customer you would use the
LEFT() function
ie Left([invoiceno],6)
as the criteria in your report query

HTH

 

[salad]

Hi

I have a jobs table in which a client can be assigned to multiple jobs. Now
I need to assign invoice numbers to jobs such that all jobs belonging to one
client get the same first number, all jobs belonging to second client get
the second invoice number and so on. Basically I need to batch jobs by
client so they all appear on the same invoice rather than individual invoice
for each job. How can I achieve this?

Thanks

Regards

Well, you could check to see if the customer exists
'get invoice for that job
lngInv = Dlookup("InvNum","Table","Job = " & Me.Job)
'doesn't exist, get the next number
If IsNull(lngInv) Then
lngInv = DMax("InvNum","Table") + 1
Endif

 

[Graham Mandeno]

Hi John

Add an InvoiceNumber field to your Jobs table, related one-to-many with your
invoices table.

When you want to add an invoice, list all the completed jobs for that
customer that have not yet been invoiced (InvoiceNumber is null). Then you
can select which jobs to include on the new invoice. Including a job on an
invoice merely constitutes recording the invoice number in that field.

If you prefer not to be selective, you could automate the system entirely
to:

1) Open a DISTINCT query to list all the customers who have completed,
uninvoiced jobs

2) For each customer, add a new invoice, and then update the invoiceable
jobs for that customer to record the invoice number.