Remove Duplicates SQL

[cjh7111]

Hey all,

I have a query that is returning a number of duplicates because of the way that it is set up. The duplicates are the same in every field except one (BS%). For the duplicates, BS % is blank in one and has a value in the other. I want to query the original query in order to remove the duplicates as well as add another criteria. If the record is a duplicate not counting (BS %), and the BS% is null then I want it deleted. Any ideas on how this would look? Thanks

 

[plog]

... because of the way that it is set up.

Why not fix the real issue and not try and apply a band-aid on the backend?

With that in mind, let's start from scratch. Can you post sample data from your table(s), along with table and field names? And then, based on that sample data show what you expect your query to return.

 

[James Deckert]

Also it's possible that the tables are ok, but your query needs fixed. Can you post the SQL or a pic of the query (in addition to what plog asked for)?

 

[cjh7111]

Alright, so here is an abridged version of the SQL of the query. [Paste Table] is populated with data that users input (done via vba in excel) and then it pulls the BS%@W and BS%@C from the back tables. I need records that it doesnt find in the tables to stay blank so they know that it didnt find that record however the way it's set up to do that it also returns empty results for records that have been filtered out. So, I was thinking to remove the duplicates if there is no number in the BS%@W. Is there any way to do that within this query?

SELECT [Paste Table].[Material Key], [Paste Table].[Mat Grp Name], [Paste Table].[D/S Flag], [Table1].[BS%@W], [Table1].[BS%@C]
FROM [Paste Table] LEFT JOIN Table1 ON [Paste Table].[Material Key] = Table1.[Material Key]
WHERE [Paste Table].[D/S Flag]="N" AND [Paste Table].[Mat Grp Name]="BR"

UNION ALL SELECT [Paste Table].[Material Key], [Paste Table].[Mat Grp Name], [Paste Table].[D/S Flag], Table1.[BS%@W - DS], [Table1].[BS%@C]
FROM [Paste Table] LEFT JOIN Table1 ON [Paste Table].[Material Key] = Table1.[Material Key]
WHERE [Paste Table].[D/S Flag]="Y" AND [Paste Table].[Mat Grp Name]="BR"

UNION ALL SELECT [Paste Table].[Material Key], [Paste Table].[Mat Grp Name], [Paste Table].[D/S Flag], Table2.[BS%@W], [Table2].[BS%@C]
FROM [Paste Table] LEFT JOIN Table2 ON [Paste Table].[Material Key] = Table2.[Material Nbr]
WHERE [Paste Table].[Mat Grp Name]<>"BR" AND " GR";

 

[arnelgp]

you dont need union for that.

 

[cjh7111]

How would I go about it instead?