Access query to transpose table data

Hello,

I have the following table structure in my database and would like to create a query or table that displays a single record for each country with the three different RatingsType1 across the top as column headings with the respective rating filled in. Something like this:

Country Rating1 Rating2 Rating3

US AAA CCC Baa1

where Rating1, Rating2, Rating3 are one of the three types of ratings from the tblRatingsType1 table. Here's the table structure:

tblCountry

CountryID (PK autonum)

CountryName

tblCountryRating

CountryRatingID (PK autonum)

RatingType1ID (FK to tblRatingType1)

Rating

RatingDate

tblRatingType1

RatingType1ID (PK autonum)

RatingType1

I tried playing with a crosstab query, but it looks like you have to do an operation on the data, whereas all I want to do is display it differently. Thanks for the help!

Answer this question

Access query to transpose table data

NateF

If you want table tblCountryRating to represent the m:m association between countries and ratings, then it needs a FK to tblCountry, which is missing. It doesn’t need the existing PK CountryRating but if you want to keep, that’s OK.

Now assuming there is a column CountryID (FK to tblCountry) in tblCountryRating, the statement should be:

select CountryName, CR1.Rating as Rating1, CR2.Rating as Rating2, CR3.Rating as Rating3< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

from tblCountry as C

      left join ( tblCountryRating as CR1

                  join tblRatingType1 as R1

                       on CR1.RatingType1ID = R1.RatingType1ID and

                          R1.RatingType = 'CurrencyRating_SP' )

            on C.CountryID = CR1.CountryID

      left join ( tblCountryRating as CR2

                  join tblRatingType1 as R2

                       on CR2.RatingType1ID = R2.RatingType1ID and

                          R2.RatingType = 'SovRating_Moodys' )

            on C.CountryID = CR2.CountryID

      left join ( tblCountryRating as CR3

                  join tblRatingType1 as R3

                       on CR3.RatingType1ID = R3.RatingType1ID and

                          R3.RatingType = 'Rating3' )

            on C.CountryID = CR3.CountryID
K. Murli Krishna
Ha! Thanks for trying, I apologize for the misunderstanding. Thanks for pointing me in the right direction.
Susan S
I’m sorry I’ve mislead you. I was doing this in SQL Server. It does fail in Access. Access has a way to do “pivot” queries which may work very well in your case but I’m not an Access expert. I’m trying to find the right forum for your post.
Ovidiu Padurean
The database is in Access 2003.
airwalker2000
OK. This should be it. I created these queries with the wizard.
1. Create a query that joins the three tables so you get names as row- and column headers. (If you want to have IDs, skip this step, and use your existing tblCountryRating table.)
SELECT tblCountry.CountryName, tblCountryRating.Rating, tblRatingType1.RatingType

FROM (tblCountry INNER JOIN tblCountryRating ON tblCountry.CountryID = tblCountryRating.CountryID) INNER JOIN tblRatingType1 ON tblCountryRating.RatingType1ID = tblRatingType1.RatingType1ID;
1. This is the actual query it’s based on the previous one:
TRANSFORM First(CrosstabBase.Rating) AS FirstOfRating

SELECT CrosstabBase.CountryName

FROM CrosstabBase

GROUP BY CrosstabBase.CountryName

PIVOT CrosstabBase.RatingType;
Mikael H&#229;kansson
Yup! I changed it around a little and still get the same thing. Here's what my structure is at the moment that's giving the error:

select CountryName, CR1.Rating as Rating1, CR2.Rating as Rating2, CR3.Rating as Rating3

from tblCountry as C
left join ( tblCountryRating as CR1
join tblRatingType1 as R1
on CR1.RatingType1ID = R1.RatingType1ID and
R1.RatingType = Rating1 )
on C.CountryID = CR1.CountryID

left join ( tblCountryRating as CR2
join tblRatingType1 as R2
on CR2.RatingType1ID = R2.RatingType1ID and
R2.RatingType = Rating2 )
on C.CountryID = CR2.CountryID

