• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

I/O Logic when working with "survey" of 150+ fields

I need help with my thinking... (common occurrence)
I am working on the input and output of a standards-based competency profile where each student, depending on the lab, may have in excess of 150 standards. The instructors are to identify 4 possible levels (P, S, I, N/I) of proficiency for each standard - for each student - and then generate a report  for each student.
I have created one input format (one record per student with 100+ fields) but I cannot get a form to work for the larger reports as I exceed the limit of fields. I prefer to give the instructors a toggle button of the 4 choices for visual and "mechanical" ease.
Ideally, I would like to create one input form that "flexes" according to the lab and number of competencies. I have attached the output file. CTE-Competencies-Merge.doc
0
Heartless91
Asked:
Heartless91
  • 20
  • 14
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Need a database design like this:

tblStudents - one record per student
StudentID - Autonumber - PK
LastName - Text
FirstName - Text
...

tblLabs - One record per lab offered
LabID - Autonumber - PK
LabName - Text

tblStudentLabs - One record per student/lab combination
StudentID - Long - PKA
LabID - Long - PKA

tblLabCompetency - One record per lab / competency combination
CompetencyID - Auto Number - PK
LabID - Long - FK to tblLabs
Description

tblStudentLabCompetency - One record per student per lab competency required
StudentID - Long - FK to tblStrudents
CompetencyID - Long - FK to tblLabCompetency
Result - Text - P, S, I, N/I

  With this setup, you can have any number of labs and students, students can have as many labs as then need.

  Each lab can have as many competencies as required and a student can be rated on each one.

HTH,
JimD.
0
 
Heartless91Author Commented:
Actually, my table setup is pretty close to what you recommended. What is your recommendation for the form design? In my head, I'm seeing a form that is similar to the Word document that is attached where the instructor will see groupings of the competencies and click on associated toggle choices for each competency.  What is your advice for the input design? Screen shot of my input form
0
 
GRayLCommented:
In Help enter - Access Specifications , click Forms and Reports

Number of controls and sections you can add over the lifetime of the form or report - 754

If during the development of the form there has been a lot of additions and deletions maybe they accumulate, you could try a copy and paste.  If the form had 250 controls/sections at the time of the copy, but was at 754 over its lifetime, although you would expect to be able to add a lot more, that limit may stop development.  I'm thinking a Copy and Paste may reset the 'counter' for the new form to 250 from 754.  I don't have anything that busy so I'm not in a position to test my theory.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GRayLCommented:
Sorry, I'm running A2003 - I just noticed you are running A2007.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<What is your advice for the input design? >>

  Looks good.  I would use a main form allowing a pick for a student.  Then two subforms; first to control the labs, the second sync'd to the first to display the competency for the choosen lab.  Both would be in continous form view. Pretty close to what you outlined there.

Ray,

<<Sorry, I'm running A2003 - I just noticed you are running A2007. >>

  A2007 has the same limitation.  Shouldn't bump into that though with a continous sub-form.

JimD.
0
 
Heartless91Author Commented:
What you see in the image is the form (with student and lab - on the left) and the subform (with the competencies). I'm not following your suggestion of the subform with the continuous form view. The form you see has 100+ toggle buttons - each designated to a specific field (001, 002, etc). I couldn't figure out how to set up a toggle button that assigned the selected value to the current field. Let me play with the database once I return to work on Monday. I'll post then.
0
 
Heartless91Author Commented:
Sorry so long in the process. My job is teaching first with databases in spare moments. Today didn't offer much in spare time. I am struggling with the form(s). Main form is for student selection (PK is StID). The subform for the competencies looks good with continuous form. I am having trouble with the subform for rating using the toggle buttons. Do you mean that I should place each subform on the student form? The logic to me (that isn't working) is that I would link the subform (sfrmCompetencies) to the subform (sfrmScoring) [using CompID as the parent link]and then link this combined subform to frmStudents [using StID as the parent link]. When I try linking the two subforms, I get the message that I cannot use continuous forms.
I have:
tblCompetencies CompID as PK, LabID as txt, Lab as txt, Category as txt, Competency as txt (sfrm Competencies)
tblScoring ScoreID as PK, StId as txt, CompID as number, Rating as txt (sfrmScoring)
tblStudents StID as PK, Last as txt, First as Txt, Lab as txt (frmStudents)

Hopefully, I make some sense.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
One question, a student can only have one lab?

 tblStudents StID as PK, Last as txt, First as Txt, Lab as txt (frmStudents)

  And are your PK's really all text fields?

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

In tblCompetencies, what's the difference between LabID and Lab?

JimD.
0
 
Heartless91Author Commented:
The only txt PK is the StID which is a unique number that comes from the school as a text field. I could convert it to number if that would reduce possible errors. In tblCompetencies, CompID is the PK and runs sequentially. LabID is sequential numbering (per lab) as a 3-character text field that I have used on previous database forms. Lab is a text field that is pulled from the school that has the acronym for the lab (CTE is Construction Trades Electricity).

Things I've learned from you already. Recommended practice for PK's  - numeric. Table redundancy - make the form "work more" and the table "hold less". I also see poor naming as LabID means two different things in separate tables. Screen shot of relationships
0
 
Heartless91Author Commented:
Yes, a student can only be in one lab.
0
 
Heartless91Author Commented:
Teachers log into the network to use the database. My intentions are to filter the data to their specific lab based on their login ID that is retrieved. all the information revolves around the lab acronym. I've attached images of my subforms. sfrmCompetencies
0
 
Heartless91Author Commented:
Since I'm still learning. I leave a lot of extras on the form until I know it is working. Then I streamline. sfrmScoring with extra fields
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Table redundancy - make the form "work more" and the table "hold less". I also see poor naming as LabID means two different things in separate tables. >>

  This is actually a rule of normalization; fields (attributes) belong in the table which represents that "thing" or entity.  Another way of saying that is data should only be stored once in a DB.  Right now, if you look in your tblCompetencies, the Lab field will show the same value for many records.  This is incorrect.  What you should have is this:

tblLabs - One record per lab
LabID - PK
LabShortName - Text
LabLongName - Text
CategoryID - Foreign Key to tblLabCategories

tblLabCategories
LabCategories - PK
Description - Text

tblCompetencies
CompID - PK
LabID - Foreign Key to tblLabs
Competency

  Now LabShortName is stored only once.  It doesn't belong in tblCompetencies because it describes something about the "thing" labs.  So it belongs in the labs table.

  When you need that piece of info, you use a view (query) to join the tables based on the key.  In the view, you see that field in the resultset for every row even though it has been stored in only one place.

  This will give you problems eventually.  I would advise going back and correcting the design before you go further.

<<Teachers log into the network to use the database. My intentions are to filter the data to their specific lab based on their login ID that is retrieved. all the information revolves around the lab acronym. I've attached images of my subforms. >>

  Well there are a couple of different ways of approaching that:

1. Pick a lab, display all the students in a subform, and when you select a student, display all the competencies to be scored.

2. Pick a lab, display all the competencies in a subform, and one you select one, display all the students in a second subform to be scored.

  Which way would you like to approach it?

JimD.
   
0
 
Heartless91Author Commented:
Thanks for your teaching. Step 1 for me today is to recreate the db following your recommendations. Since we are starting our Final Exams schedule, I will be scrambling to get that done. As to option 1 vs 2 on input format, if you are willing, I'd like to see both. If that is asking too much, I would prefer option 1.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I'd like to see both.>>

Their both pretty much the same thing and are done the same way.  Once I walk you though one, you can probably accomplish the other.  I'll walk you through the first, and if you need help with the second, holler.

So for this:

<<1. Pick a lab, display all the students in a subform, and when you select a student, display all the competencies to be scored.>>

  You'll have a main form based on tblLabs.

  Then a subform based on tblStudents.  The Parent/Child links for this subform will be the Lab field.  If 'Lab' in tblStudents is the short name for the lab, this is incorrect.  What you should have is the primary key of tblLabs (LabID) stored here as a pointer to tblLabs.  Again, this is somthing you should change,  but you still could specify the Lab field for the Parent/Child links and it will work.  

  So now we've got the main form and when viewing a lab, have a subform that displays all the students for that lab.  Now we want a 2nd subform that when a student is picked from the 1st subform, it displays all the scores.

  The trick for doing this is that a parent link for a subform can be a field or a *control* on the main form.  The child link must always be a field.

  The 2nd subform will be based on tblScoring and the Child link for this subform will be the StID.  To get the student ID from the 1st subform:

1. Create a control on the main form called  txtStudentID
2. Make it hidden (visible = no)
3. Now in the 1st subforms OnCurrent event, place the following code:

   Me.Parent.txtStudentID = Me.StID

  Now, when you select a record in that 1st subform, the student ID is placed in the control on the main form.

