SQL statement For a recordset

[Tim Mannah]

I have 2 tables tbMembers and tbMembersAccessLvl

I know that a person may have more that one accesslevel in the
tbMemberAccessLvl, I only want to retrieve the highest number (the access
level can only be 0,1,2).
The statement below is retrieveing the lowest one because its the first one
it comes across.

SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
tbMemberAccessLvl.AccessLvl
FROM tbMembers CROSS JOIN tbMemberAccessLvl
WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
'ebony')

How can i get the record that supports the above statement but also gets the
highest tbMemberAccessLvl.AccessLvl.

There might be 3 records that have MemberId = '402195' and MemberPass =
'ebony' but i want the one with the highest value in
tbMemberAccessLvl.AccessLvl. Do i need to use the MAX prefix??

I tried SELECT tbMembers.MemberId, tbMembers.MemberPass,
tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl) but the server said I need
a "Group by" not sure how any ideas.

This select statement is what I will change the recordest in DWMX2004 to be
instead of the normal recordset from one table. Please help

[Singularity.co.uk]

Tim

Would the below work:

SELECT Top 1 tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
tbMemberAccessLvl.AccessLvl
FROM tbMembers CROSS JOIN tbMemberAccessLvl
WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
'ebony')
ORDER BY tbMemberAccessLvl.AccessLvl DESC

Brendan


"Tim Mannah" <timm@diabetesnsw.com.au> wrote in message
news:d0qlc8$gjr$2@forums.macromedia.com...

> I have 2 tables tbMembers and tbMembersAccessLvl
>
> I know that a person may have more that one accesslevel in the
> tbMemberAccessLvl, I only want to retrieve the highest number (the access
> level can only be 0,1,2).
> The statement below is retrieveing the lowest one because its the first

one

> it comes across.
>
> SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
> tbMemberAccessLvl.AccessLvl
> FROM tbMembers CROSS JOIN tbMemberAccessLvl
> WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
> 'ebony')
>
> How can i get the record that supports the above statement but also gets

the

> highest tbMemberAccessLvl.AccessLvl.
>
> There might be 3 records that have MemberId = '402195' and MemberPass =
> 'ebony' but i want the one with the highest value in
> tbMemberAccessLvl.AccessLvl. Do i need to use the MAX prefix??
>
> I tried SELECT tbMembers.MemberId, tbMembers.MemberPass,
> tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl) but the server said I

need

> a "Group by" not sure how any ideas.
>
> This select statement is what I will change the recordest in DWMX2004 to

be

> instead of the normal recordset from one table. Please help
>
>

[Tim Mannah]

This just orders them but doesnt issolated the specific access level the MAX
ended up working when i added a group by clause that groups by all fields
except the one being MAX'ed

"Tim Mannah" <timm@diabetesnsw.com.au> wrote in message
news:d0qlc8$gjr$2@forums.macromedia.com...

> I have 2 tables tbMembers and tbMembersAccessLvl
>
> I know that a person may have more that one accesslevel in the
> tbMemberAccessLvl, I only want to retrieve the highest number (the access
> level can only be 0,1,2).
> The statement below is retrieveing the lowest one because its the first

one

> it comes across.
>
> SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
> tbMemberAccessLvl.AccessLvl
> FROM tbMembers CROSS JOIN tbMemberAccessLvl
> WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
> 'ebony')
>
> How can i get the record that supports the above statement but also gets

the

> highest tbMemberAccessLvl.AccessLvl.
>
> There might be 3 records that have MemberId = '402195' and MemberPass =
> 'ebony' but i want the one with the highest value in
> tbMemberAccessLvl.AccessLvl. Do i need to use the MAX prefix??
>
> I tried SELECT tbMembers.MemberId, tbMembers.MemberPass,
> tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl) but the server said I

need

> a "Group by" not sure how any ideas.
>
> This select statement is what I will change the recordest in DWMX2004 to

be

> instead of the normal recordset from one table. Please help
>
>