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
|
|||
|
|||
Avoiding duplicate data entry
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. |
#2
|
|||
|
|||
Avoiding duplicate data entry
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 wrote: 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. |
#3
|
|||
|
|||
Avoiding duplicate data entry
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" wrote: 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 wrote: 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. |
#4
|
|||
|
|||
Avoiding duplicate data entry
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 wrote: 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" wrote: 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 wrote: 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. |
#5
|
|||
|
|||
Avoiding duplicate data entry
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
#6
|
|||
|
|||
Avoiding duplicate data entry
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 wrote: Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk? - StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
#7
|
|||
|
|||
Avoiding duplicate data entry
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" wrote: 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 wrote: Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk? - StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
#8
|
|||
|
|||
Avoiding duplicate data entry
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 wrote: 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" wrote: 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 wrote: Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk? - StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
#9
|
|||
|
|||
Avoiding duplicate data entry
Hmm.... there are still things you will need to do ... but this is a start
smile Indeedsmile 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" wrote: 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 wrote: 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" wrote: 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 wrote: Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk? - StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
#10
|
|||
|
|||
Avoiding duplicate data entry
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 wrote: Hmm.... there are still things you will need to do ... but this is a start smile Indeedsmile 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" wrote: 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 wrote: 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" wrote: 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 wrote: Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk? - StudentID, long, fk to Students Something to do with Relationships? Link? Great tutorial, now a permanent link in my favourites! thanks, regards Peter. "strive4peace" wrote: 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 wrote: 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. |
Thread Tools | |
Display Modes | |
|
|