Home > IT Answers > Database > How can I select unique records from Access based on two criteria
Grantp
40 pts.
 How can I select unique records from Access based on two criteria
Microsoft Access, SQL, SQL queries
I need to do the following: 
SQL = "select amount, count(amount) from table1 group by amount having count(*) =1"
This returns all unique records, but I need to add another criteria to that statement ie: I need to select all unique records only if a second value is met as well! This is what I had in mind but it doesn't work, I have tried so many variations! 
SQL = "select amount, count(amount) from table1 group by amount having count(*) = 1 And msgsent = 1"
So I need to get all unique amounts but only if msgsent = 1 and not = 0 Basicly I need to verify against two fields! Can this be done?

Software/Hardware used:
ASKED: February 20, 2009  1:08 AM
UPDATED: February 20, 2009  2:13 PM
RELATED QUESTIONS
Answer Wiki:
SQL = "select amount, count(amount) from table1 where msagent = 1 group by amount having count(*) = 1 And msgsent = 1" will take care of your issue. Regards, Sarfaraz Ahmed <a href="http://findsarfaraz.blogspot.com">MS Excel help</a> Hi, Take this SQL select amount, count(amount) from table1 where msagent = 1 group by amount having count(*) = 1 Regards, Siva Hi Sarfaraz I tried your solution and get the following run time error: You tried to execute aquery that does not include the specified expression 'count(*)=1 And msgsent=1' as part of an aggregate function Regards, Grant Hi Siva, I tried your solution and it works great, thanks alot Regards, Grant
Last Wiki Answer Submitted:  February 20, 2009  10:59 am  by  Sivu   105 pts.
All Answer Wiki Contributors:  Sivu   105 pts. , Findsarfaraz   1,395 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _