I've been given a database to create and I'm having a few problems with some of the required queries, this one in particular. What I need to do is to create a query that lists the number of people wanting a particular option on a course, but there are multiple options for this particular field. Basically, it's a database of workers and what type of training they would be interested in taking. I'ce created a form for the query that has it's own table (table1), and that when you open the form, clears the contents of table1 and when you fill in the form, select the training option from the combo box, it puts your selection into table1. What I want the query to do is to count the number of workers with there first, second, third, or forth training option equal to the course in table1, and dislay total number that selected the course somewhere. Then I need to list the workers and worksite that they attend. The fields are as follows:
Table name: Worker1
Fields:
course option1: option1
course option2: option2
course option3: option3
course option4: option4
Table name: table1
Fields:
form input field: field1
So far for the "number" query, I have the following SQL:
SELECT Count([option1]) AS [O1] FROM Teacher1 WHERE [option1] = field1.table1;
But this comes up with an input box for "option1" and then an input box for "field1.table1". I've also tried table1.field1" thinking I may have got the order wrong, but the same thing still occurs. Any help anyone can give me on getting this query to get its input from field1 in table1 and giving the required outcome would be greatly appreciated.
Table name: Worker1
Fields:
course option1: option1
course option2: option2
course option3: option3
course option4: option4
Table name: table1
Fields:
form input field: field1
So far for the "number" query, I have the following SQL:
SELECT Count([option1]) AS [O1] FROM Teacher1 WHERE [option1] = field1.table1;
But this comes up with an input box for "option1" and then an input box for "field1.table1". I've also tried table1.field1" thinking I may have got the order wrong, but the same thing still occurs. Any help anyone can give me on getting this query to get its input from field1 in table1 and giving the required outcome would be greatly appreciated.