Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help is required for writing a tricky query

Status
Not open for further replies.

VijiKumara

Programmer
Mar 13, 2018
17
US
Hi,

I am struggling in writing a query, where I need to find the sum of values from the same column and concatenate values and put them in one row from another col,umn. I tried with many options, couldn't get anything. I have explained what i want in below image. PLEASE PLEASE PLEASE SOMEONE HELP.
Query_1_oahznu.png


Thanks Much,
Vijay
 
There were at least three FAQs that provided some assistance for this. Another post that might be a bit more efficient is thread183-1721366.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Thank you so much for sharing that post. I could able to make use of that helpful query for my purpose. I am able to execute the query, but the result is not convincing. Because each of the reports I have one or more than one users. But my query is returning only one result for all the results.

I am demonstrating this with below example.

You can see that, the report "Public Folders > Power Systems > AMS > AMS Extract File Based on M&S" has multiple users.
Report_with_multiple_users_vmlxfd.jpg


I have my query as follows,
Code:
SELECT  Prod_Report, 
               (SELECT DISTINCT ',' + User
			     FROM Prod_Users_Reports
			     FOR XML PATH(''))
FROM Prod_Users_Reports
GROUP BY Prod_Report
ORDER BY Prod_Report
The above query result is as follows,
Query_Result_il7rob.jpg


My apologies again, I missed looking into FAQ section. I will be searching there first before I post anything. You guys have been great help in terms of saving my time. Thanks again...Please help with this difficulty.
 
Again [highlight #FCE94F]User[/highlight] is not a good column/field name.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Thanks again! I realized that mistake.it was an effect of multitasking. This time I was able to concatenate, but the problem with this, it's concatenating all the users for each report.
As you can see in my previous post, for this report, "Public Folders > Power Systems > AMS > AMS Extract File Based on M&S", there are four users. I am interested in concatenating only these four users. But this query is concatenating all the users available in User_ID column for each report.
Report_with_multiple_users_gf4ltq.jpg


Query used:
SQL:
SELECT  distinct Prod_Report, 
               (SELECT  DISTINCT ',' + User_ID
			     FROM Prod_Users_Reports
			     FOR XML PATH(''))
FROM Prod_Users_Reports
where Prod_Report = 'Public Folders > Power Systems > AMS > AMS Extract File Based on M&S'

ResulT:
multiple_users_gpikyl.png
 
You can add a WHERE clause inside the subquery to restrict the records accessed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Sorry, if at all I caused confusion here.
I don't want to filter on report name as such. That was just an example.
This is my original query.
SQL:
SELECT  distinct Prod_Report, 
               (SELECT  DISTINCT ',' + User_ID
			     FROM Prod_Users_Reports
				 FOR XML PATH(''))
FROM Prod_Users_Reports

This is what the result I am getting, which is wrong. As you can see in the second column, all the users are concatenated for all the reports. I want users related to that particular report.
Query_Result2_kfigfo.jpg
 
Do you know which is the subquery? Notice there is no WHERE in it. This should be before “FOR XML PATH”

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
I am sorry. I am still unable to figure out how the WHERE clause would help in getting the concatenated users at the individual report level. My apologies. I know its not fair to ask the direct query. This is what I tried so far and getting an
Query_Result3_aciyfd.jpg
error.
 
Oh My gosh! Finally, I could able to figure out how to do this. Below is the query I used. Your hints helped me a lot. I really appreciate your help.

SQL:
SELECT 
       A.Prod_Report, Sum(No_Of_Runs) As Total_No_of_Runs, 
	   STUFF((SELECT ','+B.User_ID
	         FROM Prod_Users_Reports B
			 WHERE A.Prod_Report = B.Prod_Report
			 FOR XML PATH('')), 1, 1, '') [Total_Users]
FROM Prod_Users_Reports A
GROUP BY A.Prod_Report
ORDER BY A.Prod_Report

Thanks again. This was good learning!

Have a great day!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top