Union or Joined Queries

[funderburgh]

I wish to join the results of three queries.

My application is in a special needs school and I am calculating GPA. This requires converting numeric to ordinal grades and averaging the results over a time period for a unique group of students. There are three conversion algorithims for three spearate kinds of classes.

This has proved to be too complex for one query.

I now have three queries, each selecting the correct student group and calculating the GPA - one query for each class type. The combined results would create a group of records covering each student's schedule for the specified time period that could be averaged to obtain the GPA.

I have combed my manuals and this fourm, but can't put together if this is possible.

Any help would be welcome.

 

[boblarson]

Normally, since you would include the StudentID in each of the queries so as to identify who it belonged to, you could then join each table in another query that would display the results of each query as a field. So, with 3 queries you would start a new query and then add each of the three to the list, join them on StudentID and then put each field you want into the Query by Example grid.

As far as the joins go, if all students have a record in each of the three queries, you would just have to do an equal join. If not, you would need to have the one query or two that have a record for each student joined with an outer join to the one (or two) that do not necessarily have a record for each student).

 

[funderburgh]

I am grateful for your answer, but there are some terms I don't understand. When you say start a new query and "add all three to the list", do you mean include them in the top portion of the query builder window?.

When you say "join them on studentID", is that accomplished by dragging between the query boxes so that a line connects the three student ID fields?

This is the SQL produced by the above action:

SELECT
FROM [Class Rank Source Normal] INNER JOIN ([Class Rank Source Special] INNER JOIN [Class Rank Source Modified] ON [Class Rank Source Special].StudentID = [Class Rank Source Modified].StudentID) ON [Class Rank Source Normal].StudentID = [Class Rank Source Special].StudentID;

I am embarrassed that I do not know what the "Query by example grid" is. If I drag StudentID, and the three class names and calculated GPA fields to the grid in the lower half of the query builder and try to run the query I get a dialog box that says "unknown".

Thank you so much for your help.

 

[boblarson]

I am grateful for your answer, but there are some terms I don't understand. When you say start a new query and "add all three to the list", do you mean include them in the top portion of the query builder window?.

Yes, that is what I mean.

When you say "join them on studentID", is that accomplished by dragging between the query boxes so that a line connects the three student ID fields?

Yes, that is what I mean.

I am embarrassed that I do not know what the "Query by example grid" is.

This is the QBE Grid:

If I drag StudentID, and the three class names and calculated GPA fields to the grid in the lower half of the query builder and try to run the query I get a dialog box that says "unknown".

Thank you so much for your help.

Not sure why and would probably need to have you upload a copy of your database here for me to take a look at.

 

[funderburgh]

I am very greatful for your help. I am not allowed to publish or distribute information concerning our students, although today I wish I could.


Here is what I have. The result of running the query is "Unknown".

 

[boblarson]

I am very greatful for your help. I am not allowed to publish or distribute information concerning our students, although today I wish I could.


Here is what I have. The result of running the query is "Unknown".

What if you could scramble the data so nobody could be identified, could you post it then? (the screenshot shows that it is what I was talking about but since it doesn't work it would be more helpful to see the whole database).

I have a data scrambler which can scramble the data (in a copy of the database, of course) if that will help.

 

[ajetrumpet]

another was of doing this would be through code and recordsets if you know how to do any of that stuff.

make sure too, that your queries have the same number of fields for unions.

what is the query sql that you tried already?

 

[funderburgh]

Well this problem has morphed a bit. The "unknown" response to the combined query is coming from the source queries, which were working fine yesterday (they return the same response). I tried to re-create one of them in query wizard and received a message that the query could not be run because "perhaps" another user had exclusive control of a table. I rebooted our server (where the tables live) and I have gone to each work station in the school and confirmed that the application is not running. I recreated one of the queries column by column and determined that the problem is on the "First Quarter Grade" field in specific, a field that teachers have been updating recently.

Any ideas would be welcome.