Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    19

    Unanswered: Remove Duplicates

    I have the following SELECT statment that displys the data from a table between 2 dates.

    Code:
    SELECT tblProject.MetDate, tblProject.ProjID
    FROM tblProject
    WHERE tblProject.MetDate Between #8/23/2008# And #8/26/2008#;
    Is it possible to add to the query so when it filters between the dates that I can exclude duplicate data from the “Username” field (if the username is listed twice or three times the query will only show the one instance of the username.)

    Note: I am new to working with databases, so any code samples are welcome

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^

    You could GROUP BY the username and use DFirst or something on the MetDate.

    Since you are new to databases, you should examine these things rather than have code just given to you. Yes, it will take you longer, but you will learn more
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Not quite sure how MetDate works in conjunction with Username...do you only want to see one username per MetDate or only one username at all regardless of the MetDate.

    If it is the first case you could try adding the Distinct keyword to your select query.

    C

  4. #4
    Join Date
    Aug 2008
    Posts
    19
    I would like to see the one user name regardless of how many times that username is listed between the dates, I just need to show that the username is present between the dates.

    Basically I am trying to retrieve data from a table that shows a list of usernames that are associated with specific dates and the same usernames can appear multiple times in the table for different dates. I can query for all usernames between a set of dates but I wish only to show the one occurrence of the username.

    I have looked on the net for ways of achieving this by means of a query but as I am displaying the information on to a VB.NET listbox control, I did not find any helpful information. If this can not be achieved by a query, I will look at using arrays in VB.net to perform the process.

    Thanks

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT DISTINCT username
    FROM   my_table
    WHERE  something BETWEEN [date 1] AND [date 2]
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2008
    Posts
    19
    Hi Georgev

    The query i use to get all usernames is
    Code:
    SELECT tblProject.MetDate, tblProject.Username FROM(tblProject)WHERE tblProject.MetDate Between #22/08/2008# AND #28/08/2008#;
    When I try to incorporate your query in to the one I use, I get a syntax error or an issue with the dates. If I use your query without specifying the dates it works so the problem seems to be with the date syntax.

    Code:
    SELECT DISTINCT Username FROM(tblProject)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Max(date) As [date]
         , username
    FROM   my_table
    GROUP
        BY username
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2008
    Posts
    19
    I have tried the following:
    Code:
    SELECT Max(date)As #20/08/2008#, Username FROM(tblProject)GROUP() BY(Username)
    but i am getting the error
    (The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.)

    With this statment are we only specifing the end date with the "Max(date)" code.

    I appreciate your help

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Celtic101
    When I try to incorporate your query in to the one I use, I get a syntax error or an issue with the dates. If I use your query without specifying the dates it works so the problem seems to be with the date syntax.
    you should continue to use the dates in the WHERE clause, there is nothing wrong with that

    just don't have the date in the SELECT clause (unless it's inside an aggregate function)

    Code:
    SELECT Username 
      FROM tblProject
     WHERE MetDate Between #22/08/2008# AND #28/08/2008#
    GROUP
        BY Username
    Quote Originally Posted by Celtic101
    Code:
    SELECT Max(date)As #20/08/2008# ...
    you cannot assign a date as a column alias, because it isn't a name

    Code:
    SELECT Username 
         , MAX(MetDate) AS max_Metdate
      FROM tblProject
     WHERE MetDate Between #22/08/2008# AND #28/08/2008#
    GROUP
        BY Username
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2008
    Posts
    19
    Cheers, got it now.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •