Crosstab Group By on Value

[tcjones]

Hi there I have the following predicament,

I want to run a crosstab query on a table that looks like the below:

ID STORE RECEIVED ITEM RESPONSE
1, 123, 2006-03-30, Name, Tony
1, 123, 2006-03-30, Position, Baron
1, 123, 2006-03-30, Name, Simon
1, 123, 2006-03-30, Position, Mgr
1, 321, 2006-03-30, Name, Sarah
1, 321, 2006-03-30, Position, Analyst

The crosstab query as it stands only pulls back the first and last records as it wont allow grouping on the value field, which in this case is the response field. All other fields are row headings.

The report looks like this:
1, 123, 2006-03-30, Tony, Baron
1, 321, 2006-03-30, Sarah, Analyst

Any ideas on how I can bring back both reccords, eg Group on the all fields? It should look like this:

1, 123, 2006-03-30, Tony, Baron
1, 123, 2006-03-30, Simon, Baron
1, 321, 2006-03-30, Sarah, Analyst

Thanks for your time!

Cheers

Tony

 

[tcjones]

Any ideas?

Again, appreciate the support

Tony