Creating variables in a query

[JRW]

I have a query with field names "State", "Project", "Role" and "LastName".
I want to take the values of "Role" (e.g. Manager, Assistant, Associate,
etc) and create a field named "Manager", "Associate", "Assistant" and
populate each new field with the persons name. For example, how to go from
this:

State Project LastName Role
WA Boeing Smith Manager
WA Boeing Jones Associate
WA Boeing Johnson Assistant
NY Airbus Drake Manager
NY Airbus Holloway Associate
CA Lockheed Bush Manager
CA Lockheed Cheney Assistant

to this:

State Project Manager Associate Assistant
WA | Boeing | Smith | Jones | Johnson
NY | Airbus | Drake | Holloway |
CA | Lockheed | Bush | | Assistant

Thanks,
Jeff

 

[Duane Hookom]

Consider a crosstab query with State & Project as Row Headings, Role as
Column Heading, and First of LastName as the Value.