Re: is there a better way to do this?

On Feb 20, 8:30 am, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Feb 20, 6:44 am, Ben <benal...@xxxxxxxxx> wrote:





On Feb 19, 5:04 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:

On Feb 19, 2:32 pm, Ben <benal...@xxxxxxxxx> wrote:

10.2.0.2 EE

Is there a more logical sql statement to get the same results than
what I am running here?

create table t (a number, b number);

insert into t values (1, 1);
insert into t values (1, 2);
insert into t values (2, 3);
insert into t values (3, 2);
insert into t values (4, 4);
insert into t values (4, 5);
insert into t values (5, 1);
insert into t values (6, 8);
commit;

select a.a, a.b
from t a, (
select b, count(distinct a)
from t
group by b
having count(distinct a) > 1) b
where a.b = b.b

         A          B C                    D
---------- ---------- -------------------- --------------------
         5          1 a                    a
         1          1 a                    a
         3          2 a                    a
         1          2 a                    a

What I am wanting are the values for A where B is the same.

What, exactly, does that last sentence mean?

David Fitzjarrell- Hide quoted text -

- Show quoted text -

in the original post my table definition doesn't have a column C or D
but the output shows them. I removed those columns from my post to try
to avoid confusion and forgot to remove them from the output of the
query.- Hide quoted text -

- Show quoted text -

You need to read the text which was referenced, and it wasn't your
tacked-on follow-up.  Apparently David Portas could decipher this and
provide a reasonable alternative.

David Fitzjarrell- Hide quoted text -

- Show quoted text -

"What I am wanting are the values for A where B is the same."

yes, I can see where it is confusing.

Looking at the dataset that I created in the insert statements, you'll
see all the values for column B. I want a listing of those records
with duplicate B values. For this dataset the values of B that have
duplicates are 1 and 2. I would want all records that have either a 1
or 2 for B. I needed a sql statement to return that information
without already knowing that 1 and 2 were the values that had
duplicates.
.

Relevant Pages

  • Re: Automating a query
    ... good luck, ... I guess I did not have the right code in for the SQL statement? ... then set index of the fieldin "test" ... "No Duplicates" ...
    (microsoft.public.access.queries)
  • Re: Check table for like values
    ... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Check table for like values
    ... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Dublicate Records in Combo Box
    ... >>Try this SQL statement: ... >>DISTINCTROW returns records that are unique for all ... I can't get those duplicates to go away, ...
    (microsoft.public.access.forms)
  • Re: delete duplicates from query
    ... Is this a one time process? ... Can you post the SQL statement of the query that gives you all the duplicates? ...
    (microsoft.public.access.queries)
Loading