Unique Numbering

[G deady via AccessMonster.com]

I am writing a db to manage my lawoffice. I have played with access on and
off over the years but am essentientally a newbee. Each client will have a
unique identifier consisting of two numbers. The first is a client id
number whch is an autonumber(I have written an append query so the auto
numbers will correspond to my own numbering system. The second is a case
id number. For exambple, person can have two legal matters-say a divorce
and a Will. One file number would be 200501-01 and the second would be
200501-02. I have the case number to be manually entered. Heres my
problem. Each case number must be unique to each client number but start
over again with each new client-ie I can't have two 200501-01 entries but
MUST have 200501-01, 200501-02 and then start over again with 200502-01.
This creates a situation that is ripe for error. Can anyone suggest a
validaton rule that will prevent the user from entering the same case
number a second time where the client id name is the same? Or some other
solution for that matter. Thanks.

 

[Douglas J. Steele]

A unique index on that field would prevent duplications.

 

[G deady via AccessMonster.com]

I thought of that. Maybe I'm too much of an newbee but I didn't think that
would work . If you and I are both clients, we would both have the same
file id. Only our client ids would be different. ie. Mine ould be 200523-
01 and yours would be 200524-01. It is only when the same client has more
than one file that I need to prevent duplicate file numbers-ie I can't have
200523-01 twice. If I index on the file id and set to no duplicates
wouldn't this prevent duplicates even when the client numbers were
different?

 

[Rick Brandt]

I thought of that. Maybe I'm too much of an newbee but I didn't
think that would work . If you and I are both clients, we would both
have the same file id. Only our client ids would be different. ie.
Mine ould be 200523- 01 and yours would be 200524-01. It is only
when the same client has more than one file that I need to prevent
duplicate file numbers-ie I can't have 200523-01 twice. If I index
on the file id and set to no duplicates wouldn't this prevent
duplicates even when the client numbers were different?

You can create a unique index on the the combination of the two fields. In
desing view of the table go to "View - Indexes" and make an entyr that looks
like...

IndexName FirstField
SecondField

Unique = Yes

 

[G deady via AccessMonster.com]

Thanks. That worked

 

[G deady via AccessMonster.com]

Another question occurred to me. Since most clients will only have one
file can I set the default value for the file number field to one? I would
enter code for a message box with the message"The value entered is invalid.
Please enter a new value". I'm assuming if there was already a record with
the client number 2005023 and file number 01 the unigue index would trigger
an error and popup the message box if the default value were set to "01" or
am I wrong?