Hi There-

I had no idea generally where to post this as I was not sure if this was just a form question or a table relationship question, etc.

I have a database for Student Grades (version 2 -- i messed up version 1 and now have to rebulid from scratch, but that's another story). Please see attached document for list of tables & fields that are associated with this issue, problem.

What should happen is when I open a Form (frm_Blocks) based on tbl_Blocks, I have a subform (frm_Rotations) that is linked to forms based on (tbl_Rotations). Here I will enter the names of the students that need to be graded for a specific clinical rotation. Within the frm_Rotations I have a subform -- in datasheet view (so I guess that's a subform subform?) for something else, which works exactly the way I want (it is also a datasheet view).

What I need to somehow figure out. Is with the frm_Rotations there are 3 yes/no controls. If I click the yes/no control for Case Presentation for example, I would like it to transfer the RotationID & StudentIDs to the new table tbl_CasePresentations without needing to open the form (or it could be a hidden form?) and clicking another button or something to that nature.

Maybe I am just doing something wrong with my relationships or something or perhaps there is a more efficient way of doing this. But this is the way in my mind I thought it could/should work? I won't continue on until I figure out this step in case there are easier ways to do this

Any thoughts or help would be great.

Regards,

Daniel

Do you want it to happen immediately when they click the checkbox? What if they check and then uncheck?

I don't have a very good understanding of the real-world situation. Does a rotation involve more than one student? That is, for a given block it seems there may be many students, but is a rotation the intersection of a single student with a single block? The way it is outlined, a Block may have several students, and a student may be associated with several blocks, but a rotation is one student and one block.

Since Presentations is related to a Rotation, and a Rotation has just one Student, it should be possible to have another subform on the Rotations subform without the need to copy the data as such, but rather taking advantage of the relational capabilities of Access.

Hi Bruce-

Here is the real world situation as it is seen through my eyes.

1. ) Block is defined as a period of time in which is student is on a specific rotation. There are multiple rotations, in my work, there are 3 specific rotations.
2.) Rotation is the actual rotation, for example Diagnostic Radiology, Advanced Imaging, Ultrasound
3.) Student is the entity for which will be participating in the said rotation. A student can only be on one rotation at a time during the given block, but may return during a different block for a different rotation.

How I originally wrote the database did not account for the different blocks/rotation scenarios and did not account for the fact that the student would return for different rotations. It was a mess, but after 24 hours I rewrote the entire database. In my eyes it is complex, but in Access terms I believe it to be probably relatively easy in terms of coding.

At any rate what I have done was created 6 different forms and made them subform upon subform upon subform. It actually is working out quite nicely and I have solved my initial issue. Though I am sure there is something that would work easier, it is working and i don't want to fix what isn't broken until it becomes broken!!! ;-)

Thanks for the responses.

I had wondered if block was somewhat analogous to course in a student/course database. That is, there is a block (e.g. ultrasound) that is the same time period for a number of students, so one specific block would be "attended" by several students. Now it seems block is a period of time for a specific student, not a group of students, in which case Student, not Block, is the top-level table. One student may have many blocks, but one block is for one student. Blocks would be related to Students (one student, many blocks). A rotation, if I am using the term correctly, would be ultrasound, etc. One block would have one rotation, so RotationID is a field in the Block table. There can be a lookup table to supply the RotationID. The Rotation table would be related one-to-many to the Block table (one rotation such as ultrasound would be in many block records).