Help is required for writing a tricky query

[VijiKumara]

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.

Thanks Much,
Vijay

 

[dhookom]

VijiKumara,
Did you look at the FAQs in this forum?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VijiKumara]

Nope. I am sorry...may I know why?

 

[dhookom]

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

 

[VijiKumara]

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.

I have my query as follows,

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,

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.

 

[dhookom]

Again [highlight #FCE94F]User[/highlight] is not a good column/field name.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VijiKumara]

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.

Query used:

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:

 

[dhookom]

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

 

[VijiKumara]

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.

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.

 

[dhookom]

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

 

[VijiKumara]

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

error.

 

[VijiKumara]

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.

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!