MS Access SQL Statement. Help???

[samearle]

Help me write this for MS access, I'm used to Oracle -

SELECT Users.*, group_1, group_2, group_3

from

(select UserInGroup.UserID,

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

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

max(decode(GroupID, 2, 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
)

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't believe that query would work in Oracle (as well as Access),
you've not added the Users table in the main query's FROM clause.

SELECT Users.*, group_1, group_2, group_3

from

(select g.UserID,

max(IIf(GroupID=1, GroupID, null)) as group_1,

max(IIf(GroupID=2, GroupID, null)) as group_2,

max(IIf(GroupID=2, GroupID, null)) as group_3

from Users u INNER JOIN UserInGroup g ON u.UserID = g.UserID

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

group by u.UserID

) AS A

INNER JOIN Users ON A.UserID = Users.UserID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFfluYechKqOuFEgEQK4kQCeK5/OG7uRsrkLBcUEHqqlO4g7LmQAnRsu
5g9/VF6CSf02db0HscLV6N3u
=Wu5n
-----END PGP SIGNATURE-----
sa******@gmail.com wrote:

Help me write this for MS access, I'm used to Oracle -

SELECT Users.*, group_1, group_2, group_3

from

(select UserInGroup.UserID,

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

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

max(decode(GroupID, 2, 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
)

[samearle]

MG -

Thanks so much for the help!!! I'm hopefull we can resolve this today.

I've tried the above SQL statement and get the following:

"YOu tried to execute a querry that does not include the specific
expression 'UserID' as part of an aggregate function."

And I'm clueless as to what that means. Any ideas?

Additionally, Access rewrote the statement a bit when I brought it into
design view and then gave me the following error:

"The Microsoft Jet engine does not recognize 'Users.*' as a valid field
name or expression."

SELECT Users.*, group_1 AS Expr1, group_2 AS Expr2, group_3 AS Expr3
FROM [select g.UserID,
max(IIf(GroupID=1, GroupID, null)) as group_1,
max(IIf(GroupID=2, GroupID, null)) as group_2,
max(IIf(GroupID=2, GroupID, null)) as group_3
from Users AS u INNER JOIN UserInGroup AS g ON u.UserID = g.UserID

where u.GroupID in (1,2,3)
group by u.UserID
]. AS A;

To sum up, just to make sure I'm explaininig myself correctly:

1 record returned from querry per UserID with its
GroupID's in additional columns (either null or with value as needed).
Ergo - trying to match each UserID up with all of its GroupID's and
returned in ONLY one record.

thanks everyone for your help.

S.

[mgf00]

Change the line

FROM (select g.UserID

to

FROM (select u.UserID

and add this line to the bottom of the query (remove the ending
semi-colon ";" first):

INNER JOIN Users ON A.UserID = Users.UserID