Filtering based on multiple selection

brichard

Registered User.
Local time
Today, 16:15
Joined
Apr 13, 2012
Messages
28
Hello

Could you please help me with the following issue?

Please see a simplified version of my database below. (The tables are containing hundreds of records.)

STUDENTS
(record1)
id - 1
name - student1
course1 - 101
course2 - 102
course3 - 106
course4 - 109
course5 - (blank)

(record2)
id - 2
name - student2
course1 - 102
course2 - 106
course3 - (blank)
course4 - (blank)
course5 - (blank)

COURSES
101 - editing
102 - acting
103 - directing
104 - producing
105 - screenwriting
106 - documentary
107 - effects
108 - drawing
109 - relaxing


I would like to create a form, what I could use to produce a query showing all students who are attending the selected courses.
For example I would like to see all students attending the "acting" course.
I am selecting "acting" on the form, running the query from a button and getting both records.

The difficulty is that the courses are in different columns (5 columns in total) in the STUDENTS table and I would like to select multiple COURSES and get all STUDENTS attending any of the selected courses.

My idea was to create a form with checkboxes and create a filter based them, but I am lost.

(Please let me know if I have a design error, I can re-design the tables.)

Your help is much appreciated.
 
Before you go any further, you need to normalize your schema. Whenever you have more than one of something, you have many. Once you have many, you need a separate table. Therefore, you need an additional table which is a specialized type of table sometimes called junction table or relation table. It is used to connect students to classes. Once you have created this table, you'll need to change your form to a main form showing student demographic data and a subform showing the courses he is taking.

tblStudents
StudentID (autonumber, primary key)
FirstName
LastName
etc.
tblClasses
tblClassID (autonumber, primary key)
ClassNum
ClassName
etc.
tblStudentClasses
StudentClassID (autonumber, primary key)
StudentID (long integer, foreign key to tblStudents)
ClassID (Long integer, foreign key to tblClasses)
Grade
Etc.
 
Thank you Pat

I knew something was wrong, thank you for pointing me in the right direction.

The tables look like this now:

tblStudents
StudentID (autonumber, primary key)
FirstName
LastName
tblClasses
tblClassID (autonumber, primary key)
ClassNum
ClassName
tblStudentClasses
StudentClassID (autonumber, primary key)
StudentID (long integer, foreign key to tblStudents)
ClassID (Long integer, foreign key to tblClasses)

Could you or anyone help me further?
 
Hi,

Firstly you need to set up the relationships between those tables. Go into the relationship editor and join:
tblStudents.StudentID to tblStudentClasses.StudentID
tblClasses.ClassID to tblStudentClasses.ClassID

Now go to the properties for each of the relationships and check the option "Enforce Relational Integrity"

From your description I would probably use an unbound main form with a drop-down box listing the classes, and a subform based on a query using tblStudents and tblStudentClasses.

Create a new form, say called frmStudentClasses and don't change the record source. Add a combobox called cboClasses. Change the number of columns to 3, the first column width to 0 and the bound column to 1. Set the row source for the combobox to tblClasses (note that the ClassID field needs to be in the first column, otherwise you will need to change the bound column and column widths to hide the ID).

Now create a subform, say called sfmStudentClasses, listing the students in the selected class, based on the following query:

Code:
SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=[Forms]![frmStudentClasses]![cboClasses]));;
Now insert the subform onto the main form.

Lastly you need some code to update the subform when the combobox selection changes. This code can go in the AfterUpdate event of the ComboBox.

Code:
Private Sub cboClasses_AfterUpdate()
  Me!sfmStudentClasses.Requery
End Sub
 
Hello Sparks80

Thank you for your prompt response.

Can I make multiple selections in a drop-down box?

I would like to be able to select multiple courses from the available 70, need to have an option to select 0 to all courses and get all students attending any of the selected courses.

I will save the selection in excel and use it to send a mail merge to the students about the changes in their courses.
 
Hi,

Sorry I hadn't appreciated the need to select multiple courses at once.

Instead of using a combobox you could use a listbox, which allows multiple selections. I will write some code for you that will set the row source of the subform accordingly.
 
OK, if you set the subform record to the following by default, this will load the subform without errors, and no records selected:

