Delete duplicate records if a field matches certain criteria

[NPT]

Hi



I have a table called "Sponsor_Sec" with 3 columns.

1) secid
2) cusip
3) flag

Each secid could have either

a) one cusip with either flag "C" or "S"
b) two cusip with both flags "C" and "S"

I need a query that can create a new table called "Sponsor_Sec_Post" by only including records that only includes unique secids (a) and in the event there is a duplicate, include the one with the flag "C" (b)

In other words I need to remove any duplicate records where the flag is "S". Keep all other records.


I have attached a screenshot of the table.


Hope somone can help me with this.

Thanks
NPT

[June7]

What makes record a 'duplicate', repeats of just Secid? cusip will not duplicate? http://office.microsoft.com/en-us/ac...010341696.aspx

[NPT]

Yes. Only repeat of secid makes a record duplicate. Duplicates in cusp are OK. Thanks

[June7]

Did the reference help?

[recyan]

Make this query & keep it.
qrySecID_1

SELECT 
	Sponsor_Sec.SecID, 
	Count(Sponsor_Sec.flag) AS CountOfflag
FROM 
	Sponsor_Sec
GROUP BY 
	Sponsor_Sec.SecID
HAVING 
	(((Count(Sponsor_Sec.flag))=1));

Make this query & keep it.
qrySecID_2

SELECT 
	Sponsor_Sec.SecID, 
	Count(Sponsor_Sec.flag) AS CountOfflag
FROM 
	Sponsor_Sec
GROUP BY 
	Sponsor_Sec.SecID
HAVING 
	(((Count(Sponsor_Sec.flag))=2));

Run this query first.

qrySecID_1_MakeTable

SELECT 
	Sponsor_Sec.SecID, 
	Sponsor_Sec.cusip, 
	Sponsor_Sec.flag 
INTO 
	Sponsor_Sec_Post
FROM 
	Sponsor_Sec 
	INNER JOIN 
	qrySecID_1 
	ON 
	Sponsor_Sec.SecID = qrySecID_1.SecID;

Run this query second.

qrySecID_2_AppendTable

INSERT INTO 
	Sponsor_Sec_Post ( SecID, cusip, flag )
	SELECT 
		Sponsor_Sec.SecID, 
		Sponsor_Sec.cusip, 
		Sponsor_Sec.flag
	FROM 
		Sponsor_Sec 
		INNER JOIN 
		qrySecID_2 
		ON 
		Sponsor_Sec.SecID = qrySecID_2.SecID
WHERE 
	(((Sponsor_Sec.flag)="C"));

Thanks

[NPT]

Thanks recyan. This worked very well. Appreciate your assitance.

NPT

[recyan]

Glad you got things working.

Thanks.