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
|
|||
|
|||
auto increment
It's not good database design to have hidden data in a column e.g. the year
'hidden' in a primary key. Stick with the autonumber primary key and put the year into another column. Does the sequence number really have to restart at 1 for each new year? No, you cannot do what you want in table design, you would have to create the key manually yourself. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". |
#2
|
|||
|
|||
auto increment
Hi,
it's not because you need a unique field it has to be the primary key. The same situation exists in invoice numbering, they have to be continuously numbered and restart with 1 each year. The most used solustion for that is: the pk field is, as usual, an autonumber which is hidden for the users. The unique invoice number, or ticket number in this case, is build automatically from the sysCounters table. You can create this table with the following fields: countID (autonumber) = PK field countType (text or number if you have a related types table), in my case this is a text field with the following 3 records: INV(invoice), CRN(credit note), PRF(Pro Forma); in your case you need only TICKET countYear (the year the counter is used, this makes it possible at the beginning of a year to create a ticket for the last year, or to create already a ticket for the next year at the end of a year) countValue: the last used number. all you have to do on creation of a new record is to use a dlookup function to get the last number, build the correct ticket number with this number + 1, and then update the counters table with this new last number. Succes -- Kind regards Noëlla "Dorian" wrote: It's not good database design to have hidden data in a column e.g. the year 'hidden' in a primary key. Stick with the autonumber primary key and put the year into another column. Does the sequence number really have to restart at 1 for each new year? No, you cannot do what you want in table design, you would have to create the key manually yourself. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". |
#3
|
|||
|
|||
auto increment
Noëlla
I've used a variation on your approach, but done away with the separate table. I have a [SequenceNumber] field (the same as your countValue) in each record in my "main" table, as well as a [RecordYear] field. When it comes time to add a new record, I first require the user (via a form, of course!) to indicate the applicable year, then use a simple function to find the largest [SequenceNumber] already used where [RecordYear] matches the indicated applicable year. Then I add 1. I have to futz with it a bit to handle the 'first record of the year' situation, but that's not complex either. Regards Jeff Boyce Microsoft Office/Access MVP "Noëlla Gabriël" wrote in message ... Hi, it's not because you need a unique field it has to be the primary key. The same situation exists in invoice numbering, they have to be continuously numbered and restart with 1 each year. The most used solustion for that is: the pk field is, as usual, an autonumber which is hidden for the users. The unique invoice number, or ticket number in this case, is build automatically from the sysCounters table. You can create this table with the following fields: countID (autonumber) = PK field countType (text or number if you have a related types table), in my case this is a text field with the following 3 records: INV(invoice), CRN(credit note), PRF(Pro Forma); in your case you need only TICKET countYear (the year the counter is used, this makes it possible at the beginning of a year to create a ticket for the last year, or to create already a ticket for the next year at the end of a year) countValue: the last used number. all you have to do on creation of a new record is to use a dlookup function to get the last number, build the correct ticket number with this number + 1, and then update the counters table with this new last number. Succes -- Kind regards Noëlla "Dorian" wrote: It's not good database design to have hidden data in a column e.g. the year 'hidden' in a primary key. Stick with the autonumber primary key and put the year into another column. Does the sequence number really have to restart at 1 for each new year? No, you cannot do what you want in table design, you would have to create the key manually yourself. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". |
Thread Tools | |
Display Modes | |
|
|