DISTINCT not returning correct data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access table with a Yes/No field called Active. If Active is True
for all records,

SELECT Active FROM ATHLETE ORDER BY Active;

Returns Active=True for all of the records as expected. However,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns a single record with Active=False, where it should return one record
with Active=True.

If I set Active to False for one of the records in the table and leave the
others True,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns one record with Active=True and one record with Active=False as
expected.

Why is DISTINCT not working with Active=True for all records?

Thanks,
Rich...
 
I may be misunderstanding something...

I would have guessed that your first SQL statement returns ALL rows, since
there was no DISTINCT qualifier and no WHERE clause to limit/restrict the
records. Even if all records' [Active] field were not true, that first SQL
should return all records.

When you include DISTINCT, you should get as many unique values of [Active]
as you have.

Have you tried the same SQL statements against a smaller set of records?
Say, a test table with only two records, one with [Active] = True and one
with [Active]=False?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

The first SQL returns all records (there are only 4 in the table).

If all records are True, the second SQL only returns False. This is the
problem.

If some records are True and some are False, the second SQL returns True and
False which is correct.

Rich...


Jeff Boyce said:
I may be misunderstanding something...

I would have guessed that your first SQL statement returns ALL rows, since
there was no DISTINCT qualifier and no WHERE clause to limit/restrict the
records. Even if all records' [Active] field were not true, that first SQL
should return all records.

When you include DISTINCT, you should get as many unique values of [Active]
as you have.

Have you tried the same SQL statements against a smaller set of records?
Say, a test table with only two records, one with [Active] = True and one
with [Active]=False?

Regards

Jeff Boyce
Microsoft Office/Access MVP

rpotash said:
I have an Access table with a Yes/No field called Active. If Active is
True
for all records,

SELECT Active FROM ATHLETE ORDER BY Active;

Returns Active=True for all of the records as expected. However,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns a single record with Active=False, where it should return one
record
with Active=True.

If I set Active to False for one of the records in the table and leave the
others True,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns one record with Active=True and one record with Active=False as
expected.

Why is DISTINCT not working with Active=True for all records?

Thanks,
Rich...
Click to expand...
Click to expand...
 
Rick

I haven't see that before. Perhaps one of the other readers has...

Regards

Jeff Boyce
Microsoft Office/Access MVP

rpotash said:
Jeff,

The first SQL returns all records (there are only 4 in the table).

If all records are True, the second SQL only returns False. This is the
problem.

If some records are True and some are False, the second SQL returns True
and
False which is correct.

Rich...


Jeff Boyce said:
I may be misunderstanding something...

I would have guessed that your first SQL statement returns ALL rows,
since
there was no DISTINCT qualifier and no WHERE clause to limit/restrict the
records. Even if all records' [Active] field were not true, that first
SQL
should return all records.

When you include DISTINCT, you should get as many unique values of
[Active]
as you have.

Have you tried the same SQL statements against a smaller set of records?
Say, a test table with only two records, one with [Active] = True and one
with [Active]=False?

Regards

Jeff Boyce
Microsoft Office/Access MVP

rpotash said:
I have an Access table with a Yes/No field called Active. If Active is
True
for all records,

SELECT Active FROM ATHLETE ORDER BY Active;

Returns Active=True for all of the records as expected. However,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns a single record with Active=False, where it should return one
record
with Active=True.

If I set Active to False for one of the records in the table and leave
the
others True,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns one record with Active=True and one record with Active=False as
expected.

Why is DISTINCT not working with Active=True for all records?

Thanks,
Rich...
Click to expand...
Click to expand...
Click to expand...
 
Rick

I just tested the conditions you outlined, first with a field named
[Archive] (in case the fieldname "Active" is a problem), then with it named
[Active]. I got the results I expected every time, with all True records
and half-n-half.

Were this my problem, I'd throw out the old query and write a new one...

Regards

Jeff Boyce
Microsoft Office/Access MVP

rpotash said:
Jeff,

The first SQL returns all records (there are only 4 in the table).

If all records are True, the second SQL only returns False. This is the
problem.

If some records are True and some are False, the second SQL returns True
and
False which is correct.

Rich...


Jeff Boyce said:
I may be misunderstanding something...

I would have guessed that your first SQL statement returns ALL rows,
since
there was no DISTINCT qualifier and no WHERE clause to limit/restrict the
records. Even if all records' [Active] field were not true, that first
SQL
should return all records.

When you include DISTINCT, you should get as many unique values of
[Active]
as you have.

Have you tried the same SQL statements against a smaller set of records?
Say, a test table with only two records, one with [Active] = True and one
with [Active]=False?

Regards

Jeff Boyce
Microsoft Office/Access MVP

rpotash said:
I have an Access table with a Yes/No field called Active. If Active is
True
for all records,

SELECT Active FROM ATHLETE ORDER BY Active;

Returns Active=True for all of the records as expected. However,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns a single record with Active=False, where it should return one
record
with Active=True.

If I set Active to False for one of the records in the table and leave
the
others True,

SELECT DISTINCT Active FROM ATHLETE ORDER BY Active;

Returns one record with Active=True and one record with Active=False as
expected.

Why is DISTINCT not working with Active=True for all records?

Thanks,
Rich...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top