GROUP numerous lines to one record

[tweetyksc]

I have a table from our system that has numerous lines per student that I'm trying to "roll up" to have a flat file with student id and the relating data in columns per id, instead of an id with multiple lines.

ex: Current table


ID------field name--- String_value
12345--TownofBirth--Bloomfield
12345--Grade--------5
12345--EnterDate----05/16/2009

I'd like to have a resulting query that lists for example:
ID-----TownofBirth----Grade---EnterDate
12345--Bloomfield------- 5-----05/16/2009

I can easily create the fields using the query using iif statement
ex: StateID: IIf([FIELD_NAME] Like "CT_State_Student_ID",[String_VALUE],Null) for the numerous fields; now am trying to get it to appear on one line in a group...just can't get it...

I was able to do it with one of the tables where I didn't need to return the actual data from the table...in that instance it was a list of race codes, multiple lines per student if more than one - it was easy enough to create a field for each race, assign it "1" if populated, 0 if not, and then count in an aggregate query...everything on one line real nice. But in this table, I need actual values...I don't know if I'm rusty and this is something that should be simple...or what

[ConneXionLost]

Hi Tweety,

If you are certain that there is only one entry of "field name" per ID, then you might try using a crosstab query (just use "First" as the aggragate function for the Value).

Cheers,

[tweetyksc]

That didn't work - it is returning a list of one line per student, but not including all the data in the accompanying lines.

Yes there is only one line, per student id, for each unique "field name" data content (ex: Student 1234 would only have one line for "field Name" = grade, but may have numerous lines in the table for other field names. I'm grouping on what I know is the same - ID, Name, but calculating the fieldnames to create a column name and the content. Now just need it in a flat file per student id...

[ConneXionLost]

Perhaps I'm not understanding you, are you saying this doesn't work?