Transform Query Help required

[KevinM]

Hi I have a database storing trip details. There's a parent table which stores the basic trip information and a child table that stores the countries to be visited information.
The query I have at the moment is:

TRANSFORM [Test Child].[Arrival Country] AS country
SELECT [Test Tickets].Locator
FROM [Test Tickets] INNER JOIN [Test Child] ON [Test Tickets].Locator = [Test Child].Locator
GROUP BY [Test Tickets].Locator
PIVOT [Test Child].[Arrival Country];

The problem is that all the countries in the child table are shown as headings and what I want is to have 10 columns Country1 to Country10 displayed as column headings with the country name in the data.

I'm sure there's a simple solution but I can't think of it. Any help will be much appreciated.
-Kevin.

[Rabbit]

Are you talking about something that looks like:

TripID Country1 Country2 Country3
Trip19 Germany  France
Trip84 Germany  Russia   China
 

[KevinM]

Are you talking about something that looks like:

TripID Country1 Country2 Country3
Trip19 Germany  France
Trip84 Germany  Russia   China
 

Yes, that's exactly what I'm trying to achieve. Any ideas?

[Rabbit]

Well, you'll need two queries. One to assign a "rank" to each country for a trip. And then the crosstab. If it doesn't matter what order they're in, which I doubt, then you can "rank" by country name. Otherwise you'll probably be ranking on the date of the visit.

[KevinM]

Well, you'll need two queries. One to assign a "rank" to each country for a trip. And then the crosstab. If it doesn't matter what order they're in, which I doubt, then you can "rank" by country name. Otherwise you'll probably be ranking on the date of the visit.

Thanks for the response. I'm not quite sure what you mean by "rank". Do I need to insert a new field in a table or crerate a new query and use the results of that query as the source data for the crosstab query? The order is not important in this instance as I use the results to mailmerge into Word.

[Rabbit]

Well, you basically need to assign each country in a trip a number before you can crosstab; Starting with 1 and ending with the number of countries in that trip. To assign the numbers, you need to be able to rank them on something. What you rank on can be anything as long as it's unique for each trip. If order doesn't matter then you can just use the name of the country.

[KevinM]

I've assigned a number for each country for the trips and then used the Rank field to pivot on and it almost works. The only problem is the results produce a row for each country, rather than just 1 row per trip. Any ideas on how to produce 1 row per trip.

Output now looks like this: (.....=spaces)
Trip Country 1 Country 2 Country 3
001 USA
002 FRANCE
003 USA
003...................MEXICO
003.........................................BELIZE
004 GERMANY

Query is as follows:
TRANSFORM Kev.[Country] AS country
SELECT [Test Tickets].Locator
FROM [Test Tickets] INNER JOIN Kev ON [Test Tickets].Locator = Kev.Locator
GROUP BY [Test Tickets].Locator,Kev.Country
PIVOT Kev.[Rank];

[Rabbit]

Query1

SELECT x.Trip, x.Country, (SELECT Count(Country) As Expr1 FROM TableName WHERE Trip = x.Trip AND Country <= x.Country;) As Rank
FROM TableName As x;
 

Query2

TRANSFORM First(Country) As CountryName
SELECT Trip
FROM TableName
GROUP BY Trip
PIVOT "Country " & Rank;
 

[KevinM]

Thanks very much for your help and patience, that's all working now. It will make a big difference to my reporting.
Thanks again.
Kevin.

[Rabbit]

That second query should be pulling its data from the first query, not the table. But if it's working then it sounds like you figured that out.

[KevinM]

That second query should be pulling its data from the first query, not the table. But if it's working then it sounds like you figured that out.

Almost, but not quite. I can run the first query and it works fine, so I saved it.
When I refer to that query in the crosstab query I get an error saying that the database jet engine does not recognise 'x.Locator' as a valid field name or expression. I got around this by updating a new field in the child table with the Rank value. This took a bit of messing around and isn't a sensible way of working. The only references I could find for this error was to specify the parameters in the first query, but when I tried this the query was expecting some input values.
The queries used are below. The first query is saved as query5 referred to in the 2nd query.

SELECT x.Locator, x.[Arrival Country], (SELECT Count([Arrival Country]) AS Expr1
FROM [Test Child1] WHERE Locator = x.Locator AND [Arrival Country] <= x.[Arrival Country];) AS Rank
FROM [Test Child1] AS x;

TRANSFORM First(Query5.[Arrival Country]) AS CountryName
SELECT Query5.Locator
FROM Query5
GROUP BY Query5.Locator
PIVOT "Country " & Rank;

I think it's something to do with the alias in the first query but it could be something completely different for all I know. Hopefully it's easy and you know the answer.
Thanks.

[Rabbit]

As long as your data table is called Test Child1 and there's a field in there named Locator, the query should work fine.