I have the following MS Access query:
which returns this data:
I want to remove the rows where there are duplicates in the DATA column, so the result would look like this:
Rows which contain duplicates in the DATA column have been removed. The duplicate data in this case is S1 and a4b7c3d4.
So far, I have managed to cobble together this:
however the issue is that it doesn't return the cnPart and CAT columns.
If I modify the query to contain these columns in the SELECT statement, I don't get anything back at all.
How can I modify this to include these additional columns?
Thanks
Code:
SELECT *
FROM (SELECT [cnPart],[desc] as DATA,"desc" as CAT from PartData
UNION SELECT [cnPart], [qty], "qty" FROM PartData
UNION SELECT [cnPart], [matl],"matl" FROM PartData
UNION SELECT [cnPart], [spec],"spec" FROM PartData) AS A
WHERE (((A.[cnPart]) In ('00001','00002','00003','00004','00005')));
which returns this data:
Code:
|---------------------------|
|cnPart |DATA |CAT |
|---------------------------|
|00001 |10 |qty |
|00001 |a4b7c3d4 |spe |
|00001 |blWidget |desc |
|00001 |s1 |matl |
|00002 |20 |qty |
|00002 |a4b7c3d4 |spec |
|00002 |brWidget |desc |
|00002 |s1 |matl |
|00003 |15 |qty |
|00003 |a4b7c3d4 |spec |
|00003 |gnWidget |desc |
|00003 |s1 |matl |
|00004 |5 |qty |
|00004 |a4b7c3d4 |spec |
|00004 |rdWidget |desc |
|00004 |s1 |matl |
|00005 |30 |qty |
|00005 |a4b7c3d4 |spec |
|00005 |s1 |matl |
|00005 |vtWidget |desc |
|---------------------------|
I want to remove the rows where there are duplicates in the DATA column, so the result would look like this:
Code:
|---------------------------|
|cnPart |DATA |CAT |
|---------------------------|
|00001 |10 |qty |
|00001 |blWidget |desc |
|00002 |20 |qty |
|00002 |brWidget |desc |
|00003 |15 |qty |
|00003 |gnWidget |desc |
|00004 |5 |qty |
|00004 |rdWidget |desc |
|00005 |30 |qty |
|00005 |vtWidget |desc |
|---------------------------|
Rows which contain duplicates in the DATA column have been removed. The duplicate data in this case is S1 and a4b7c3d4.
So far, I have managed to cobble together this:
Code:
SELECT A.data
FROM (SELECT [cnPart],[desc] as DATA,"desc" as CAT from PartData
UNION SELECT [cnPart], [qty], "qty" FROM PartData
UNION SELECT [cnPart], [matl],"matl" FROM PartData
UNION SELECT [cnPart], [spec],"spec" FROM PartData) AS A
WHERE (A.cnPart In ('00001','00002','00003','00004','00005'))
GROUP BY A.data
HAVING (Count(A.data)=1);
however the issue is that it doesn't return the cnPart and CAT columns.
If I modify the query to contain these columns in the SELECT statement, I don't get anything back at all.
How can I modify this to include these additional columns?
Thanks