How do I solve this problem?

[Tsung90]

Hi, at the moment I have this query:

SELECT Feedback, Exercise_Class_ID, Count(*) AS Total_Per_Exercise_Instructor
FROM tblExerciseClass
WHERE (((tblExerciseClass.Feedback) Between '0' And '1')) AND (((tblExerciseClass.Date_Exercise_Class) Between '02/01/12' And '03/01/12'))
GROUP BY Feedback, Exercise_Class_ID;

I want to include the name of the exercise instructor from tblExerciseInstructor in the above query. The name of the field = Full_Name. In the tblExerciseInstructor, each Exercise_Class_ID represents an exercise instructor (i.e. EC00001 is taught by Thomas Howard). The query shown above is just from tblExerciseClass. How do I do this?

Many thanks.

 

[Pat Hartman]

You have to join to the instructor table. The query as you have it counts by class. I will add instructor to the query but that may be too low a level. If it is, just remove Exercise_Class_ID from the select and group by.

SELECT Feedback, Exercise_Class_ID, Full_Name, Count(*) AS Total_Per_Exercise_Instructor
FROM tblExerciseClass Inner Join tblExerciseInstructor ON tblExerciseClass.InstructorID = tblExerciseInstructor.InstructorID
WHERE tblExerciseClass.Feedback Is Not Null AND (((tblExerciseClass.Date_Exercise_Class) Between '02/01/12' And '03/01/12'))
GROUP BY Feedback, Exercise_Class_ID, Full_Name;

You didn't mention the ID that relates the two tables. It should NOT be a name field if that is what you are using. Names should be separated into first and last and more if your data warrents it (prefix, sufix, etc). If the name fields are not separated, it is hard to work with them. For example sorting by last name is a problem if the field is first name then last name. Writing a letter is difficult because it looks really stupid to address a letter Dear Joe Doe, III. or Dear Doe, III, Joe which is what would happen if you stored the names last, first.
I also changed the criteria. I don't know what 0 and 1 represent but you have them in quotes so they are text and that means they will be evaluated as text fields. A silly example is '10' is actually between 1 and 2 rather than following 9 and '20' is between 2 and 3 rather than following 29. The bottom line is don't try to use ranges with text values.

 

[Tsung90]

I tried what you told me to do but it comes up with the error message 'the specified field 'Full_Name' could refer to more than one table listed in the FROM clause of your SQL statement'.

 

[Pat Hartman]

I tried what you told me to do but it comes up with the error message 'the specified field 'Full_Name' could refer to more than one table listed in the FROM clause of your SQL statement'.

Whenever you join two tables that have common columns, you may need to disambiguate their names.

Select tbl1.FldA as tbl1FldA, select tbl2.FldA as tbl2FldA

I just chose to prefix the name with the table name but do whatever makies sense - tbl1.City as BillingCity, tbl2.City as ShippingCity.