Move Column data to row data

[TSG]

I hope this makes sense. I have a database that has a student table as
well as an interest table. Each student can have multiple interests.
When I run a quesry to find students interested in a certain area
(interest), the results give me a student record for each interest! I
really need to figure out a way to run a query that will result in ONE
student record with each interest placed in its own cell on the same
row as the student record. I would then like to export this info to
excel.I've listed an example. The first example shows how it currently
returns results and the second example is how I would like for it to
return the results.

First Last Address Interest (these are the headers)
Jay Jones 12 main St. Biology
Jay Jones 12 main St. Math


First Last Address Interest Interest2
Jay Jones 12 main St. Biology Math


Help Please!! Also, please put in simple terms as I am not an Access
expert. I am using Access 2003.

Thanks in advance!

 

[Duane Hookom]

I would create a query that numbers the interests using DCount(). The
specific expression depends on your primary key field in the students table
etc. However, try something like:
SELECT StudentID, Interest, DCount("*","tblStudentInterests","StudentID =" &
StudentID & " AND Interest <=""" & Interest & """") as InterestNum
FROM tblStudentInterests;

You can the create a crosstab query from the above query and the student
table with First, Last, Address as Row Headings, "Interest" & InterestNum as
the Column Heading, and First Of Interest as the Value.

 

[John Spencer]

Or if you have a limited and specific set of interests you can use a
crosstab query to return something like
First Last Address Biology English Math Science
Jay Jones 123 x x

Where you would have an X or other marker under those interests that apply
to the individual.

The crosstab would have first last and address as row headings, column
heading would be Interest, and First of "X" (or whatever you choose to use)
as the Value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..