Taking Multiple Records and making one

[jason6084]

In an Access query, I am trying to do the following:

I have this table that has 3 coulmns

Name Grade Pass/Fail
-------------------------------------------------------------------
AL A P
AL B P
AL F F
Bob A P
Jane B P
Jane D F
Mary C P
Mary A P
Mary D F

I would like to get everything on one line.
Name Grade Pass/Fail

Name Grade1 Pass/Fail1 Grade2 Pass/Fail2 Grade3 Pass/Fail2
------------------------------------------------------------
AL A P B P F F
Bob F F
Jane B P D F
Mary C P A P D F

Any help is greatly appreciated!

[brju]

Have you tried to use a crosstab query, in other words a TRANSFORM SQL statement?

[jason6084]

Well I think you may be on to something. I dont have a lot of experiance doing Crosstab queries, and Im not familiar with the TRANSFORM SQL statement. Id love to hear more.

[Allan Murphy]

A crosstab will not work for this solution. A crosstab gives you an analysis of the results. e.g. how many students obtained a grade pass of A, B, C, D, E and F etc

As I stated earlier in a message to you that you assume the user enters the results in a consistent manner Grade 1 results followed by Grade 2 results etc. for each student.

If you know the number of Grades you have a table as follows

student name
grade 1 result
grade 1 P/f
grade 2 result
grade 2 P/f
grade 3 result
grade 3 P/f
etc
etc.

This is a simpler solution.

[brju]

I still think you could use a crosstab query (subject to your response), but need to clarify what exactly your 3 fields represent:
Field 1: Name - student name?
Field 2: What is grade - the marks obtained or a reference to a specific subject? If not, how do you determine what the grade (mark) is for?
Field 3: Pass/Fail?

[jason6084]

The problem im having with doing a crosstab query is that Access only lets you display one coulmn.

[brju]

If you could answer the questions in my last post, I will then be able to advise you further.

[Allan Murphy]

As I said earlier in my previous post, Access does not allow multiple columns in crosstabs, multiple rows YES.

I have attached a sample database that may help.
table tbl_results has student,grade_test and result e.g. BOB, grade 1,A. grade 1 is the name of the first test etc.

I then used a crosstab qry_results_Crosstab to give the layout Name, Grade 1, Grade 2, Grade 3
AL A B F

To get your desired result I then used a query qry_final_result to produce the Pass Fail result using the module mod_check_pass. When run, this query makes a table tbl_grade_results that has the results in your desired format.