How to create a MS Access query to show a data in columns by criteria?

Question

I have a little problem with MS Access 2013. I cannot figure out how to create a query to show needed data in one table. The data structure can be illustrated with the following:

USERS 

ID  Name
001 John
002 Sarah
003 Miles 

RATING

UserID  Year    Rating
001     2012    79
001     2013    89
002     2012    78
002     2013    75
003     2012    74
003     2013    70

Tables are connected with user ID field. I need query to return the following data:

Name    Rating in 2012  Rating in 2013 
John    79              89
Sarah   78              75
Miles   74              70

Is it possible in MS Access 2013? Criteria in query constructor seems to be applied for all columns simultaneously and I am not able to filter them by year differently.

Answer 1

One way to accomplish this would be to create two initial queries that filter the ratings by year:

SELECT UserId, Year, Rating
FROM Rating
WHERE (((Rating.Year)=2012));

and

SELECT UserId, Year, Rating
FROM Rating
WHERE (((Rating.Year)=2013));

and then the final query:

SELECT Users.Name, Rating2012.Rating, Rating2013.Rating
FROM Users 
LEFT JOIN Rating2012 ON Users.ID = Rating2012.UserId
LEFT JOIN Rating2013 ON Users.ID = Rating2013.UserID;