I am having a little trouble with my database for my studio, i have three tables and what i am trying to do is create a form in which i have a combo box and a sub form. in the combo box it contains all the class in which the studio teaches and when the class is clicked on in the combo box the sub form will display all the students within those classes.
This database is constructed up of three tables,
1) StudentTable: StudentID, FirstName, Surname, Address, DOB, PhoneNumber
2) ClassTable: ClassID, ClassName, Day, Time
3) SignInTable: SignInID, CLassID, StudentID week 1, week2 week3
(the week in the sign in table are the where payment amounts would be placed)
I have also created a Query in which is meant to power the subform. so when the class fromt he drop down box is selected all the setudents in the class plus all the payments(week 1, week 2 week 3, etc.) are displayed in the sub form.
the SQL for the query is:
"SELECT [Student Table].Surname, [Student Table].Firstname, [Sign in].[Week 1], [Sign in].[Week 2], [Sign in].[Week 3], [Sign in].[Week 4], [Sign in].[Week 5], [Sign in].[Week 6]
FROM [Student Table] INNER JOIN (ClassTable INNER JOIN [Sign in] ON ClassTable.ID = [Sign in].[Class ID]) ON [Student Table].[Student Number] = [Sign in].[Student ID]
WHERE (((ClassTable.[Class name])=[forms]![Form1]![Combo0]![AfterUpdate]));"
Any help will be greatly appretiated