Avoiding duplicate data entry

[pmccrackan]

Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

 

[strive4peace]

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer --> fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if >= 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
have an awesome day
*

 

[pmccrackan]

Hey Crystal, many thanks for all that. I'll get back here when I have tidied
the file up as per your suggestions and read through the link you have posted.
Great help, much appreciated.
Regards Peter.

 

[strive4peace]

you're welcome, Peter

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[pmccrackan]

Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
RE: > - StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

 

[strive4peace]

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[pmccrackan]

yeah I managed to work out the FK bit just after I posted the question (now
how often does that happen!) Thanks anyway.
Right......... now I have 3 tables with these fields
T_Students - StudentID LastNameStudent FirstNameStudent
T_Subjects - SubjectID SubjectName (with the 4 subjects already entered in)
hmmm, do I need them entered here? Can I just enter the 4 subjects into a
combo box on the form and have them entered into the table that way?
T_StudentPrefs StudentPrefID StudentID SubjectID PrefNum
Now, I am starting to feel a little lost but have put this together with the
following relationships established,
T_StudentPrefs PK StudentID outerjoin to T_Students PK StudentID
T_StudentPrefs PK SubjectID outerjoin to T_Subjects PK SubjectID
Is this what you mean when talking about Foreign keys?
The teacher wants the students to enter their names and preferences into a
form
So, I imagined a form with blank fields for name, the list of 4 subjects,
each subject with a combo box off to the right to select their preference by
selecting 1 for Geography etc.
At the end of the day, the teacher wants a list with the number of students
for each subject and preferences., 1st, 2nd, 3rd, 4th.
Now this is very easy to do, and I had a trial database worked out to her
satisfaction, but the more I thought about it the more ideas I had about how
to go about it., There is still plenty of time before she needs this, so I
thought I would use it as an exercise for my own training and "do it properly"
Regards Peter.

 

[strive4peace]

Hi Peter,

by foreign key, I mean this:

studentID is the primary key in the Students table
studentID is a foreign key in the StudentPrefs table because it links a
related student preference record to a student record

read the section on mainform/subform in Access Basics

make a main form based on Students -- this will be your main form

make sure that StudentID is on this form

make another form based on StudentPrefs -- this will be used as a
subform. Meke the default view --> continuous form

on this form:

textbox control for PrefNum allowing 1-4 to be entered

combobox control for SubjectID
RowSource -->
SELECT SubjectID, SubjectName
FROM Subjects
ORDER BY SubjectName

Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2.2

textbox control for StudentID
Visible --> No

textbox control for StudentPrefID
TabStop --> No

on the BeforeUpdate event of PrefNum

'~~~~~~~~~~~~~~~~~~
if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Prefereance Number"
Cancel = true
exit sub
end if

if me.prefnum < 1 or me.PrefNum > 4 then
msgbox "You must enter 1-4 for the preference" _
,,"Data not valid"
Cancel = true
exit sub
end if

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND Prefnum = " & me.prefnum _
& " AND StudentPrefID <> " & me.StudentPrefID _
),0) <> 0 then
msgbox "You must enter 1-4 for the preference" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the BeforeUpdate event of SubjectID

'~~~~~~~~~~~~~~~~~~

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND SubjectID= " & me.SubjectID _
& " AND StudentPrefID <> " & me.StudentPrefID _
),0) <> 0 then
msgbox "You may not use the same subject twice" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the form BeforeUpdate event, make sure prefnum and subjectID are
filled out


'~~~~~~~~~~~~~~~~~~

if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Preferance Number"
Cancel = true
exit sub
end if

if isnull(me.subjectID) then
msgbox "You must specify a Subject" _
"Missing subject"
Cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~

now, add the subform to the mainform
use a subform control and set these properties:

SourceObject --> the name of your subform

LinkMasterFields --> StudentID
LinkChildFields --> StudentID

there are still things you will need to do ... but this is a start <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[pmccrackan]

Hmm.... there are still things you will need to do ... but this is a start
<smile>

Indeed<smile> something to keep the brain ticking over for a while. I will
let you know when I have found all the syntax "errors" etc and it is all
working as it should.
You have been a great help, I enjoy this style of help, it makes you
actually think and learn.
regards Peter.

 

[strive4peace]

you're welcome, Peter

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*