Need Help with 4 table query

[tjones]

I have a query that is working (the report gives me the information I need) but it will only display the Department ID (12 etc.).

I have joined the using #2 "Include all records from CourseTaken and only those from Courses where joined fields are equal" as the other two join types do not work with the Courses table in the query.

What am I doing wrong here? I am fairly new to querys and still not sure how to set up more than a simple one.

 

[Kraj]

Try selecting DepartmentID from Courses instead of CourseTaken. I'm not certain it will work but that's the first thing I'd try.

 

[tjones]

Good thought, but all that did was remove the display of the DepartmentID from the report.

 

[Kraj]

Try adding CourseID to your query. Since CourseNo and CourseName are dependant on that field rather than on DeptID, I don't think any of the fields in your query are currently pulling any records from Courses.


Other possibilities that spring to mind:


Does DepartmentID in both tables have the same data type?

I don't see a primary key defined in CourseTaken. I can imagine that might cause oddities like this.

Also, I'm a big believer in not being afraid to ask stupid questions so... there is data in the CoursesTaken table, right?

 

[tjones]

No your right. Department ID in (CoursesTaken) is the same as Course ID in courses, but when I try and join those two fields I get the error

"Type mismatch in expression"

which I though strange as they are the same numbers, but Department ID in Courses is things like BIOL.

 

[Kraj]

Well, without seeing your data and table structure it's hard to pinpoint the problem, but that message ought to mean that Department ID and Course ID have different data types defined. CourseID in Courses is the primary key, which should mean it has a data type of Autonumber. Therefore, DepartmentID in CourseTaken should have the data type Number in order for them to be linked.