Need help with Select Distinct

[LynnEsther]

I have a query where say: Select Distinct Serial_Number, Field1, Field2, Field3, etc from TableName. In some cases there are two records with the same Serial_Number, but in one record all other data is blank, and in the other record, Field3 has data. I need to run a query that shows me all the distinct serial numbers, but only in cases where there is also data in Field3. No matter what I have tried, I keep getting the duplicates in the result, probably because the Select Distinct because it thinks Serial_Number along with Field3 is unique, and not just Serial_Number. Wow, I hope I made sense. I tried searching the forum and didn't see anything that might help with my problem. Thanks in advance.

 

[CraigDolphin]

Have you tried using a criteria in Field3 to remove records without information in that field?
i.e.
Is Not Null

 

[LynnEsther]

Sadly, that won't work. Many of the good records (not duplicate Serial_Number) have null Field3.

 

[honda882000]

Have you tried grouping by Serial_Number and setting a condition for Field3 Is Not Null (as suggested).

 

[CraigDolphin]

Ok, so you want to remove the null field3 records only if the Serial number is duplicated?

Create a calculated field in your query that has something like:

IsDuplicate: DCount("*","TableName","[Serial_Number]=" & [Serial_Number])

Then you could set two criteria rows in your query:

IsDuplicate must be 1
OR
IsDuplicate >1 AND Field3 is not null

 

[just.a.guy]

Not sure what you want do display as a result of the select.
Here I just display the serial number.

I use a JOIN of the table with itself.
The LEFT table is a query of distinct serial numbers.
The RIGHT table is a list of serial number that have
data in FIELD3. I assumed FIELD3 is a text field.
If no, you can figure out a test to detect its
present.

I join the two side on the "serial number".
and then in the "WHERE" clause, I eliminate
all rows (serial numbers that don't have
a match from the RIGHT table.

I you want to display more information,
you may need to use an additiona join
of the table to itself.

Here is the SQL (did not test it, just tried to write what you
defined).

Hope it helps

================================================

select
sno.serial_number
from
( select distinct a.serial_number
from tableName as a
) as sno
left join
( select
b.serial_number
, b.field3
from tableName as b
where len(trim(nz(b.field3,''))) > 0
) as wdata
on sno.serial_number = wdata.serial_number
where nz(wdata.field3,'') <> ''