left join ( tblCountryRating as CR3
join tblRatingType1 as R3
on CR3.RatingType1ID = R3.RatingType1ID and
R3.RatingType = Rating3)
on C.CountryID = CR3.CountryID

Rating1, Rating2, and Rating3 are not literally in tblRatingType1, they're called something else (i.e. CurrencyRating_SP, SovRating_Moodys, CountryRating_Moodys). I'm not sure if that matters
tackett
What is your database – Access or SQL Server
C-J Berg
Thanks for sticking with me. The exact error is: "Syntax error in JOIN operation". I tried it with the schema as posted prior to your most recent posting, and then I tried it with the following, where I substituted Rating1, Rating2, and Rating3 with the actual records in the tblRatingType1 table: CurrencyRating_SP, SovRating_Moodys, and CountryRating_Moodys. Here it is:

select CountryName, CR1.Rating as CurrencyRating_SP, CR2.Rating as SovRating_Moodys, CR3.Rating as CountryRating_Moodys

from tblCountry as C
left join ( tblCountryRating as CR1
join tblRatingType1 as R1
on CR1.RatingType1ID = R1.RatingType1ID and
R1.RatingType = 'CurrencyRating_SP' )
on C.CountryID = CR1.CountryID

left join ( tblCountryRating as CR2
join tblRatingType1 as R2
on CR2.RatingType1ID = R2.RatingType1ID and
R2.RatingType = 'SovRating_Moodys' )
on C.CountryID = CR2.CountryID

left join ( tblCountryRating as CR3
join tblRatingType1 as R3
on CR3.RatingType1ID = R3.RatingType1ID and
R3.RatingType = 'CountryRating_Moodys')
on C.CountryID = CR3.CountryID

but I still get that syntax error. Perhaps our tables are different
Sam_res03
I had to regroup the joins. See my new statement above.
Venkatroyal
Thanks! I forgot to include the FK to tblCountry. I do indeed have it, but thanks for pointing out that I don't need the PK in tblCountryRating.

I have the following query statement and it's yielding the error: "Syntax error (missing operator) in query expression 'C.CountryID = CR1.CountryID LEFT JOIN tblRatingType1 AS R1 ON CR1.RatingType1ID = R1.RatingType1ID".

SELECT CountryName, CR1.Rating AS CurrencyRating_SP, CR2.Rating AS SovRating_Moodys, CR3.Rating AS CountryRating_Moodys

FROM tblCountry AS C

LEFT JOIN tblCountryRating AS CR1
ON C.CountryID = CR1.CountryID
LEFT JOIN tblRatingType1 AS R1
ON CR1.RatingType1ID = R1.RatingType1ID AND
R1.RatingType = "CurrencyRating_SP"

LEFT JOIN tblCountryRating AS CR2
ON C.CountryID = CR2.CountryID
LEFT JOIN tblRatingType1 AS R2
ON CR2.RatingType1ID = R2.RatingType1ID AND
R2.RatingType = "SovRating_Moodys"

LEFT JOIN tblCountryRating AS CR3
ON C.CountryID = CR3.CountryID
LEFT JOIN tblRatingType1 AS R2
ON CR3.RatingType1ID = R3.RatingType1ID AND
R3.RatingType = "SovRating_Moodys"

I'm not very good at debugging since I'm new to SQL. I'd appreciate any additional assistance. Thank you again!

- John
TheViewMaster
The exact schema matters with SQL. The query compiler must have correct information in order to build a correct query. I recreated the tables based on the information that you posted, and (the last version of) the statement produced a correct result.

BTW what is the exact error that you are seeing and the exact statement you are executing Do you want to post your exact schema as well
RainerK
We have posted within 2 minutes. Did you try my latest version of the statement
BBesser
I tried that, but I'm still getting the same error. Any thoughts Thanks again!

Oh, I also changed the 'Rating3' to 'CountryRating_Moodys', but still I get a "syntax error in JOIN operation" now.
Sandy Place
Please replace the double quotes with apostrophes. Sorry about that.
Access query to transpose table data

Answer this question