Restricting selecting

  • Thread starter Thread starter Shane Nation
  • Start date Start date
S

Shane Nation

I have an Access database (2003) with three tables:

Table A has a name, team name etc.

Each row in table B has a date (Each day, going to next year), with six
possible shifts each person could work

Table C has the ID of the persons name from table A, and the dates along
with the shifts that they are doing.

(As it was a many to many relationship I had to created a third table (C)

I have created a query (Q1) from table A and table C, linking them on the
name ID field.

I then have a form showing Table A and Q1 as a sub form. All works fine as
you can see the name of the person and select the date requires in the sub
form in a dropdown list linked to table B and tick which shift they are
doing on the date you have picked.



Question:

The trouble is that you can select the date more than once in the sub form,
so you may have the 02/09/2004 twice, which obviously is no good as they can
't work the same day twice. How do I stop this being allowed?



Any help would be great



Thanks
 
Shane

Are you saying that your third table can't have the same person ID and date
more than once? So, no double-shifting is allowed, right?

You can set a unique index on the two fields combined. This will prevent
the entry of more than one person/date combination.
 
Yes that is it to a tee. I have been trying to set a combined unique index
but don't know how. So if "Fred" and "02/09/2004" have already been selected
you can't select them again, but "Harry" and "02/09/2004 would be fine or
"Fred" and 03/09/2004".

Please tell me how I do this combined index!

Thanks
 
Pardon me for jumping in.

To make a compound index in Access

Open up the table in design view
Select View: Indexes from the menu
Type an Index Name in the first blank row
Select one of your fields
Set Unique to Yes in the index properties
Move down to the next row and select another field
(leave the index name BLANK)
(repeat the above step for up to 9 total fields)

Close the indexes window.
 
Thank you so much, that is great, it works just how I needed it to. I can go
to work happy

Thanks

Shane
 
Wonderful
Thank you

Shane
John Spencer (MVP) said:
Pardon me for jumping in.

To make a compound index in Access

Open up the table in design view
Select View: Indexes from the menu
Type an Index Name in the first blank row
Select one of your fields
Set Unique to Yes in the index properties
Move down to the next row and select another field
(leave the index name BLANK)
(repeat the above step for up to 9 total fields)

Close the indexes window.
Click to expand...
 
Back
Top