LookUp values in a query

[ricshaw7gm]

I am building a query in Access 2003. I have two tables, A and B. Table A contains a list of schools. Table B contains a list of educational courses and one field is a lookup field which looks up the name of the schools in Table A to show what courses are being run at every school. In my query, I wish to view courses by individual schools to be able to see all the courses held at a particular school. However when I run the query having put i "Like School" n the school field I get the numerical value as shown in the lookup table and not the textual name of the school which is what I want to see. I hope this makes sense and would dearly welcome advice on how to achieve my wish.

 

[Beetle]

You have two problems here (you may have more than two - I don't know - but you have at least two).

Problem #1 - You are using a lookup field in a table, thus violating the (cue deep booming voice coming from above) SECOND COMMANDMENT OF ACCESS. If you want to see all 10 Commandments, see the sticky in the General section of this forum. However, the SECOND COMMANDMENT OF ACCESS (fading echo here....) concerns using tables for data entry and, more specifically, lookup fields. The lookup field obscures what is truly being stored in the table, so when you create queries and try to apply criteria to that field, it either fails completely or displays data other than what you expected. This is a case of "what you see is not what you get". Combo boxes (aka - "lookup fields") should be used in forms, not in tables. There are several other reasons why you should not use lookup fields. For more on that see;

http://access.mvps.org/access/lookupfields.htm

Problem #2 - In regards to your tables in general, what you've described is a many-to-many relationship. A school can have more than one educational course, and a given educational course can be taught in more than one school. To model this relationship you need three tables. One for the schools, one for the courses and a junction table to manage the relationship between those two entities. Something like;

tblSchools
*********
SchoolID (PK)
SchoolName
(other fields specific to each school)

tblCourses
*********
CourseID (PK)
CourseName
(other fields specific to each course)

tblSchoolCourses
**************
SchoolID (FK to tblSchools)
CourseID (FK to tblCourses)
StartDate
EndDate
(other fields specific to the relationship between schools and courses)