How do you modify query to include additional fields in the results? (1 Viewer)

monika_V

New member
Local time
Today, 02:28
Joined
Oct 7, 2021
Messages
2
I have the following MS Access query:
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
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:28
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

You may have to use a combination of more than one query to get the final result you want (with the dups removed).
 

plog

Banishment Pending
Local time
Yesterday, 21:28
Joined
May 11, 2011
Messages
11,613
First, a UNION query should just be a UNION--don't try and jam logic or GROUP BY in there. Just they keywords UNION, SELECT and FROM. Otherwise it just gets too complicated to get right and troubleshoot. Then if you want to do stuff down the line, build another query using that UNION query as the datasource.

So, I would go back, make a query that just does the UNION, then work from there.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Feb 19, 2002
Messages
42,981
I'm confused, which is getting easier day by day. Your title is about adding new columns but I think the problem is actually eliminating duplicates. If it is the latter, you need two more queries. Using the union query you started with create a totals query thst selects ONLY th Desc column and adds a count. Save that query. Then create the final query to join the original union with the totals query on the desc field. Add a where clause that selects only rows where the count field = 1.

This is probably NOT the most efficient method. It is the first that came into my head. Eliminating the duplicates as early in possible in the process would normally be the most efficient but that's a little harder since they are all in different columns and it is the union that brings them together.

I'm not even going to ask what you are trying to do.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Feb 19, 2002
Messages
42,981
Welcome aboard:)
@monika_V -- we really don't care if you cross post. We understand, you need an answer and you want it yesterday. However, in the future, please include a link in both threads to indicate what other forums you posted the same question to. That gives us the option of checking the other thread before we waste our time trying to give you a solution.
 

monika_V

New member
Local time
Today, 02:28
Joined
Oct 7, 2021
Messages
2
I'm confused, which is getting easier day by day. Your title is about adding new columns but I think the problem is actually eliminating duplicates. If it is the latter, you need two more queries. Using the union query you started with create a totals query thst selects ONLY th Desc column and adds a count. Save that query. Then create the final query to join the original union with the totals query on the desc field. Add a where clause that selects only rows where the count field = 1.

This is probably NOT the most efficient method. It is the first that came into my head. Eliminating the duplicates as early in possible in the process would normally be the most efficient but that's a little harder since they are all in different columns and it is the union that brings them together.

I'm not even going to ask what you are trying to do.
What I'm trying to do is extremely simple. All I need, is to remove rows where are duplicates in the DATA column.

So in the results I get, I need to remove the marked rows as these are the ones which contain duplicate data S1 and a4b7c3d4

Code:
|---------------------------|
|cnPart |DATA       |CAT    |
|---------------------------|
|00001  |10         |qty    |
|00001  |a4b7c3d4   |spe    | < remove
|00001  |blWidget   |desc   |
|00001  |s1         |matl   | < remove
|00002  |20         |qty    |
|00002  |a4b7c3d4   |spec   | < remove
|00002  |brWidget   |desc   |
|00002  |s1         |matl   | < remove
|00003  |15         |qty    |
|00003  |a4b7c3d4   |spec   | < remove
|00003  |gnWidget   |desc   |
|00003  |s1         |matl   | < remove
|00004  |5          |qty    |
|00004  |a4b7c3d4   |spec   | < remove
|00004  |rdWidget   |desc   |
|00004  |s1         |matl   | < remove
|00005  |30         |qty    |
|00005  |a4b7c3d4   |spec   | < remove
|00005  |s1         |matl   | < remove
|00005  |vtWidget   |desc   |
|---------------------------|

which should leave 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   |
|---------------------------|

But, in order to remove the duplicates by modifying my original query:

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);

I am left with these results which fair enough, does remove the data but it also removes the cnPart and CAT columns:
Code:
|--------|
|data    |
|--------|
|10      |
|15      |
|20      |
|30      |
|5       |
|blWidget|
|brWidget|
|gnWidget|
|rdWidget|
|vtWidget|
|--------|

so my question is how do you add these columns in to the query?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Feb 19, 2002
Messages
42,981
so my question is how do you add these columns in to the query?
Try reading my remarks again. I explained the solution there.
 

plog

Banishment Pending
Local time
Yesterday, 21:28
Joined
May 11, 2011
Messages
11,613
Agree with Pat (and myself). Your method is incorrect and you are trying to do too much in one query.

You need to UNION your data together (call that qryUnion), then once done build a query using qryUnion to identify the duplicate values (qryDupes). Next you need to build a query based on qryUnion and qryDuplicates to find the records in qryUnion that are not in qryDuplicates. This method will allow you to bring in all the fields you want.
 

June7

AWF VIP
Local time
Yesterday, 18:28
Joined
Mar 9, 2014
Messages
5,423
What's wrong with solution suggested in Stackoverflow answer?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:28
Joined
May 7, 2009
Messages
19,169
from your Original query (first query in post #1), Create another query
and use this query.
replace yourQuery with the name of your original query:

Code:
SELECT yourQuery.cnPart, yourQuery.DATA, yourQuery.CAT
FROM yourQuery
WHERE
((((Select Count("1") From yourQuery As Q Where Q.Data = yourQuery.Data And Val(Q.cnPart) <= Val(yourQuery.cnPart)))=1));
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Feb 19, 2002
Messages
42,981
What's wrong with solution suggested in Stackoverflow answer?
The OP said it didn't solve the problem.

Now we a second answer by Plog which is the same as mine and a third which may or may not work.
 

June7

AWF VIP
Local time
Yesterday, 18:28
Joined
Mar 9, 2014
Messages
5,423
Okay, so any field could result in "duplicates"? What if qty had duplicates? Or is it only the mat1 and spec fields that are of concern? And even these will not always have duplicates? Should have provided more representative raw sample dataset.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Feb 19, 2002
Messages
42,981
The OP doesn't seem to be monitoring this thread.
 

Users who are viewing this thread

Top Bottom