MS Access CrossTab query - across 3 tables

Posted by Prembo on Stack Overflow See other posts from Stack Overflow or by Prembo
Published on 2010-05-12T15:35:32Z Indexed on 2010/05/12 16:04 UTC
Read the original article Hit count: 257

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about ms-access