none
Syntax assist for distinct on trim RRS feed

  • Question

  • Using two fields Assets.Location and User Name: (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2)))

    This returns the room and username for each piece of equipment. I need the list to return a single instance of each unique User Name and Location combination.

    Examples:

    1. A001 David Henry
    2. A001 David Henry
    3. A002 David Henry

    Should return

    1. A001 David Henry
    2. A002 David Henry

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, June 11, 2018 10:01 PM

Answers

  • I see how I can use a query without an alias to get the distinct data. Then create a query against that query rather than the table to change the contextual view of a field as an alias example;

    first create this query

    SELECT DISTINCT Assets.Location, Assets.User
    FROM Assets
    ORDER BY Assets.Location;

    then create this query

    SELECT [qry_ Users_ByLocation_Distinct].Location, (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2))) AS [User Name]
    FROM [qry_ Users_ByLocation_Distinct];

    I get the results needed however, it would be better not to create an extra object in the database. how can I create this as a single object using the first query as a sub query?


    Chris, have you considered using a single query with a derived table, like this?   

    SELECT D.Location, (Trim(Mid(D.User,InStr(D.User,",")+2))) & " " & (Trim(Left(D.User,InStr(D.User," ")-2))) AS [User Name]
    FROM (SELECT DISTINCT Assets.Location, Assets.User FROM Assets) As D
    ORDER BY Assets.Location



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Thursday, June 14, 2018 4:34 PM
    Thursday, June 14, 2018 4:11 PM

All replies

  • Hi,

    With this SQL you can return the records where you asked for:

    SELECT DISTINCT TableName.FieldName
    FROM TableName
    WHERE ((Not (TableName.FieldName) Is Null))
    ORDER BY TableName.FieldName;

    • Proposed as answer by Terry Xu - MSFT Tuesday, June 12, 2018 6:27 AM
    • Unproposed as answer by KCDW Tuesday, June 12, 2018 1:09 PM
    Monday, June 11, 2018 10:20 PM
  • Good Morning Peter and thank you for responding.

    If I only had one field and it were not an alias that will work. However it does not work in this context...

    SELECT DISTINCT [Assets].[Location], (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2))) AS [User Name]
    FROM Assets
    Where ((Not ([Assets].[Location]) is null))
    Order By [Assets].[Location];

    I get an invalid procedure call.

    Any other thoughts?

    Thank you,


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, June 12, 2018 1:19 PM
  • If a field value for the alias has to be retrieved with an expression (in the SELECT component), as in your case, that is difficult. You must therefore use an expression in the WHERE component to. You probably need to write a specific function to do that which you use both in the WHERE and in the SELECT component. Then see how you can in one way or another pass the correct arguments to that function.

    It is sometimes a bit of a puzzle.

    Tuesday, June 12, 2018 1:50 PM
  • I see how I can use a query without an alias to get the distinct data. Then create a query against that query rather than the table to change the contextual view of a field as an alias example;

    first create this query

    SELECT DISTINCT Assets.Location, Assets.User
    FROM Assets
    ORDER BY Assets.Location;
    

    then create this query

    SELECT [qry_ Users_ByLocation_Distinct].Location, (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2))) AS [User Name]
    FROM [qry_ Users_ByLocation_Distinct];
    

    I get the results needed however, it would be better not to create an extra object in the database. how can I create this as a single object using the first query as a sub query?

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, June 13, 2018 1:28 PM
  • Hi KCDW,

    I do not know if I fully understand you. But I think you should look into the context of this example:

    SELECT DISTINCT [Assets].[Location], (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2))) AS [User Name]
    FROM Assets
    Where ((Not ([Assets].[Location]) is null))
    Order By [Assets].[Location];

    As I told before, the SELECT component need in this case the value user. Whether you create a subquery or not, that value should simply be there.

    Whether you create a subquery or not, that value should simply be there. For that you can create a specific function that retrieves that value. I would do that so that the function returns the entire value (for the alias [User name]). To that function you have to pass on the value user that you include in your SELECT component.

    Important: declare the function as a Variant so that you can take into account that the function can return Null (if user does not give value).

    Wednesday, June 13, 2018 10:11 PM
  • Hi Peter,

    I don't think I am the slowest person in the world however I am not following you. Copied your sql expression into a blank query and I still get the same error. I don't get that error if I follow my previous post. I am not using a function. It might be helpful to understand you if you explained the function you are referring to and how it is used and where you declaring the variant. Sounds like you are suggesting using a VBA object in conjunction with an query object. If so I already have a two object solution and am trying to have a one object solution. As far as pulling in all the User values, the first query is doing that.

    Perhaps if you can clarify that for us...

    Thank you.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, June 14, 2018 3:03 PM
  • Hi KCDW,

    Exactly like the function Trim (which is a VBA function) you can write a user-defined function. You can use it in the same way you use the Trim function in your expression. It does not matter whether it is a VBA function or a user-defined function. And every function can be declared as a certain type such as a Long, Boolean, Variant etc. You must declare your user-defined function as a Variant. Pass an argumet for the value user and declare that argument as a Variant. In your function you can test that argument on IsNull and if so, set your function to Null. This prevents errors. In order to achieve your goal, I expect this to be necessary. With this you should be able to solve it anyway. But there are many possibilities.



    Thursday, June 14, 2018 3:30 PM
  • I see how I can use a query without an alias to get the distinct data. Then create a query against that query rather than the table to change the contextual view of a field as an alias example;

    first create this query

    SELECT DISTINCT Assets.Location, Assets.User
    FROM Assets
    ORDER BY Assets.Location;

    then create this query

    SELECT [qry_ Users_ByLocation_Distinct].Location, (Trim(Mid([user],InStr([user],",")+2))) & " " & (Trim(Left([user],InStr([user]," ")-2))) AS [User Name]
    FROM [qry_ Users_ByLocation_Distinct];

    I get the results needed however, it would be better not to create an extra object in the database. how can I create this as a single object using the first query as a sub query?


    Chris, have you considered using a single query with a derived table, like this?   

    SELECT D.Location, (Trim(Mid(D.User,InStr(D.User,",")+2))) & " " & (Trim(Left(D.User,InStr(D.User," ")-2))) AS [User Name]
    FROM (SELECT DISTINCT Assets.Location, Assets.User FROM Assets) As D
    ORDER BY Assets.Location



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Thursday, June 14, 2018 4:34 PM
    Thursday, June 14, 2018 4:11 PM
  • Yes Dirk!!!

    That is precisely what I was trying to get. Works Perfect. Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, June 14, 2018 4:36 PM