query problem with counts.

[kidrobot]

I'm not sure if this is a query or report problem, so it is in both areas. let me know and i'll delete one of the postS!

I have this query:

SELECT Owner, lgtn, Count( lgtn) AS Cnt, States
FROM [bare chassis BRIO]
GROUP BY Owner, lgtn, States
HAVING ((( CountOwner) Is Not Null))
ORDER BY Owner, lgtn, States;



Which will output this report:

Owner__length__Cnt__States
flex__20________4__ Virginia
TRAC__40_______6__ Maryland
flex__40________4__ Virginia
flex__20________7__ Maryland
flex__20________8__ New Jersey
pool__48_______4__ Virginia


How can I make it so the report makes the "States" as the column headers? like this:


Owner__length__VA__MD__NJ
flex___20______4____3___2
TRAC___40______6__5___3
flex___40______4___2___ 2
flex___20_______7__ 3__4
flex___20______8___2 ___4
pool___48______4__ 4___4

the data make look wrong but you get the idea?

 

[twoplustwo]

Look up cross-tab queries

 

[Fifty2One]

Make a column for each states abreviation then go like this
VA: IIf([States]="Virgina",[Cnt],"")
that will populate the column of VA with the value of Cnt if it exists