Help with Query that ALMOST works

[tjones]

Extremely new to queries and still don't understand quite how they work, but I have a query that is almost working the way I would like. :banghead:

SELECT StudentInformation.[790ID], StudentInformation.LastName, StudentInformation.FirstName, StudentInformation.DegreeType, CourseTaken.CourseType, CourseTaken.CourseStatus, CourseTaken.DepartmentID, CourseTaken.Term, CourseTaken.Year, CourseTaken.Units, CourseTaken.Grade, CourseTaken.Transfer, UnitTotal.TotalCredits
FROM (StudentInformation INNER JOIN CourseTaken ON StudentInformation.[790ID] = CourseTaken.[790ID]) INNER JOIN UnitTotal ON StudentInformation.[790ID] = UnitTotal.[790ID]
WHERE (((StudentInformation.[790ID])="790-61-5935") AND ((CourseTaken.CourseStatus)="completed"))
ORDER BY CourseTaken.CourseType;

It is pulling the information I need, but the DepartmentID is just displaying the auto number (10 or 11) instead of "Department ID = PB 510 CName"
The department ID field pulls from this:

SELECT Courses.[CourseID], [Courses].[DepartmentID], [Courses].[CourseNo], Courses.[CourseName] FROM Courses ORDER BY [DepartmentID], [CourseNo], [CourseName];
​

How do I get it to display the deptid (PB) c# (510) and name (CName)?

I tried adding the Courses table and pulling the three fields but it does not return any information when I did that. then i tried adding the courses table and linking the student information table deptID field to the three fields but again nothing returned.
any help would be greatly appreciated. I have added the return the way the top is wrote.

 

[boblarson]

Your problem is likely

1. The use of lookups at table level instead of using them only at form level. See here for more about that:
http://www.mvps.org/access/lookupfields.htm

2. Because you are using lookups at table level, it displays the description when you look at the table and that lulls you into a false assumption that it will look like that when used in a query. Well it doesn't. You have to add the table which contains the lookup values into your query here and link them in on the appropriate field.

 

[tjones]

Sorry to say the link comes up dead "Service not available".

I think I get what you are saying, "Think" being operative. Is this what I do.

1. pull the courses table into the query, add the three fields from there.
2. add to the three, in criteria "dept ID" from the ctaken table

is that correct?

 

[boblarson]

Sorry to say the link comes up dead "Service not available".

Strange, I just clicked on it myself and it came up fine.

I think I get what you are saying, "Think" being operative. Is this what I do.

1. pull the courses table into the query, add the three fields from there.
2. add to the three, in criteria "dept ID" from the ctaken table

is that correct?

No, not correct.

Pull the courses table into the query and add the three fields from there, and then LINK (not in criteria) the DeptID to the departmentID field. If you need to link the other two as well, you'll need to add that courses table in 3 times and connect only one field from each instance to the corresponding field in the coursetaken table.

 

[tjones]

It did wok when I went to firefox and entered it directly.

I did add the courses table but linked all 3 to one table. I will try addng three courses tables and linking the three once.

 

[tjones]

Ok that did not work, more likely Im not doing it right.

Here is a pic of the query. It is still not returning any results.

 

[boblarson]

Of course it isn't silly - you have all three tables linked to DepartmentID in the Courses taken table. You need to link the one DepartmentID in courses taken to the DepartmentID field in Courses, the CourseCRN in the courses taken table to CourseID in Courses_1, and I just had something dawn on me.

Your Department ID shouldn't be linked from the courses table. It should be linked from the Departments table.

 

[tjones]

Ok, I really must have messed up or just not understanding (newbie here). dept ID (combo 91) on the form when I select this it pulls up the courses table. I select the course and it puts:
pb in the dept id field - 510 in the course no - name in the course name field

but when the query was working I would get the autogenerated number (11 or whatever -long integer) listed under the department id.

All the information is pulled from the courses table and entered through just choosing the dept id on the ctaken form.

and now i am more confused then ever. it seems crazy that the db works great given all the apparent mistakes I made building it.

 

[boblarson]

Ok, I really must have messed up or just not understanding (newbie here). dept ID (combo 91) on the form when I select this it pulls up the courses table. I select the course and it puts:
pb in the dept id field - 510 in the course no - name in the course name field

but when the query was working I would get the autogenerated number (11 or whatever -long integer) listed under the department id.

All the information is pulled from the courses table and entered through just choosing the dept id on the ctaken form. View attachment 43713

and now i am more confused then ever. it seems crazy that the db works great given all the apparent mistakes I made building it.

Post the row source from the combo box.

 

[tjones]

The row source is:

SELECT Courses.[CourseID], [Courses].[DepartmentID], [Courses].[CourseNo], Courses.[CourseName] FROM Courses ORDER BY [DepartmentID], [CourseNo], [CourseName];

listed in the first post as where the deptid field is pulling the information

 

[boblarson]

So do you, or do you not, have a table which has only department info like:

DepartmentID
Department Name


If you do that is the table which needs to be added to the query to show the department name.

 

[tjones]

No. Just the course table. I thought I had split the tables fairly well in to smaller relevant pieces together.

 

[tjones]

Also, don't know if it helps at this point but I was looking at the difference between table lookups and form lookups. When I built this database, I used the forms, that is i added the combo box to the form and went through telling what table to use for the field.

 

[tjones]

So I have made a small advance on the query. It is now returning the information, only I can not get it to insert the course number and course name to the returned report.


Any help in getting this query to work is greatly appreciated!

 

[boblarson]

So I have made a small advance on the query. It is now returning the information, only I can not get it to insert the course number and course name to the returned report.
View attachment 43728

Any help in getting this query to work is greatly appreciated!

Umm, your screenshot shows your courses taken table Department ID linked to the course name field on Courses_1 and the CourseNo field on Courses.

You have incorrect linking there. Somehow my description didn't get through to you. So, let's try it in picture format:

 

[tjones]

I think that the problem lays in the fact that Department ID on the Course Taken table/form field contains all the information for the deptid, courseno, coursename.

it is pulled from the courses table which has separate fields for all the information and then split on the course taken form to display in the correct fields.

SELECT Courses.[CourseID], [Courses].[DepartmentID], [Courses].[CourseNo], Courses.[CourseName] FROM Courses ORDER BY [DepartmentID], [CourseNo], [CourseName];

When it displays the department ID field on the report it shows the autonumber (long interger) of the assigned course from the courses table.

The course CRN etc are different information.

 

[tjones]

I also made a seperate query that works and returns just the same info so it can be set up either way.

I included a graph that shows the field on the Course Taken form that is pulled from the courses table. :banghead: