Writing a select statement with a Distinct

[JoAnna]

I need this query to only give distinct QSUserLoginName's .
The QSUser table has multiple entries for one person depending on how many
time the users profile has been updated.
I need a query that will show me everyone that has the privilege 31 but I
only want to see each user 1 time.

SELECT DISTINCT QSUser.QSUserLoginName, QSUser.QSUserFullName,
QSUser.QSUserID, QSUser.QSPrivileges, QSUser.Active
FROM QSUser
WHERE (((QSUser.QSPrivileges) Like "* 31,*") AND ((QSUser.Active)=Yes))
ORDER BY QSUser.Active;

Thank you for any help!

 

[Jeanette Cunningham]

Hi JoAnna,
if you are getting too many rows, just remove some of the fields until you
get what you want.

I suggest this-->

SELECT DISTINCT QSUser.QSUserLoginName, QSUser.QSUserFullName
FROM QSUser
WHERE (((QSUser.QSPrivileges) Like "* 31,*") AND ((QSUser.Active)=Yes))
ORDER BY QSUser.QSUserFullName;


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I need this query to only give distinct QSUserLoginName's .
The QSUser table has multiple entries for one person depending on how many
time the users profile has been updated.
I need a query that will show me everyone that has the privilege 31 but I
only want to see each user 1 time.

Only include the fields that you want distinct in the SELECT clause. They can
be in the WHERE clause without including them in the SELECT; to do so in the
query grid just uncheck the Show checkbox under Privileges and Active.

There's also no point in sorting by QSUser.Active if the values are all the
same! You may want to order by LoginName or UserFullName instead.

SELECT DISTINCT QSUser.QSUserLoginName, QSUser.QSUserFullName,
QSUser.QSUserID
FROM QSUser
WHERE (((QSUser.QSPrivileges) Like "* 31,*") AND ((QSUser.Active)=Yes));

 

[Gina Whipp]

JoAnna,

Is QSUserID uniquer to the table or the User? If it is unique to the table
then remove it and you should get what you want. Also, any other field that
would *change* in that table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[James Hall]

I need this query to only give distinct QSUserLoginName's .
The QSUser table has multiple entries for one person depending on how many
time the users profile has been updated.
I need a query that will show me everyone that has the privilege 31 but I
only want to see each user 1 time.

SELECT DISTINCT QSUser.QSUserLoginName, QSUser.QSUserFullName,
QSUser.QSUserID, QSUser.QSPrivileges, QSUser.Active
FROM QSUser
WHERE (((QSUser.QSPrivileges) Like "* 31,*") AND ((QSUser.Active)=Yes))
ORDER BY QSUser.Active;

Thank you for any help!

 

[Paul Shapiro]

You can't include multiple values for QSUserID if you only want a single
output row per QSUserLoginName. You can only output values which will ALWAYS
be the same for a particular QSUserLoginName. You didn't give the table
schema, but something like this is a start:

SELECT DISTINCT QSUser.QSUserLoginName
FROM QSUser
WHERE
QSUser.QSPrivileges) Like "* 31,*"
AND QSUser.Active)=Yes
ORDER BY QSUser.Active;

Guessing from this SQL statement, your table design would appear to be very
far from normalized and you should probably have more tables. QSPrivileges
looks like a multi-valued attribute, which violates normalization. Instead
you might have tables like this:

Users(userID, userFullName, userLoginName)
PrivilegeType(privilegeTypeCode, privilegeTypeName)
Privileges(userID, privilegeTypeCode)
UserProfile(userId, ..., dateStart, dateExpired)