4. Finally, for the 2nd subform, the parent link property will be txtStudentID.

  As a result, picking a student from the 1st subform updates the control, which forces a refresh of the 2nd subform.

  For the scoring, you need to use an option group, and place toggle buttons in the group for each possible score.  This group will be bound (have it's controlsource set to) to Rating.

  By using a option group, you will only be able to click one button.  Each button has attached to it the value that will be stored in the controlsource (Rating) when clicked.

Let me know if you get stuck...

Jim.

0
 
Heartless91Author Commented:
Tables have been created. Attached is a spreadsheet of the data.
db-tables.xls
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Much better!

Just two points:

1. "Instructor" and "LoginID" both are attributes of an "Instructor", not of a lab.  You should have:

tblInstructors -
InstructorID - Autonumber - PK
FirstName - Text
LastName - Text
Address - Text
Phone - Text
e-mailAddress - Text
...
LoginID - Text

tblLabs
LabID - PK
LabShortName - Text
LabLongName - Text
InstructorID - FK (Foreign Key) to tblInstructors

  I added some additional fields on tblInstructors so you can see this a little more clearly; would you really want an instructors e-mail address in tblLabs on every record that they are an instructor for?  No.  Same is true for their name and loginID.

2.  Can a comptency belong to more then category?   If the answer is yes, then existance of tblCompCat is correct.  If the answer is no, then CatID can be stored in tblCompetencies.

3. Ditto #2 for tblCompLab and tblCompetencies.  If a competency can only be assigned to one lab, then LabID can be stored in tblCompetencies.

  Other then that, loads better!

JimD.
0
 
Heartless91Author Commented:
I'm understanding it now. I did have items 2 and 3 of your suggestions but then changed it.
db-tables.xls
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  Ah, there you go.  Looks good!  You should now find building your forms more straight forward.  Let's talk about this a little bit more.

  On this scoring form in the 1st subform which shows the Competencies, I said in my earlier comment that it would be based on tblCompetencies.

  But let's say besides the competencies, you want to show the category it belongs to as well.  Do to that, you need to get the Category field from tblCategories.

  So what we do is use a query and then based the subform on that rather then directly on a table.

  in the query, you would add tblCompetencies and tblCategories, then drag the CatID field from the tblCompetencies over to the CatID in tblCategories.   You now have a "join" and for each record in tblCompetencies, it will try and find a match in tblCategories based on the CatID.

 Now drag down the fields from each of the tables that you need.  First CompID, LabID, and Competency from tblCompetencies, then Category from tblCategories.
 
  Try executing the query.  You should see the category description for all the records.
 
 Save the query.  Then in the subform, set it's rowsource property to the name of the save query.

  Once you do that, you'll now see the Category field available to the form and you can display it with a control.

JimD.
0
 
Heartless91Author Commented:
My queries:
qryStudents
SELECT tblStudents.*, tblLabs.Lab
FROM (tblStudents INNER JOIN tblStudentLab ON tblStudents.StID = tblStudentLab.StID) INNER JOIN tblLabs ON tblStudentLab.LabID = tblLabs.LabID
ORDER BY tblStudents.Last, tblStudents.First;

qryCompetencies
SELECT tblLabs.Lab, tblCategories.CatID, tblCategories.Category, tblComptetencies.CompID, tblComptetencies.Competency
FROM (tblComptetencies INNER JOIN tblCategories ON tblComptetencies.CatID = tblCategories.CatID) INNER JOIN tblLabs ON tblComptetencies.LabID = tblLabs.LabID
ORDER BY tblLabs.Lab, tblCategories.CatID, tblComptetencies.CompID;

What I don't understand is how I'm to link the two subforms to the main form so that they show the toggle buttons for each of the competencies. With A2007, I have Parent and Child Link properties. The logic to me is that the Scoring subform get the student Id from the main form and the CompID from the other subform. How do I get the Scoring subform to show so many, separate records consecutively when they have the potential to be new records for every competency?

sfrmCompetencies.jpg
sfrmScoring.jpg
0
 
Heartless91Author Commented:
I have set up the db to automatically pull the correct students and competencies by tying the queries to the LoginID . On the attached graphic, frmStudents, I have added the subforms. I need the scoring subform to show rating options should the instructor randomly answer the survey. (I've seen one go through and mark all the "S's" and then go back and mark the "P's", etc)

You will notice the "1's" down the middle of the subforms. I would like that to be a counter with a running sum as done on reports. This helps the instructors track progress.

I would need the two subforms to scroll in sync.

I think it's close. I greatly appreciate your help.
frmStudents.jpg
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<What I don't understand is how I'm to link the two subforms to the main form so that they show the toggle buttons for each of the competencies. With A2007, I have Parent and Child Link properties. >>

  One you've created the subforms, they need to be placed on the Main form in a subform control.

<<The logic to me is that the Scoring subform get the student Id from the main form and the CompID from the other subform. How do I get the Scoring subform to show so many, separate records consecutively when they have the potential to be new records for every competency?>>

  These were the two setups that we discussed:

1. Pick a lab, display all the students in a subform, and when you select a student, display all the competencies to be scored.

2. Pick a lab, display all the competencies in a subform, and one you select one, display all the students in a second subform to be scored.

  What is it your trying to do?  Why are you using students for the main form?

  As for the competency scoring records, I would create those when a lab is choosen for a student.

JimD.
0
 
Heartless91Author Commented:
At this point, my intention is to base input by selecting a student. My thinking was the main form would have qryStudents as a control. Am I correct in understanding you that the main form has no assigned control and students would be selected from a combo box and the two subforms (sfrmScoring and sfrmcompetencies) would be linked to each other? I'm experimenting with them but haven't had success.

I also get only one (new) record and not a continuous form.
Thanks
0
 
Heartless91Author Commented:
Is this possible?
If I were an instructor and was the very first one in the database and my lab had 150 competencies that I needed to rate for each of my 60 students, could I choose one of my students, see a screen that showed my student and all 150 competencies (scrolling) and randomly choose the competencies I wanted to rate?

Or...,  am I going to select the student and rate the student on competencies sequentially, seeing one competency on the screen at a time?

Visually, the instructors will prefer the first scenario. Programmatically, I'm seeing the creation of [150] records per student for this scenario (or "pulling" 150 previously created records) in order for the scoring subform to display in tandem with the competencies.

The other possibility, with my limited vision, is that sfrmScoring would only show the toggle buttons and clicking on one of the choices will generate, in the background, a check for an existing matching record to edit or create a new record?????....

I look forward to seeing how this progresses.
Eric
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Eric,

<<At this point, my intention is to base input by selecting a student. My thinking was the main form would have qryStudents as a control. Am I correct in understanding you that the main form has no assigned control and students would be selected from a combo box and the two subforms (sfrmScoring and sfrmcompetencies) would be linked to each other? I'm experimenting with them but haven't had success.>>

  A form can be bound (has a rowsource set) or unbound (no rowsource).  Typically what one sees is a bound  form, in this case that would be based on tblStudents, and possibly a combo control in the header of the form that could be used to pick a student.

  Now at this point, a student can only have one lab, so right away, you could display the lab info in the main form (by adding it to the query that the main form is based on), and would have a subform in continous form view that would display the competencies and let the scoring be done.

<<Is this possible?
If I were an instructor and was the very first one in the database and my lab had 150 competencies that I needed to rate for each of my 60 students, could I choose one of my students, see a screen that showed my student and all 150 competencies (scrolling) and randomly choose the competencies I wanted to rate?>>

  This is not quite what you said above in your first comment (main form based on student).   This is option #1 from a comment I made before:

<<
1. Pick a lab, display all the students in a subform, and when you select a student, display all the competencies to be scored.

2. Pick a lab, display all the competencies in a subform, and one you select one, display all the students in a second subform to be scored.
>>


and yes, it's certainly possible so let's get it done.

  First, your main form will be based on tblLabs.  Add controls for the LabID, Short Name, and Long name in the detail section.  Call it frmScoringByStudent.   Execute it and make sure you can scroll through the labs.

  Now we need a subform for the students.  First create a query for the form (I'm assuming were going to display the students by name).

  In the query, add the student table.  Define the first column as:

Name: [Last] & ", " & [First]

 and sort it ascending.

 Next, pull down StID as the 2nd column, and Lab as the third colunmn.  Save the query as qryfrmScoringByStudentsfStudents.

  Next create the form.  Place it in continious form view.  Set the forms rowsource to qryfrmScoringByStudentsfStudents.  Put the Name and StID into the detail section.

  Try viewing the form.  You should see a list of all students.  Save the form as frmScoringByStudentsfStudents

  Now open the first form you create (frmScoringByStudent).  Add a subform control.  Name the control embfrmScoringByStudentsfStudents.

  Set the source object to frmScoringByStudentsfStudents

  Set the Master and Child links proeprty to [Lab]

  Save the form and execute it.  Now as you scroll through the labs on the main form, you should see the list of students change.

  I'm going to stop here.  Once you have this working, we'll add the scoring subform and also a combo at the top of the main form to select a lab and jump to it (instead of scrolling through all the labs).

  Last, we'll work on filtering the labs based on the instructor, either with another combo at the top of the form or by a login form.

JimD.
0
 
Heartless91Author Commented:
Done.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Done. >>

 All right!  Now we need another subform for the scoring.  First, let's create the query for it.  Open a new query and add the tblCompetencies, tblCategories, and tblScoring ScoreID as PK, StId as txt, CompID as number, Rating as txt (sfrmScoring)

  Join tblScoring to tblCompetencies on CompID and tblCompetencies to tblCategories on CatID.

  Now that setup assumes that there will be one scoring record for the student for each of the competencies.  To make life simple, I would create those records when the lab is choosen for the student.   You'd probably also want to add logic that checks if a student has already been scored for a lab and if so, warn the user that they are about to delete data.

  That should all be done outside of this as part of a "Student Maintenance Form",  "Assign Lab Form", or as some start of semester/year process.   It should also tie in with modify labs in some way because if you've already assigned competencies, you'd need to add or delete scoring records as you modify the ones assigned to labs.

  Right now, because I believe you already have all the labs assigned, I would keep it simple and just create an append query to fill tblScoring with all the required records.

  You can do that easily by joining tblStudents to tblLabs, then to tblCompetencies.  This will give you one record per student/competency combination.  Change that to an append query, specifying tblScoring, and in the gird make sure that each field in tblScoring is filled.  If you need more help with that, let me know.

  Ok, now assuming we have all our record, back to this form.

  Not sure which way you want the categoires presented to the user, but I'm going to assume by category.  You can change that easily enough if you want to.   Drag the category down to the grid, then Competency, and then ScoreID, StID, and Rating from tblScoring.  Sort ascending on Category and Competency

  Save the query as qryfrmScoringByStudentsfScoring.

  Now create a new form.  Set it's rowsource to qryfrmScoringByStudentsfScoring and set it for continious form view.    In the detail section, create controls in the detail for Category and Competency.  

  Create an option group control (call it grpRating). Set the controlsource for this to Rating.

  Within the group, add a toggle button for each rating that you want.  On each button, set the OptionValue property for one specific rating.

  Now save the form as frmScoringByStudentsfScoring.  Execute it.  You should see one record for each student / competency combination and should be able to score them.

  Now lets add it to frmScoringByStudent.  open up frmScoringByStudent and add a text control called txtStID.  This will be unbound (no control source).  Set it's visible property to false.

  Edit the first subform, open the property sheet, enter "[Event Procedure]" (you can choose it from the drop down) for the OnCurrent event.  Click the builder button (...) off to the right.  A code window will pop up.

  Enter:

   Me.Parent.txtStID = Me.StID

  Close the code window.

  Now back on the main form, add a second subform control.  Set its object source to frmScoringByStudentsfScoring.   Set the Master Link to txtStID and the child link to [StID]

  Save the form and try it!  

  The compentencies displayed should be only for the current student that is selected in the first subform.  moving from lab to lab should change the list of students.  Selecting a new student in the first subform should change the list of compentencies.

  I'll stop there for the moment.

JimD.
0
 
Heartless91Author Commented:
<<Now back on the main form, add a second subform control.  Set its object source to frmScoringByStudentsfScoring.   Set the Master Link to txtStID and the child link to [StID]>>

In trying to set the master and child Links, the unbound object is not available. Writing the code from the other subform did populate it but frmScoringByStudentsfScoring does not see it. I don't know if this makes a difference but A2007 allows me to drag the subform onto the form. I wouldn't think that would make a difference.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<In trying to set the master and child Links, the unbound object is not available. Writing the code from the other subform did populate it but frmScoringByStudentsfScoring does not see it. I don't know if this makes a difference but A2007 allows me to drag the subform onto the form. I wouldn't think that would make a difference. >>

  Just put txtStID (make sure that's what you named the hidden control on the main form) in the Master link property.

JimD.
0
 
Heartless91Author Commented:
frmScoringByStudentsfScoring does not allow me to link as it must be in the "List"
sfrmError.jpg
0
 
Heartless91Author Commented:
Got it! I didn't use the builder.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  OK.  So you have a working form at this point?

JimD.
0
 
Heartless91Author Commented:
I have tweaked the forms and am comfortable with how everything is working. I have an output question but don't know if that is best listed as another thread.

As an example:

P  Competency blah blah...................................

I need to mimic the file that was attached to the original post. What I want to do is center the "P" vertically with the competency that might word wrap and expand.

Thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Another question please.  This thread is long enough already and we haven't touched the filtering yet.  In fact those should probably be new questions as well pointing back to this one using the related question feature in the new question wizard.

JimD.
0
 
Heartless91Author Commented:
Jim did a great job of teaching. I will apply to future db's the concepts he shared.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 20
  • 14
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now