Part Autonumber

[Kerry Purdy]

Hi,

I have a table which has happily generated invoice numbers for a couple of
years made up of "unique job ref" (autonumber generated when a job details
record is added) + "invoice date". It is a unique number so for invoices and
searching to update records it has worked fine. However as we are growing
and our accountant is running through our records he needs us to change the
way it works.

We now need to change the pattern so that they are sequential in order of
invoice generated date, if we started a job in June it would have a job ref
for arguments sake of 932, a job in october would have a job ref of 1247, if
we generated an invoice for the october job today it would be 2810-1247, then
we generate an invoice tomorrow for our june job the invoice number would be
2910-932.

What i need is to show through invoice numbering, that the invoice for the
october job was generated before the june job - does this make any sense?
Also, just to put the cat amongst the pigeons I need the sequence to start
from 0001

My brain is fried so not sure where to start, I already have 1260 records
in my job table so if I add a simple autonumber field all records are
populated of course, thus my first invoice generated would be 1261 and i need
it to be 0001.

Any ideas please (darn accountants and their demands hehe!)

Thanks very much for your time.

 

[Klatuu]

Can you break down the rules for creating the number a bit better, please.
I could find no pattern based on your description.

Also, never use autonumbers for this sort of thing. There will be gaps.
Autonumber fields should only be used for surrogate primary keys and for
relationg records between tables.

 

[Dorian]

All an invioice number needs is to be unique. Then you need a way to relate
invoice number to job.
The numbers that the accountant requires can be generated on the fly, no
need to make that the actual invoice number.
Do not make the serious design mistake of having hidden information in a
field (invoice number).
Of course the above is a vast oversimplification, just trying to set you on
the right track.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".