Select Record Problem

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi folks,

I tried to build a query to select record from the
following table:

ID Value
99 aa
99 aa
98 bb
98 ba
97 ab
97 ab
97 aa
95 a
94 dd
94 dd
93 cc
93 cc
93 cc

I am looking for the following output:

98
97

The request for the query is if the same ID in ID field
but different value in value field. Then, I need to
select them.

Could anyone show me how to do it?

Thanks in advance.

Tim.
 
SELECT VT.ID
FROM
(
SELECT DISTINCT Table2.ID, Table2.Value
FROM Table2
) AS VT
GROUP BY ID
HAVING Count(ID) > 1
 
Not sure but AFAIK, everything I used is in JET 3.5 so it should work.

I tested in A2K2, though.
 
Van,

I tried it in Access 97 but I got an error message "Syntax
error in From clause"

Could you help me to fix?

Tim.
 
I think in 97 you had to use brackets
instead of parentheses, and at end of
closing bracket, add a period.

SELECT VT.ID
FROM
[SELECT DISTINCT Table2.ID, Table2.Value
FROM Table2]. AS VT
GROUP BY ID
HAVING Count(ID) > 1

I could be wrong though....
 
I don't have A97 installed anymore. It works fine in A2K2.

Try Gary's suggestion.

If that doesn't work, break it into 2 Queries, one for the inner SELECT and
one for the outer SELECT.
 
Back
Top