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
|
|||
|
|||
Restricting selecting
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 |
#2
|
|||
|
|||
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. -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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. -- Good luck Jeff Boyce Access MVP |
#4
|
|||
|
|||
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. Shane Nation wrote: 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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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. -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Thank you so much, that is great, it works just how I needed it to. I can go
to work happy Thanks Shane "John Spencer (MVP)" wrote in message ... 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. Shane Nation wrote: 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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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. -- Good luck Jeff Boyce Access MVP |
#6
|
|||
|
|||
Wonderful
Thank you Shane "John Spencer (MVP)" wrote in message ... 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. Shane Nation wrote: 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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
selecting multiple worksheets | dolphinv4 | Worksheet Functions | 1 | August 4th, 2004 01:28 PM |
selecting earliest dates | evel79 | General Discussion | 5 | June 13th, 2004 08:20 PM |
Prevent selecting all recipients in global address list | gugf | Contacts | 0 | May 25th, 2004 01:56 PM |
Arrows and Shortcut keys do not work when selecting cell ranges | Erik | Charts and Charting | 2 | October 14th, 2003 01:42 PM |