How to apply a conditional Select distinct (or conditional Where onduplicated cell values in a colum

[Jamie]

Hello,

I hope some can help. I have tried to detail this issue clearly, but
not being sure of the correct functions/sql code to use I apologise if
this post is a bit muddled.

I am trying to drop rows from a query (itself a union of smaller
queries) that contains duplicate fields, however I need to apply a
conditional test against another column to determine the correct row
to drop.

Here is an example of a couple of rows from my query dataset that show
the partial duplications:

CAT Number StockCode Supplier Combi
1234 C1234 Supp2 C1234Supp2
1234 1234 Supp1 1234Supp1


I have applied a SELECT Distinct which drops most duplicates, how ever
the remainder are legitimate. Where there is a duplication of 'CAT
Number', I'm trying to drop the row that contains a specific supplier
value. In this query all the remain duplicates belong to two
suppliers. I am trying to drop the row that belongs to Supp1.

All suppliers have a number of legitimate CAT Number - StockCode
combinations so I am unable to filter on the supplier column. This
query is then used as part of another larger query to add stock code
and supplier details to 'cat number' focused table.

I'd really appreciate any suggestions that can manage this in SQL code
if it's possible. If this isn't possible would my next logical step be
to investigate using VBA?

Thanks in advance,
Jamie

 

[vanderghast]

What make you decide to drop Supp1 rather than, say, Supp2?

If you want to drop ANY of them, keeping any single one of them would be
more precise, use a total query:




SELECT CatNumber, LAST(stockCode), LAST(supplier)
FROM somewhere
GROUP BY CatNumber





Vanderghast, Access MVP

 

[Jamie]

This query is for someone else and it's their requirement that
Supplier 2 be matched against the 'cat no' when more than one
supplier. This query will be used as reference for other queries to
determine the correct 'stock code' dependent on 'cat no' match to
stock code, unfortunately some of the suppliers have the same
products, hence the duplicated 'cat no'.

Could the CASE function be used?

 

[vanderghast]

I don't understand. In

CATNumber StockCode Supplier Combi
1234 C1234 Supp2 C1234Supp2
1234 1234 Supp1 1234Supp1


it seems, from THIS data, that both suppliers satisfy your requirements, so
how to do decide to keep Supp1 rather than Supp2? if it is just a matter
of having CatNumber = StockCode, since here 1234 <> C1234, it would be a
simple matter to add that criteria, under the field CatNumber. If not, then
you take your decision because YOU know something that is NOT in the data?


Vanderghast, Access MVP


This query is for someone else and it's their requirement that
Supplier 2 be matched against the 'cat no' when more than one
supplier. This query will be used as reference for other queries to
determine the correct 'stock code' dependent on 'cat no' match to
stock code, unfortunately some of the suppliers have the same
products, hence the duplicated 'cat no'.

Could the CASE function be used?