Help combining data from two rows in one row in resultset

[PEJO]

As the subject line says.
FOR Example the table I start with would look like this:

USER_ID TEST_ID SCORE

User_1 TEST1 89
User_1 TEST2 69
User_2 TEST1 54
User_2 TEST2 64
User_3 TEST1 73
User_3 TEST2 83

and I would like my results set to look like this.

<USERID <TEST1_Score <TEST2_Score

USER_1 89 69
USER_2 54 64
USER_3 73 83


I've put together Create table and Insert statements hopefuly someone can
help me.

CREATE TABLE Test_Results
(User_ID char(50),
Test_ID char(50),
SCORE INT)


INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_1",
"TEST1", "89")
INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_1",
"TEST1", "69")
INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_2",
"TEST1", "54")
INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_3",
"TEST1", "64")
INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_3",
"TEST1", "73")
INSERT INTO Test_Results (USER_ID, TEST_ID, SCORE) VALUES ("User_3",
"TEST1", "83")



I've done similar things using cursors with sql server in the past but I
am stuck with an Access DB in this case.
Thanks in advance for any help you can offer.

 

[[MVP] S.Clark]

Make a crosstab query, which is one of the reasons why Access is such a
great tool.