Code:
SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=0));;
Instead of using a combobox use a listbox called "lstClasses" and set the multi-select option to true (it's on the "Other" tab I think). Now add this code to the listbox AfterUpdate event.

Code:
Private Sub lstClasses_AfterUpdate()
    Dim I As Integer
    Dim strSQL As String
    
    strSQL = "SELECT * FROM tblStudentClasses INNER JOIN tblStudents ON " & _
        "tblStudentClasses.StudentID = tblStudents.StudentID WHERE "
    
    With Me!lstClasses
        For I = 0 To .ListCount - 1
            If .Selected(I) Then
                If Len(strSQL) > 116 Then
                    strSQL = strSQL & " OR "
                End If
                strSQL = strSQL & "tblStudentClasses.ClassID=" & .Column(0, I)
            End If
        Next
    End With
    Me!sfmStudentClasses.RecordSource = strSQL
End Sub
Hopefully you will now see a list of all students/classes for your selection in the listbox.
 
:D

It's a bit complicated for me, so I have created this template database (attached) and once I do understand how is it working, I will move on to the main one.

Where should I insert this?
SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=0));;

Thank you
 

Attachments

Hi,

I'll have a look at this in the evening (UK) as I only have Access 2007 at home ;)
 
I created a small example some years ago so I will attach it here. It shows customers and venues. Just think of them as Students and classes. it includes two main forms which allow you to view the intersection data from the perspective of the customer or the venue. No code is required to implement this solution. Simply properly structured tables and forms.
 

Attachments

Hi,

Pat is correct, you can view all the records by using a main form based on classes, and a subform based on StudentClasses and Students.

The disadvantage of this set-up is that you can only view records for one class at a time.

I have modified the sample database to show you my method of doing this, and see which you prefer.
 

Attachments

This is amazing.

My brain is too flat for today, will try to implement it tomorrow morning and get back to you.
 
sparks,
Did you actually intend for the listbox to be multi-select? It could be useful but could also be confusing. If you use the multi-select, it is important to have a clear button otherwise, you have to deselect each selected item.
 
Hi Pat,

Yes, if you look at post #7 it reads "I would like to be able to select multiple courses from the available 70". I hadn't appreciated this at first which is why I initially suggested a ComboBox, rather than ListBox.

Good advice about the clear button.

It is also worth using a command button to update the subform, so that the user can make the selection, and then update the form, rather than after each individual list item.

I've made the changes to the sample database, including extra buttons for "Select All" and "Invert Selection". Have a look at the updated version attached.
 

Attachments

I actually prefered the way it worked without the update form button. The update button doesn't really update, it just shows selected data. Originally, it was showing the data as it was selected.

Going back to the original post, you would only use a multi-select list box if you had a multi-value field (ug!) or if you were selecting multiple records for viewing on a form or report (your example) or if you were willing to write code behind the form to create individual records for each item selected.
 
Thank you for helping me with this.

All of your versions are great, but for my task the one in #11 is the most suitable.

I really don't want to abuse your time, but moving forward I am facing even more issues and would be grateful if you could have a look at the following:

Some students have multiple email addresses. Need to send it to all of their addresses to make sure they are receiving it.
I have created a new table with the emails.
sfmStudentClasses should show all records per email address (multiple names).
Instead of the emails I am receiving #Name?

I also have an exclusion list with students not affected by these mailings (they are in another payment package).
Would like to exclude these students from the mail merge.

As last step I would like to export sfmStudentClasses to xlsx but getting an empty spreadsheet.
Is this related to some "after update"?
 

Attachments

That's ok, I have started helping on this forum because I quite enjoy Access, and it is a good way to learn myself!

I'll take a look tonight.

It shouldn't be too hard to select the data you want to export, both for multiple email addresses, and students that don't need to be included.

I have a fair amount of experience of getting Access and Excel to communicate, so that shouldn't be a problem. Can you remind me what version of Access and Excel you have.

Thanks
 
Hi,

I have removed the Exclusions table from the database. There is potentially a single exclusion for each student, therefore you would have a 1:1 relationship between your students table and the exclusions table.

I think this will potentially make the application slower, particularly when there are large numbers of records.

The solution is to include the exclusion information in your students table. I have added a yes/no field to the students table called ExcludeStudent. The query for the subform will only display records where the value of ExcludeStudent is False.

I have written some VBA code behind the Export button that will open Excel and export the data into a new workbook.

Because I have Access and Excel 2007 you will find that the reference for the Excel library is missing. To fix this press ALT+F11 to open the VBA editor. Go to the References option on the Tools menu. Scroll down the list and check the box next to "Microsoft Excel x.0 Object Library". I think for Office 2010 the version number is 14.0.

Finally I have changed the code to use filters rather than modify the code of the subform. Hopefully the subform recordset will make more sense to you this way.
 

Attachments

In my main database I got the following error when trying to select items:
"Runtime error '3464' Data Type mismatch in criteria expression"
for the line:
.Form.FilterOn = True

I suspect this is because my IDs are texts, no numbers.
Is there a way around or do I need to change them to numbers?

Thank you
 

Users who are viewing this thread

Back
Top Bottom