Query Problem - Design Problem?

R

Ripper

I have a problem. I have a table(tblStudents) that conatins a list of
students (Name, LocID, Ethnicity, etc.) I have another table (tblWatch) that
contains a list of student IDs and potential watch groups for them to belong
to (New Students, Last Year Test Failures, At Rick of Failing, etc) as Yes/No
fields. Students can belong to more than 1 group.

tblStudents is the complete list of students in the district and is updated
weekly. All the queries look for the max of upload date to keep only the
current students in the queries.

tblWatch is created a record at a time by administrators as they add kids to
the different groups. This is will not contain all the kids in the district,
only those in watch groups.

What I would like to do is create a form that contains all the students on a
particular campus and then checkboxes for all the potential groups the
studetns could belong to. I created a query and had the join between LocIDs
set to show all records in tblStudents. However, it is not updatable, unless
there is already a record created in tblWatch. How can I create a query/form
like this? I know you'll need more information, just let me know what you
need.
 
B

Beetle

I recommend you re-think your table structure. What you are doing now is
poor design. What if you need to add or remove watch groups in the future?
Not only will you need to add or remove fields from your Watch table, you
will also have to redesign every query, form or report that is based on
that table.

You have a many-to-many relationship (a student can belong to many
groups and a group can have many students), so you need a third
(junction) table to define the relationship. omething like;

tblStudents
********
StuID (PK)
FirstName
LastName
BirthDate
etc.

tblWatchGroups
************
GroupID (PK)
GroupName
etc.

tblStudentGroups
*************
StudentID (FK to tblStudents)
GroupID (FK to tblGroups)
other fields that are related specifically to a Student/Group record
(StudentID and GroupID could be combined PK or you could a separate PK field)

With this structure, any student could belong to as many, or as few, groups
as are necessary. Also, if you need to add more groups in the future, you
just add another record (not another field) to tblGroups.
 
R

Ripper

Ok. I knew there was a problem with my design, but now that I created the 3
tables, how do I put them together to get my end result? I want the
principal to be able to easily select groups while viewing every student on
the campus. I guess I don't "see" how to build off of this type of query.
 
B

Beetle

The typical approach in a case like this is to have a Main form (based on
tblStudents - or a query thereof - in your case) with a subform based on
the junction table (tblStudentGroups in this example). The Master/Child
link would be StuID. In the subform, you would typically use a combo box
for selecting the groups associated with that student. The combo box
would have properties such as;

Control Source = GroupID (from the junction table)
Row Source = Select GroupID, GroupName From tblGroups
Order By tblGroups.GroupName
Bound Column = 1
Column Count = 2
Column Widths = 0",1.5" (or any width that works best for the 2nd column)
 
R

Ripper

I already have a subfrom that will create a student by student approach.
Although you are correct that I would need to add a field to the table to
create a new group. What the principals are wanting is a form that opens up
with a list of all their students and a list of potential groups for them to
check and select.

I realize that is a TON harder, but it is what I am attempting to create.
Any tips?
 
C

Clif McIrvin

Ripper said:
I already have a subfrom that will create a student by student
approach.
Although you are correct that I would need to add a field to the table
to
create a new group. What the principals are wanting is a form that
opens up
with a list of all their students and a list of potential groups for
them to
check and select.

I realize that is a TON harder, but it is what I am attempting to
create.
Any tips?

I thought that is what Beetle suggested. He suggested a dropdown list
of choices instead of columns across the screen, but it's still a list
of choices to choose from.

If the principals need the ability to select multiple items from the
list of choices, use a multi-select list box control. You can also
write code to expose the dropdown list so that the user doesn't need to
click on the dropdown arrow: me.name_of_your_control.dropdown perhaps in
the gotfocus event for the control.

Search these groups for multi-select or multiple select for more
discsussion.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top