Query for Export

[Jeffrey Marks]

I currently have a database that has student information in it. One
table is demographic information. Another table is for grades by
semester and a third represents the results of the state graduation
test, which may be taken multiple times.

The user wants to create a query where the output has 1 row per
student that includes grades for a school year and the graduation test
results. They plan to export the data from the query into a
statistical program which requires 1 record per student.

I had a couple of questions regarding this.

1) I know I can put the grades in by using an IIF statement (e..g. IF
course-code between 111 and 222, then put the English grade in this
field). Is there a more efficient way to do this other than the IIF
statement?

2) Since the graduation test can be taken multiple times and be shown
as multiple records in the table, is there a way to put the results
into a generic field (e.g. gradtest1) and then the next result in
gradtest2, or should I stick to my IIF statements of if the gradtest2
is null and gradtest1 is filled then put the data in gradtest2?

I hope this makes sense. Thanks!

Jeff

 

[John Spencer]

Depending on your table structure(s), you may be able to do what you want
using crosstab queries and additional table(s).

For instance, do you have a table of Course Codes and the subject area?
111 :: English
112 :: English
222 :: English

The SQL for the crosstab query would look like the following
TRANSFORM First(G.Grade)
SELECT S.StudentID
FROM (Students as S INNER JOIN GRADES As G
ON S.StudentID = G.StudentID)
INNER JOIN CourseCodes as C
G.CourseCode = C.CourseCode
PIVOT C.CourseSubject IN ("Math","English","French","Reading","History")

Next you would need to handle the State graduation test data. I would use a
ranking query to number the test rows for each student and then build a
crosstab based on that.

Final step, join the two crosstab queries (probably using an outer join to
handle instances where a student has not taken any state tests) to produce the
final record.

Again without greater detail on the structure of the tables, it is difficult
to give you a more detailed explanation.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County