MS Access CrossTab query - across 3 tables

Question

Hi,

I have the following 3 tables:

1) Sweetness Table

FruitIndex       CountryIndex       Sweetness
1                1                  10
1                2                  20
1                3                  400
2                1                  50
2                2                  123
2                3                  1
3                1                  49
3                2                  40
3                3                  2

2) Fruit Name Table

FruitIndex      FruitName
1               Apple
2               Orange
3               Peaches

3) Country Name Table

CountryIndex    CountryName
1               UnitedStates
2               Canada
3               Mexico

I'm trying to perform a CrossTab SQL query to end up with:

Fruit\Country       UnitedStates Canada      Mexico
Apple               10           20          400
Orange              50           123         1
Peaches             49           40          2

The challenging part is to label the rows/columns with the relevant names from the Name tables.

I can use MS Access to design 2 queries,

1. create the joins the fruit/country names table with the Sweetness table
2. perform crosstab query

However I'm having trouble doing this in a single query. I've attempted nesting the 1st query's SQL into the 2nd, but it doesn't seem to work.

Unfortunately, my solution needs to be be wholly SQL, as it is an embedded SQL query (cannot rely on query designer in MS Access, etc.).

Any help greatly appreciated.

Prembo.

Answer 1

How about:

TRANSFORM First(Sweetness.Sweetness) AS FirstOfSweetness
SELECT Fruit.FruitName
FROM (Sweetness 
INNER JOIN Fruit 
ON Sweetness.FruitIndex = Fruit.FruitIndex) 
INNER JOIN Country 
ON Sweetness.CountryIndex = Country.CountryIndex
GROUP BY Fruit.FruitName
PIVOT Country.CountryName;

Answer 2

I hate to rely on an outside post and present it as my answer, but this is a pretty steep topic and I can't do it justice. So I suggest you look at this article.