selecting multiple values with unique primary keys on the same form

[UH60_Driver]

Hello,
I have been a member for a week or so, and have been reading, but this is my first post. You will soon figure out I am new to Access, and am having issues with one particular form field. I purchased the InfiniteSkills beginning and advanced lessons for Access and have gone through them, but cannot find an answer to solve my problem. I did find a similar post, that I think was addressing the same issue, but I did not understand the answer. That post is here (https://www.accessforums.net/forms/n...lti-57112.html)

My Issue: I have a form (a diagram of my problem is attached) that is going to ultimately serve as an academic instructor schedule. I have three classrooms, and thus, need three different instructors in them... all teaching the same lesson at the same time. I pull my instructors from tblEmployees. tblEmployees is run through a query that looks for Primary Academic Instructors for that week. The result of that query is selected and stored in the table for each individual class that is taught. In the example below, the class, for demonstration purposes is coded SA. The table fields for this class are: SA_ID (Primary Key), SA_Date (the date the lesson is taught), and SelectSA_Instructor (which is a drop down menu filter by the Primary Instructor Query).

My issue is that I cannot figure out how to select a different instructor for each classroom. Once I select an instructor for one classroom, it populates that selection into the other classrooms. Just in case someone is wondering.. I have no need to actually store the value for which classroom the lesson is being taught in, only the instructor and date the lesson was taught. Any help would be GREATLY appreciated. Once I solve this issue, I will then create the rest of the tables for the rest of my classes and continue the form to show all classes for a particular day. Thanks in advance.

[June7]

I presume those colored classroom boxes are just that, just box controls, not subforms.

You have 3 comboboxes bound to the same field so of course they will all 3 show the same data, change one and they all show the change because they are all pulling data from same record.

Each instructor selection needs to be a new record. Delete two of the textboxes and set the form for Datasheet or Continuous view.

[UH60_Driver]

Yeah, that is what I have been banging my head with. The form is going to be used by folks that really don't understand database entry. I was trying to make it graphically user friendly. Is there no way to make each of the combo box's create a unique value entry into the table with it's own PK?

[ssanfu]

Once I solve this issue, I will then create the rest of the tables for the rest of my classes and continue the form to show all classes for a particular day.

It sounds like you are going to make a separate table for each class? or Classroom?
This is the wrong approach - you will soon be or .
There should be one table - instructors teach a class on a date in a classroom.


I would like to see your table structure relationships.

This form is not actually going to display data held in the table. It is going to write data that user selects via the combo box to the tblLessonSA table

There are a couple of ways to do this. One is as June suggested - use a form in continuous view or use a main form/subform arrangement.

Another way is to us un-bound controls on an un-bound form and use VBA to insert the data into the table(s). This is a LOT more work because you have to write the VBA to do everything.
You have to write the validation code to check the controls, check to ensure you do not have two records for the same class, instructor and date (and maybe classroom).
But, using un-bound controls allows you ultimate control over what the form looks like and when/how the data is entered into a table (or tables).

[UH60_Driver]

ssnafu,
thank you for the reply. My relationships are pretty basic at this point. I have spent a lot of time thinking about how I want this DB to look with a big focus on normalizing the tables used. I did consider making a table for each day and then within that day, every single "event" that occurs. I guess that is going to be the only way that I can make this happen as I have very little experience with VB. That is my next class that I am taking. I have kinda jumped in with both feet on this project thinking that I could make do with the knowledge that I have gained thus far. This task is daunting. Thanks again for your suggestion.

[June7]

A separate table for each day - no, no, no!!!

You might get away with a subform for each class entry. I have never tried this - multiple subforms bound to the same datasource.

Or have form set up with one combobox and a button for 'Enter Next Class' which will move the focus to a new record for the user.

But my first recommendation would be to educate users on using your db. Extensive familiarity with data entry is not necessary. Don't underestimate users. Who doesn't understand selecting from a combobox? Anyone browsing the web knows about interacting with forms.

[ssanfu]

To me, the most important part of designing a dB are the tables. I design on paper (or cardboard or whiteboard or....), then write in data to see if the design works. Even when modifying an existing dB that is in service, I will print out several copies of the relationship window and sketch on them, then try entering data (on the paper) to see if the modification has a prayer of working.

Sketch on paper to see how you think the forms should look/interact. Designing on paper is a lot easier to see the big picture than jumping in and creating in the computer.

Describe in English (not computer jargon) what you goal/project is/does.
A mission statement and business rules will greatly help you (and us).

Post your table designs and ask for critique.
When you hit a wall, ask for help.



You might want to work through the tutorials at
http://www.rogersaccesslibrary.com/forum/forum46.html

[UH60_Driver]

To all who have replied to this post; Thank you. I have been re-thinking the tables and putting a lot of thought into how they relate to one another with specific emphasis on the relationships and the PKs. My issue is this... I cannot post what has been asked of me. I know you are smart people, so I do not want to try and conceptualize this database in a form that parallels what I need, but is not exactly what I need. The reason being... I am working on a database that will be eventually ported onto a classified server for the Dept of Defense. My attempt here to create this DB is not the product of a requirement, but rather a desire to make what we are doing better and more efficient. The "requirements", as in mission statement and business rules, would breach a level of classification that I cannot and will not get in to in a public forum. I may post my DB relationships and tables, which contain no classified material, but fielding questions on those tables and their intent could not be answered.... so I am at a loss for how to proceed.

[orange]

There is an 8 part series of youtube tutorials by Dr. Daniel Soper that may be useful to you.
They start here. The ones on modelling and database design are probably most applicable to how you should proceed. All 8 are very good.
As others have said, getting your tables designed to meet your business requirements is the most import thing. Failure to do that at the start will force you into a number of "work arounds" and fixes that will never end.
Spend time on design and vetting that design -and check it out with test records before getting to deep into programming.
Good luck.

[UH60_Driver]

Thank Orange. I am currently going through Dr. Soper's YouTube videos now. Will absorb that, try to put it to use with my needs, and then get back on here with what I have come up with. More to follow.

[UH60_Driver]

orange,
Dr. Soper's tutorial is mind blowing. I really wish I would have had this before sitting down and trying to "eat the elephant". I am currently on lecture #5, and the picture is becoming clearer... and at the same time... more fuzzy. I have to go back to ground zero and re-imagine everything. But the problem set that I originally posted is becoming clearer. I have not solved it yet, but I know where the problem lies at this point. I am going to continue watching the lectures to hopefully find out the combination of PK, FK, and/or composite keys to make this happen.

This thread may go stale for a while I crunch the basics. I will return and post tables/relationships and keys when I have something that I think makes sense. The good new is this: I know I was thinking way too macro. Following Dr. Sopel's advice will allow me to break the material out to a level that classification of the material will not be a problem. As he is explaining what it should look like, I am trying to examine my needs as it applies to his examples. Doing this has guided me down a path that the information will be generic enough to apply to any number of classroom settings, so I "should" be able to upload examples of everything without risking an unintended breach. Thanks again.. SO SO much. This series is eye opening.

Also, ssanfu, as I go through the lectures, I have been white boarding my table organization. What I thought was normalized was far from it. I had way too many attributes within tables that should have been broken out into their own. Trying to get these things at their lowest level and relate them to one another is going to be a process, but very necessary. Thanks for your input.

[June7]

IMO, it is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." (or is it vice versa?)

[orange]

I'm glad you find the tutorials well done. They are the best I have found (so far). It's nice to be able to go back, review, rethink. As you are finding, don't get fixed on a solution too early.
Steve (ssanfu) and I have discussed the importance of building a model showing tables and relationships and testing/vetting the model before committing to a design.
There is a brief description here re stump the model. Pencil and paper, data modelling software, cardboard and string, .....are great tools for this.

There are other tutorials that you might use to reinforce some concepts/approaches. Most any video by Prof Jennifer Widom on database or relational model is very good.

As June says, Normalize until it hurts, is a popular theme. Most participants of this and other forums do not understand the importance of Normalization, or "feel they know better"--- but sooner or later most return --stuck in an issue that proper design would resolve.

Make a list of your requirements, gradually adding more detail --avoid jargon. Bounce it off colleagues (if possible/practical) until there is a mutual clear understanding of all details. Now you're getting close to the business rules that are the base of information that will identify your tables and relationships.

Good luck with your project. And remember, as some will advise, there's more than 1 way to skin the cat.