Concatenate duplicate rows from one query using a second query

P

planar.guardian

I have tried using the code listed for combining duplicate results
found in a table but have been unsuccessful. Here's what I have:

SELECT DISTINCT Replace([String],Left([String],3),"") AS Expr1, [DS:
ASU Data].Location
FROM [DS: Files] INNER JOIN [DS: ASU Data] ON [DS: Files].ID=[DS:
ASU Data].DSFileID
WHERE ((([DS: Files].[HM Filename]) Like "*" & [Enter Schematic Label]
& ".DS") AND (([DS: ASU Data].String) Like '[a-z]' & '[a-z]' & "\*"
And ([DS: ASU Data].String) Not Like "*.*"));

Essentially, this SQL is extracting the information from two columns
based on my entered criteria, and formatting the output. An example of
the resulting output is:

Name Subpicture
ACF353 T
UAA030 T
UAA030 WU
UAA030T T
UAA031 T
UAA032S TD

I would like to run a second query on these results to combine the
names that appear in duplicate so that the results will appear like
this:

Name Subpicture
ACF353 T
UAA030 T, WU
UAA030T T
UAA031 T
UAA032S TD

I downloaded the concatenate function that was posted on another group,
but I have been unsuccessful in getting it to work correctly with the
first query. Also, running the tool on the table first and then
performing another query is out of the question seeing how the database
contains over 350000 entries. Any help would be greatly appreciated.
Thanks.
 
D

Duane Hookom

With 350000 records this might be very slow. Try something like:

SELECT [Name], Concatenate("SELECT Subpicture FROM qNoName WHERE [Name] ='"
& [Name] & "'")
FROM qNoName
GROUP BY Concatenate("SELECT Subpicture FROM qNoName WHERE [Name] ='" &
[Name] & "'");
 
P

planar.guardian

Thank you for the reply. I tried your code after modifying it slightly
for the query name, it now looks like this:

SELECT qSubRef2.Name, Concatenate("SELECT Subpicture FROM qSubRef2
WHERE [Name] ='" & [Name] & "'") AS Expr1
FROM qSubRef2
GROUP BY qSubRef2.Name, Concatenate("SELECT Subpicture FROM qSubRef2
WHERE [Name] ='" & [Name] & "'");

I am getting the following error when I try to run it:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Followed by the following error message:

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts
of the expression to variables.

Any suggestions on what I did wrong to cause my SQL string to cause
such an error?
Thank you.

Duane said:
With 350000 records this might be very slow. Try something like:

SELECT [Name], Concatenate("SELECT Subpicture FROM qNoName WHERE [Name] ='"
& [Name] & "'")
FROM qNoName
GROUP BY Concatenate("SELECT Subpicture FROM qNoName WHERE [Name] ='" &
[Name] & "'");


--
Duane Hookom
MS Access MVP

I have tried using the code listed for combining duplicate results
found in a table but have been unsuccessful. Here's what I have:

SELECT DISTINCT Replace([String],Left([String],3),"") AS Expr1, [DS:
ASU Data].Location
FROM [DS: Files] INNER JOIN [DS: ASU Data] ON [DS: Files].ID=[DS:
ASU Data].DSFileID
WHERE ((([DS: Files].[HM Filename]) Like "*" & [Enter Schematic Label]
& ".DS") AND (([DS: ASU Data].String) Like '[a-z]' & '[a-z]' & "\*"
And ([DS: ASU Data].String) Not Like "*.*"));

Essentially, this SQL is extracting the information from two columns
based on my entered criteria, and formatting the output. An example of
the resulting output is:

Name Subpicture
ACF353 T
UAA030 T
UAA030 WU
UAA030T T
UAA031 T
UAA032S TD

I would like to run a second query on these results to combine the
names that appear in duplicate so that the results will appear like
this:

Name Subpicture
ACF353 T
UAA030 T, WU
UAA030T T
UAA031 T
UAA032S TD

I downloaded the concatenate function that was posted on another group,
but I have been unsuccessful in getting it to work correctly with the
first query. Also, running the tool on the table first and then
performing another query is out of the question seeing how the database
contains over 350000 entries. Any help would be greatly appreciated.
Thanks.
Click to expand...
Click to expand...
 
D

Duane Hookom

I don't see anything that jumps out. Can we assume the qSubRef2 contains at
least two fields named Name and Subpicture? What do you see if you open a
new blank query and enter this into the SQL view:

SELECT Subpicture
FROM qSubRef2
WHERE [Name] ="UAA032S";

It may be an issue that you named a field name which is the name of a
property of every object.


--
Duane Hookom
MS Access MVP


Thank you for the reply. I tried your code after modifying it slightly
for the query name, it now looks like this:

SELECT qSubRef2.Name, Concatenate("SELECT Subpicture FROM qSubRef2
WHERE [Name] ='" & [Name] & "'") AS Expr1
FROM qSubRef2
GROUP BY qSubRef2.Name, Concatenate("SELECT Subpicture FROM qSubRef2
WHERE [Name] ='" & [Name] & "'");

I am getting the following error when I try to run it:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Followed by the following error message:

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts
of the expression to variables.

Any suggestions on what I did wrong to cause my SQL string to cause
such an error?
Thank you.

Duane said:
With 350000 records this might be very slow. Try something like:

SELECT [Name], Concatenate("SELECT Subpicture FROM qNoName WHERE [Name]
='"
& [Name] & "'")
FROM qNoName
GROUP BY Concatenate("SELECT Subpicture FROM qNoName WHERE [Name] ='" &
[Name] & "'");


--
Duane Hookom
MS Access MVP

I have tried using the code listed for combining duplicate results
found in a table but have been unsuccessful. Here's what I have:

SELECT DISTINCT Replace([String],Left([String],3),"") AS Expr1, [DS:
ASU Data].Location
FROM [DS: Files] INNER JOIN [DS: ASU Data] ON [DS: Files].ID=[DS:
ASU Data].DSFileID
WHERE ((([DS: Files].[HM Filename]) Like "*" & [Enter Schematic Label]
& ".DS") AND (([DS: ASU Data].String) Like '[a-z]' & '[a-z]' & "\*"
And ([DS: ASU Data].String) Not Like "*.*"));

Essentially, this SQL is extracting the information from two columns
based on my entered criteria, and formatting the output. An example of
the resulting output is:

Name Subpicture
ACF353 T
UAA030 T
UAA030 WU
UAA030T T
UAA031 T
UAA032S TD

I would like to run a second query on these results to combine the
names that appear in duplicate so that the results will appear like
this:

Name Subpicture
ACF353 T
UAA030 T, WU
UAA030T T
UAA031 T
UAA032S TD

I downloaded the concatenate function that was posted on another group,
but I have been unsuccessful in getting it to work correctly with the
first query. Also, running the tool on the table first and then
performing another query is out of the question seeing how the database
contains over 350000 entries. Any help would be greatly appreciated.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenate function from a Query, not a table 5
concatenate entries from a column into a cell by logical AND withanother column 2
Help with a query 1
Creating a text file from a query using a form 1
Making a query to get data from a parent-child table 1
Duplicate entries in a query 3
Concatenate values of one field from multiple records in query 2
Stubborn Array won't Populate from single ADODB.Connection Query 4

Top