October 29th, 2012, 02:22 PM
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.
October 29th, 2012, 03:00 PM
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
November 1st, 2012, 01:16 PM
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 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...
November 1st, 2012, 02:12 PM
please don't do this
Originally Posted by emailx45
DISTINCT is ~not~ a function
putting the first column that comes after DISTINCT in parentheses has no effect whatsoever on how DISTINCT actually works
November 2nd, 2012, 10:51 AM
SORRY, this is my fault!
Originally Posted by r937
the correct is JUST "DISTINCT" column
November 2nd, 2012, 11:29 AM
the correct is DISTINCT column(s)