I would appreciate somebody's help. I've setup a simple database with
three tables. Workers, WorkerShift, and Shift tables. I'm having
trouble because it is possible for the data-entry personel to input
duplicate LastName, FirstName values. For example, my database allows
the creation of a duplicate MARK SMITH. I do not want this to happen.
I do want to have multiple SMITHs, and multiple MARKs, but not multiple
MARK SMITHs. Any suggestions? Here is my table layout...

Worker fields:
WorkerID - Primary Key (autoIncrement)
LastName - Text
FirstName - Text
Gender - Text (Lookup)
PhoneNumber - Text (Input Masked)

WorkerShift fields:
WorkerID - Foreign Key(Worker) - Composite Primary Key
ShiftID - Foreign Key(Shift) - Composite Primary Key

Shift fields:
ShiftID - Priimary Key (autoIncrement)
Description - Text

I'm satisfied with the table / relationship setup. One worker can work
many shifts, and one shift may be staffed by many workers. It is not
possible for one worker to be assigned the same shift twice (due to the
composite primary key on the WorkerShift table). This is what I want.

Thank you,
John

JohnCall wrote:
> I would appreciate somebody's help. I've setup a simple database with
> three tables. Workers, WorkerShift, and Shift tables. I'm having
> trouble because it is possible for the data-entry personel to input
> duplicate LastName, FirstName values. For example, my database allows
> the creation of a duplicate MARK SMITH. I do not want this to happen.
> I do want to have multiple SMITHs, and multiple MARKs, but not
> multiple MARK SMITHs. Any suggestions? Here is my table layout...
>
> Worker fields:
> WorkerID - Primary Key (autoIncrement)
> LastName - Text
> FirstName - Text
> Gender - Text (Lookup)
> PhoneNumber - Text (Input Masked)
>
> WorkerShift fields:
> WorkerID - Foreign Key(Worker) - Composite Primary Key
> ShiftID - Foreign Key(Shift) - Composite Primary Key
>
> Shift fields:
> ShiftID - Priimary Key (autoIncrement)
> Description - Text
>
> I'm satisfied with the table / relationship setup. One worker can
> work many shifts, and one shift may be staffed by many workers. It
> is not possible for one worker to be assigned the same shift twice
> (due to the composite primary key on the WorkerShift table). This is
> what I want.
>
> Thank you,
> John

You need a unique index that consists of BOTH name fields. Access did not make
multi-field index creation as obvious as for single fields. You need to open
the table in design view and then go to View - Indexes. In the index dialog you
need to make an entry like this...

IndexName LastName
FirstName

(at the bottom)
unique = yes.

BTW, what will you do if you actually end up with two John Smiths? As long as
WorkerID is unique that should be sufficient because that is what identifies the
worker. The other fields are just "extra info".

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"JohnCall" wrote:

> For example, my database allows
> the creation of a duplicate MARK SMITH. I do not want this to happen.
> I do want to have multiple SMITHs, and multiple MARKs, but not multiple
> MARK SMITHs.

So if a second person named Mark Smith applies for a job at your company,
you'll reject his application?

Names *are not unique*. That's the real world. I once worked with Dr.
Lawrence David Wise, Ph.D., and his colleague, Dr. Lawrence David Wise, Ph.D.

I'd suggest you reconsider! Pick some other field - department, phone
number, something - to include in the Combo Box from which you select the
name to pick the correct one.

You can put code in the personnel form's BeforeUpdate event to *DETECT*
possible duplicate entries, and warn the user - but I'd really suggest that
you allow duplicates (if they are really two people with the same name).

--
John W. Vinson[MVP]

Thanks Rick,

This is exactly what I want to do. I want to create a multi-field
index of LastName and FirstName. I've followed your steps, but Access
is not allowing me to create the index. I specify a name for the
index, and then I try typing in the field names such as "LastName,
FirstName" or "LastName & FirstName" or even "LastName Ctrl+Enter
FirstName" but nothing seems to be working. Access is not helping me
create a multi-field index.

I appreciate the other comment about allowing duplicate names, but this
is not the route I want to go. I would even prefer Indexing on
LastName + FirstName + MiddleName than allowing duplicate names. The
organization is small. Sorry to break rules and force the multi-index
thing.

Rick, can you further help me (or point me in the right direction) on
establishing a multi-field index?

Thanks,
John

Rick Brandt wrote:
> JohnCall wrote:
> > I would appreciate somebody's help. I've setup a simple database with
> > three tables. Workers, WorkerShift, and Shift tables. I'm having
> > trouble because it is possible for the data-entry personel to input
> > duplicate LastName, FirstName values. For example, my database allows
> > the creation of a duplicate MARK SMITH. I do not want this to happen.
> > I do want to have multiple SMITHs, and multiple MARKs, but not
> > multiple MARK SMITHs. Any suggestions? Here is my table layout...
> >
> > Worker fields:
> > WorkerID - Primary Key (autoIncrement)
> > LastName - Text
> > FirstName - Text
> > Gender - Text (Lookup)
> > PhoneNumber - Text (Input Masked)
> >
> > WorkerShift fields:
> > WorkerID - Foreign Key(Worker) - Composite Primary Key
> > ShiftID - Foreign Key(Shift) - Composite Primary Key
> >
> > Shift fields:
> > ShiftID - Priimary Key (autoIncrement)
> > Description - Text
> >
> > I'm satisfied with the table / relationship setup. One worker can
> > work many shifts, and one shift may be staffed by many workers. It
> > is not possible for one worker to be assigned the same shift twice
> > (due to the composite primary key on the WorkerShift table). This is
> > what I want.
> >
> > Thank you,
> > John
>
> You need a unique index that consists of BOTH name fields. Access did not make
> multi-field index creation as obvious as for single fields. You need to open
> the table in design view and then go to View - Indexes. In the index dialog you
> need to make an entry like this...
>
> IndexName LastName
> FirstName
>
> (at the bottom)
> unique = yes.
>
> BTW, what will you do if you actually end up with two John Smiths? As long as
> WorkerID is unique that should be sufficient because that is what identifies the
> worker. The other fields are just "extra info".
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com

On 29 Jan 2006 19:38:45 -0800, "JohnCall" <(E-Mail Removed)>
wrote:

>Thanks Rick,
>
>This is exactly what I want to do. I want to create a multi-field
>index of LastName and FirstName. I've followed your steps, but Access
>is not allowing me to create the index. I specify a name for the
>index, and then I try typing in the field names such as "LastName,
>FirstName" or "LastName & FirstName" or even "LastName Ctrl+Enter
>FirstName" but nothing seems to be working. Access is not helping me
>create a multi-field index.

That IS NOT WHAT HE SUGGESTED.

Reread it.

There are multiple lines in the Index box, and two columns.
Put some distinctive index name in the left-hand column.
Put LastName in the right hand column next to it.
Put FirstName in the right hand column, in the next row down in the
box.
Check the Unique checkbox.

You now have a unique index on the combination of the two fields.

>I appreciate the other comment about allowing duplicate names, but this
>is not the route I want to go. I would even prefer Indexing on
>LastName + FirstName + MiddleName than allowing duplicate names. The
>organization is small. Sorry to break rules and force the multi-index
>thing.

Probably prudent... better, of course, would be to WARN about
duplicate names but allow them after the warning has been dealt with.
Your call though!

John W. Vinson[MVP]