Need Query to Pull Data Values in Array to become Grouping headers in a report

[lux]

Hi all. Please help

I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.

My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.

Is it possible to write a query from this table that will enable this?

 

[plog]

Is it possible to write a query from this table that will enable this?

Technically yes, but it is also going to require some scripting to extract out that school data into a new table.

Why/how does the data get stored like this? It's not the proper way, the proper way is to have a new table that captures all the school choices that go to an application. That table is the key to displaying the data as you want. It will also be what the script will have to populate.

If you can control how the data is input, that is what I would focus on. You shouldn't have a comma seperated field in your table, you should have a whole other table.

 

[lux]

Hi Plog - thanks for the insight. It's something I've been struggling with for a while. I've seen data stored like this in several different places (from data sources that I cannot control).

In the past I've done what you suggest (not because I thought it was the right thing, I actually thought I was cheating); I've pulled that field into a new sheet on excel and used "text-to-columns". But in that situation the data was static. In my current situation, we're in-taking applications for another month and I need a report now.

I'm not sure I how I would even go about scripting this. I assume I should use VBA?

Any guidance you can offer for where I learn about writing a script for this would be helpful. Thank you!

 

[lux]

And what about SQL? I was hoping I could pick up a SQL trick that would query my data into the table I need. Is this possible?

 

[gblack]

1. Make a listing of all schools (i.e. a seperate table)...
   
2. Using a recordset/VBA, loop though each delimited field with each school name, building a table that itterates a person count each time a school is mentioned in your delimited field.
   
   The general idea would be something like:
   

      If InStr([delimitedfield], strSCHOOL) > 0 Then
                   intPersonCount = intPersonCount + 1
       End If

3. Run a crosstab query on the table you just populated and pivot on school name, so that each school name becomes a header.