Removing rows with duplicate values in a field (distinct?)

[Brandon]

Hi everyone,

This may be a noob question but I am desparate for guidance on this
matter. I want to query some fields from my database but only the ones
with unique 'Chip Designator' values.

For example, my database looks like this:

| STD of Data | Wafer | Chip Designator | Date |Image
| 1.04 | X13 | Chip TB | 12/10/03 | DB4
| 1.99 | S59 | Chip C4 | 12/03/03 | DT9
| 1.56 | S57 | Chip B7 | 12/04/03 | DB8
| 1.34 | X13 | Chip TB | 12/10/03 | DB2
| 1.04 | S59 | Chip C4 | 12/03/03 | DB5


I want my query to retrieve data looking like this:

| STD of Data | Wafer | unique_chip_desi | Date |
| 1.04 | X13 | Chip TB | 12/10/03 |
| 1.99 | S59 | Chip C4 | 12/03/03 |
| 1.56 | S57 | Chip B7 | 12/04/03 |


Please help...

 

[Tom Ellison]

Dear Brandon:

What do you mean "the ones with unique 'Chip Designator' values?"

Do you mean those with a unique value within each [STD of Data] or
within the whole table? X13 is not unique within the whole table, so
from your sample that could not be what you mean. But S59 is unique
for 1.04, yet you have chosen to omit it from the results.

There's not enough information to tell what you want done!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Brandon]

What do you mean "the ones with unique 'Chip Designator' values?"

Do you mean those with a unique value within each [STD of Data] or
within the whole table? X13 is not unique within the whole table, so
from your sample that could not be what you mean. But S59 is unique
for 1.04, yet you have chosen to omit it from the results.

There's not enough information to tell what you want done!

Tom,

First off, thank you for your reply. I will try to clear up the
ambiguity to my question. It really does not matter which values for
wafer, or date is returned from my query. What I am seeking are the
unique values found in field 'Chip Designator' sorted by lowed
'Standard Deviation of Data'.

Let me explain my purpose for this query to help further. Ultimately, I
want to be able to see the best ranked images in my database, based on
lowest standard deviation. From this lot, I would like to find the TOP
20 based on unique values for 'Chip Designator'. I don't care to see a
chip that is ranked twice in the top 20 even if it has a low STD. I
only want Chip values listed once. With this query, I should be able to
determine the Chips that will be most profitable for further research.


Hope this helps...
Thanks in advance,
brandon

 

[John Spencer (MVP)]

You might be able to use something like the following as your base query.

SELECT
[Chip Designator],
Min([STD of Data]) as LowestSTD,
FIRST([Wafer]) AS AnyWafer,
MAX([Date]) AS LatestDate
FROM TABLE1
WHERE [STD of Data] < 2;
GROUP BY [Chip Designator]

You might even be able to do this all in one UNTESTED SQL statement.

SELECT DISTINCT TOP 20
[Chip Designator],
Min([STD of Data]) as LowestSTD,
FIRST([Wafer]) AS AnyWafer,
MAX([Date]) AS LatestDate
FROM TABLE1
WHERE [STD of Data] < 2;
GROUP BY [Chip Designator]
ORDER BY Min([STD of Data])

This might give you more than 20 results IF the last few STD were tied

What do you mean "the ones with unique 'Chip Designator' values?"

Do you mean those with a unique value within each [STD of Data] or
within the whole table? X13 is not unique within the whole table, so
from your sample that could not be what you mean. But S59 is unique
for 1.04, yet you have chosen to omit it from the results.

There's not enough information to tell what you want done!

Tom,

First off, thank you for your reply. I will try to clear up the
ambiguity to my question. It really does not matter which values for
wafer, or date is returned from my query. What I am seeking are the
unique values found in field 'Chip Designator' sorted by lowed
'Standard Deviation of Data'.

Let me explain my purpose for this query to help further. Ultimately, I
want to be able to see the best ranked images in my database, based on
lowest standard deviation. From this lot, I would like to find the TOP
20 based on unique values for 'Chip Designator'. I don't care to see a
chip that is ranked twice in the top 20 even if it has a low STD. I
only want Chip values listed once. With this query, I should be able to
determine the Chips that will be most profitable for further research.

Hope this helps...
Thanks in advance,
brandon