Duplicates

[Tazzy via AccessMonster.com]

Hi All,

I have a student database where all details of students are entered directly
onto a main form, there are a number of subforms on this that record each
students activities. One of these is an absence form that is based on a
small table as follows;

Absence No - Autonumber Primary Key
Student ID - linked to main student table
Date
Authorised
Reasons

I need to make sure that the date field cannot be repeated in this subform
for this student yet allow other students to also be absent on this date on
their form, but I'm not sure how to do this. I know that I can prevent this
when creating the table, but if student A is absent on say 9th June, then it
wouldn't allow any other student to be absent on that date. Hope I've
explained this properly, would appreciate any help you may be able to give.
I think that I'm probably have to go into the coding area for this, but I
only have very limited experience of using this.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[Allen Browne]

You can create an index such that the combination of StudentID + Date must
be unique.

Before you do this, I'm going to suggest that you rename the field to
AbsentDate. Date is a reserved word, and will cause you problems.

Open the table in design view.
Open the Indexes dialog (toolbar.)
On a fresh row in the dialog, enter 2 rows like this:
StudentidAbsentdate StudentID Ascending
AbsentDate Ascending

Note that the index name is blank on the 2nd row. That indicates that the
index is on the combination of the 2 rows.

Select the first row of the index, and in the lower pane of the Indexes
dialog, set the Unique property to Yes.

This will prevent you adding 2 records for the same combination.

For more info about field names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tazzy via AccessMonster.com" <u26845@uwewrote in message
news:736e0cc48c03b@uwe...

Hi All,

I have a student database where all details of students are entered
directly
onto a main form, there are a number of subforms on this that record each
students activities. One of these is an absence form that is based on a
small table as follows;

Absence No - Autonumber Primary Key
Student ID - linked to main student table
Date
Authorised
Reasons

I need to make sure that the date field cannot be repeated in this subform
for this student yet allow other students to also be absent on this date
on
their form, but I'm not sure how to do this. I know that I can prevent
this
when creating the table, but if student A is absent on say 9th June, then
it
wouldn't allow any other student to be absent on that date. Hope I've
explained this properly, would appreciate any help you may be able to
give.
I think that I'm probably have to go into the coding area for this, but I
only have very limited experience of using this.

[Tazzy via AccessMonster.com]

Hi Allen,

Thanks very much for your help on this. I've changed the date field as you
suggested and also changed the details for the indexing. When I tried to
save the table, I got the error message saying that this would create
duplicates in the index, primary key or relationship, I'm not too sure where
I'm going wrong here

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[Allen Browne]

Does that mean you already have some records in the table where a person has
2 records for the same date?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tazzy via AccessMonster.com" <u26845@uwewrote in message
news:736f4f93b829f@uwe...

Hi Allen,

Thanks very much for your help on this. I've changed the date field as
you
suggested and also changed the details for the indexing. When I tried to
save the table, I got the error message saying that this would create
duplicates in the index, primary key or relationship, I'm not too sure
where
I'm going wrong here

[Tazzy via AccessMonster.com]

Hi Allen,

I can see why you answer these type of questions - you're a physic!

Yes, I did have one date duplicated for a student, I deleted that, followed
your instructions and it works just the way I need.

Thank you so much for your help - live long and prosper

Kind Regards,
Tazzy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[Arch]

"Tazzy via AccessMonster.com" <u26845@uwewrote in message
news:73729ad786c77@uwe...

Hi Allen,

I can see why you answer these type of questions - you're a physic!

And a psychic too!