Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Query question

Author  Topic 

dealwi8me
Starting Member

13 Posts

Posted - 2005-01-22 : 08:58:53
Hello all,
I have a question in Queries
How can i create a query with records that appears more than once in a table based on a field of the table?

An example:

Id | Num |
--------------
02653 | 5 |
--------------
65984 | 5 |
--------------
25589 | 2 |
--------------
43678 | 3 |
--------------
65983 | 2 |
--------------
65982 | 5 |
--------------
65980 | 2 |
--------------

If i execute a query based on Num ... i want to get these results...

Id | Num |
--------------
02653 | 5 |
--------------
65984 | 5 |
--------------
25589 | 2 |
--------------
65983 | 2 |
--------------
65982 | 5 |
--------------
65980 | 2 |
--------------

Any suggestions?
Thank you in advance!




rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-22 : 09:29:01
straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)

you might look up the EXISTS clause in BOL as well.

rockmoose
Go to Top of Page

dealwi8me
Starting Member

13 Posts

Posted - 2005-01-23 : 06:29:27
"straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)"

in this solution i get "Type Mismatch in Expression" from MS Access... why is that?
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-23 : 12:32:44
quote:
Originally posted by rockmoose

straightforward solution:
select id, num from table where id in(select id from table group by id having count(*)>1)

you might look up the EXISTS clause in BOL as well.

rockmoose



This wont work rocko since the ID numbers arent the same, grouping will return always true in this case since the count of each id > 0.

dealwi8me, try this:


SELECT YourTableName.Num, YourTableName.Id
FROM YourTableName
WHERE (((YourTableName.Num) In (SELECT Num FROM YourTableName As ADup GROUP BY Num HAVING Count(*)>1 )))
ORDER BY YourTableName.Num;



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-23 : 15:07:16
Correct Jon, i was rushing things.
Also I don't know about MS-Access syntax, maybe your query will work.

rockmoose
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-23 : 15:53:57
quote:
Originally posted by rockmoose

Correct Jon, i was rushing things.
Also I don't know about MS-Access syntax, maybe your query will work.

rockmoose



Access does not have any knowledge of the EXISTS keyword.
My query should work since it's just plain old sql selects and using the IN predicate.

Jon



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:46:38
ms access knows keyword EXISTS;
rocko's query is ABSOLUTELY correct; he just misread the req.;
vodka is not cool and cavier is for the rich only;
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-24 : 16:49:42
quote:
Originally posted by Stoad

ms access knows keyword EXISTS;
rocko's query is ABSOLUTELY correct; he just misread the req.;
vodka is not cool and cavier is for the rich only;




news to me



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:57:31
about vodka or cavier?
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-24 : 21:42:11
quote:
Originally posted by Stoad

about vodka or cavier?



More along the lines of EXIST.
I dont drink ..nor do I like cavier, give me chilis chicken crispers and I'm happy


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-25 : 06:15:08
I'll have vodka and caviar any day!

Maybe MS Access will like this:
SELECT tab.Num, tab.Id
FROM tab
WHERE tab.Num = ANY(SELECT Num FROM tab GROUP BY Num HAVING Count(*)>1)


rockmoose
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-25 : 06:57:57
oho! rocko at large!
Go to Top of Page
   

- Advertisement -