Convert normal db results TO un-normalized flat file

[PaulWilson]

Folks,
I have a database that maps students to their schools. I set up a junction table relating students to schools so there is a many-to-many relationship between students and schools. One student can attend many schools and any one school can serve many students. The tables are nicely normalized and when I run a query to get a listing of students and their schools it looks something like my attached figure1. Unfortunately, my co-workers can't use the data in this format. They would like it in an un-normalized flat-file format as in figure2 with "School1, School2, School3, etc" forming the headers.

Currently, it's a painful manual process of exporting to Excel and cutting and pasting. How might I go about converting to the flat-file format automatically? Is there some way to organize a query to do it automatically?


Thanks in advance,
PaulWilson

 

[liddlem]

Hi Paul
There are 2 ways that you could accomplish this using a cross-tab.
But in both cases, the data will not be presented EXACTLY as you propose.

By far, the quickest/easiest is to export your data to Excel, then create a Pivot Table (known as a cross-tab query in Access.) You can find the Pivot table on the INSERT tab in Excell2007 and 2010.

In the Pivot1.png image, I have simply assigned a value of 1 to each record. So now, the pivot table simply reflects a 1 or 0 (or blank)

In the Pivot2.png image, I have assigned a numeric value that assumes the list is exported in order of
- Student
- School attended, in the order that they attended.

Does this assist you?

 

[stopher]

I've attached a solution that does exactly what you want using two queries. In fact you could combine the queries into one but I've done it as two so you can see how it works.

Take a look at qryRankSchools to see how a school sequence field is derived for each student.

Take a look at qryResult to see the result (using a crosstab on the first query).

hth
Chris

 

[liddlem]

thanks Stopher - now i learn too.

 

[PaulWilson]

Thanks all.
In fact, I've been using a pivot table in Excel for a while.
I use pretty much the same, indexing function and at one time I'd filter on a list of unique school names and use a variety of formulas. Even if simplified, it does require an export step.

Helen Feddema also has a VBA approach in her expert-to-expert Access book (which I've been looking for).

I've been looking for an SQL way to do it so thanks much Stopher.