Can I use a crosstab to transpose 2 fields?

[Guest]

I am trying to rearrange a set of data I have using a query. I think I need
some type of cross tab query, but I can't work out quite how:

My data looks like this:

Name Place

John London
John Paris
John New York
David London
David Paris
David Tokyo
Alan Paris
Alan Rome

But I want to change it into this format:

Name Places

John London, Paris, New York
David London, Paris, Tokyo
Alan Paris, Rome

or even:

Name Places 1 Place 2 Place 3

John London Paris New York
David London Paris Tokyo
Alan Paris Rome

whichever is easier. The sequence of the places is not important. My problem
when creating a crosstab is it requires 3 fields, and I don't know what the
third
field should be. I think maybe some kind of places counter? but thats as far
as I have got.

Any ideas would be much appreciated.

Thanks

Rob

 

[Duane Hookom]

Check out http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
for a generic concatenate function.

 

[Guest]

Genius!!! Many many Thanks! I can use that for so many things!

 

[Guest]

Genius!!! Many many Thanks! I can use that for so many things!

 

[Guest]

The concatenate function works great when I have the criteria for the query
hard coded, but I can’t seem to make it work with parameters. The following
code I have written hardcoding the quarter as 3 works fine:

ListLocations: Concatenate("SELECT [Location Code] FROM [TABLE_DETAIL] WHERE
[UNIQUE ID] =" & [UNIQUE ID] & " AND [TABLE_DETAIL]![Quarter] in ('3')")

But this code, which I was hoping would ask the user for the quarter:

ListLocations: Concatenate("SELECT [Location Code] FROM [TABLE_DETAIL] WHERE
[UNIQUE ID] =" & [UNIQUE ID] & " AND [TABLE_DETAIL]![Quarter]
=[whichQuarter]")


Produces the following error:

Run-time error ‘-2147217904 (80040e10)’:
No value given for one or more required parameters

To get round this I have tried getting the user parameter from a form, but
this produces a similar error. I also tried replacing the TABLE_DETAIL with a
query based on TABLE_DETAIL that had a criteria in it to select only the
relevant quarters. This doesn’t work either.

Where am I going wrong? Is there any way to make this work?

Many many thanks for your help.

Rob

 

[Duane Hookom]

I recommend against using parameter queries. Try use a form with a text box
to enter the value. Then your syntax would be something like:
ListLocations: Concatenate(
"SELECT [Location Code]
FROM [TABLE_DETAIL]
WHERE [UNIQUE ID] =" & [UNIQUE ID] &
" AND [TABLE_DETAIL]![Quarter] =" & Forms!frmEntry!txtQuarter)


--
Duane Hookom
MS Access MVP
--

The concatenate function works great when I have the criteria for the
query
hard coded, but I can't seem to make it work with parameters. The
following
code I have written hardcoding the quarter as 3 works fine:

ListLocations: Concatenate("SELECT [Location Code] FROM [TABLE_DETAIL]
WHERE
[UNIQUE ID] =" & [UNIQUE ID] & " AND [TABLE_DETAIL]![Quarter] in ('3')")

But this code, which I was hoping would ask the user for the quarter:

ListLocations: Concatenate("SELECT [Location Code] FROM [TABLE_DETAIL]
WHERE
[UNIQUE ID] =" & [UNIQUE ID] & " AND [TABLE_DETAIL]![Quarter]
=[whichQuarter]")


Produces the following error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters

To get round this I have tried getting the user parameter from a form, but
this produces a similar error. I also tried replacing the TABLE_DETAIL
with a
query based on TABLE_DETAIL that had a criteria in it to select only the
relevant quarters. This doesn't work either.

Where am I going wrong? Is there any way to make this work?

Many many thanks for your help.

Rob