#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Simple Access Question


    I am currently working on a new Access query and having trouble getting the results I need. The table I reference in my query has repeat note entries. This is because files are in process over more than a day and we update the database daily. I need my query to find these repeat notes, however, and return only the earliest one in time. I do have a seperate field called "Date/Time" to reference just not sure how to go about it. Anyone know how I might go about this?

    Have tried an aggregated query with a "min" function applied but I don't think that it works properly on dates and times.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    any chance you could show us the table layout?

    it's pretty hard to write a query out of thin air knowing only the name of one column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up GROUP BY - sql to count how many times a column's value is in a table


    sql to count how many times a column's value is in a table

    SELECT distinct(a.Col1),
    (Select count(Col1) FROM [Table] b where a.Col1= b.Col1) as colcount
    FROM [Table] a
    group by a.Col1 having (Select count(Col1) FROM [Table] b where a.Col1 = b.Col1 ) > 1
    order by colcount desc


    Now, see in ACCESS if is possible this query!

    "GROUP BY" can be your answer, because, you can to group values equals and count it!

    you can "catch" your ID for delete it, update it, etc...
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by emailx45
    SELECT distinct(a.Col1),
    please don't do this

    DISTINCT is ~not~ a function

    putting the first column that comes after DISTINCT in parentheses has no effect whatsoever on how DISTINCT actually works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Talking


    Originally Posted by r937
    please don't do this

    DISTINCT is ~not~ a function

    putting the first column that comes after DISTINCT in parentheses has no effect whatsoever on how DISTINCT actually works
    SORRY, this is my fault!

    the correct is JUST "DISTINCT" column
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    the correct is DISTINCT column(s)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo