MS Access SQL, HELP!!!

[samearle]

Help me write this for MS access, I'm used to Oracle, and I'm really
bad at coding SQL! -

SELECT Users.*, group_1, group_2, group_3


from


(select UserInGroup.UserID,


max(decode(UserInGroup.GroupID, 1, UserInGroup.GroupID, null))
as group_1,


max(decode(UserInGroup.GroupID, 2, UserInGroup.GroupID, null))
as group_2,


max(decode(UserInGroup.GroupID, 2, UserInGroup.GroupID, null))
as group_3


from Users u, UserInGroup g


where u.UserID = g.UserID


and u.GroupID in (1,2,3)


group by u.UserID
)

 

[Jerry Whittle]

Decode is an Oracle thing and won't work in Access. At least I've never tried
it. Also your decode statement might have a flaw as you are doing
"UserInGroup.GroupID, 2," twice.

Try this SQL and see if it gets you in the ballpark.

SELECT u.UserID,
DMax("[GroupID]","Users","GroupID = 1") AS group_1,
DMax("[GroupID]","Users","GroupID = 2") AS group_2,
DMax("[GroupID]","Users","GroupID = 3") AS group_3
FROM Users u, UserInGroup g
WHERE u.UserID = g.UserID
and u.GroupID in (1, 2, 3)
GROUP BY u.UserID;

 

[samearle]

We're getting somewhere - I'm not sure where, but somewhere!

thanks so much for your help!

Access changed the querry a bit when I opened it in design view, and it
is still not returning exactly what I want:

SELECT u.UserID,
DMax("[GroupID]"," UserInGroup","GroupID = 1") AS group_1,
DMax("[GroupID]"," UserInGroup","GroupID = 2") AS group_2,
DMax("[GroupID]"," UserInGroup","GroupID = 3") AS group_3
FROM Users AS u, UserInGroup AS g
WHERE (((u.UserID)=[g].[UserID]) AND ((g.GroupID) In (1,2,3)))
GROUP BY u.UserID;

Essentially what I want to do is return all from Users and GroupID from
UsersInGroup where UserID matches. Then put it in a new column instead
of a new row if there is a secondary group.

Ergo - return all info from a record in Users. Then, in Users in Group,
return all groups that record is in, and add it to that records row as
extra columns.

Currently, I can get all the data:

SELECT Users.*, UserInGroup.GroupID
FROM Users INNER JOIN UserInGroup ON Users.UserID = UserInGroup.UserID;

But that returns a new row for every record in a new group. What I want
to do is return all records with all groups in only one record.

Thanks in advance.

Sam