ConcatRelated Error: "Error 3061: Too few parameters. Expected 3" (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
That is not going to give an output as stated was required in #1, ie
Control1, ASD1,ASD2 , Score

(Presumably the Score value is the sum of all the individual ASDx scores.)
Hi. Probably not, and I may have missed it. But if that's the case, then neither would ConcatRelated() produce such a result. I think I may have read that to mean to display the column Control1, then a concatenated column for ASDs, and then a column for the Score (however it's derived). If so, then either ConcatRelated() or SimpleCSV() should be able to accommodate. Just my 2 cents...
 

Cronk

Registered User.
Local time
Tomorrow, 08:22
Joined
Jul 4, 2013
Messages
2,770
I'd be going for a customized function, perhaps using either of the functions you cite.
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
That is not going to give an output as stated was required in #1, ie
Control1, ASD1,ASD2 , Score

(Presumably the Score value is the sum of all the individual ASDx scores.)
Correct - the score value is an average of all the scores relating to that control - so I do not want to duplicate the same score multiple times for the control, and only want it to show one time (instead of displaying however many ASDx there are for that control).
 

Micron

AWF VIP
Local time
Today, 17:22
Joined
Oct 20, 2018
Messages
3,476
Took some searching but might not help you as it was because of a crosstab query being involved:

Your sql is an actual stored query and not something that you're using in code, right? If not, that is a different matter. Not sure as this can be interpreted either way
The form uses 3 cascading combo boxes to essentially set the WHERE clause of the results of the "Pivoted_Data_Query". The code for this query is as follows:
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Took some searching but might not help you as it was because of a crosstab query being involved:

Your sql is an actual stored query and not something that you're using in code, right? If not, that is a different matter. Not sure as this can be interpreted either way
The form uses 3 cascading combo boxes to essentially set the WHERE clause of the results of the "Pivoted_Data_Query". The code for this query is as follows:

Hmm yeah having trouble interpreting that.
It is an actual query, but it displays nothing if you don't first open up the form and select values from the three combo boxes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Correct - the score value is an average of all the scores relating to that control - so I do not want to duplicate the same score multiple times for the control, and only want it to show one time (instead of displaying however many ASDx there are for that control).
Hi. Just to clarify... You want to display all that info in just one column in your query? ControlNomen + multi-ASDs + AvgScore?
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Hi. Probably not, and I may have missed it. But if that's the case, then neither would ConcatRelated() produce such a result. I think I may have read that to mean to display the column Control1, then a concatenated column for ASDs, and then a column for the Score (however it's derived). If so, then either ConcatRelated() or SimpleCSV() should be able to accommodate. Just my 2 cents...
^^^Yes sorry this is what is meant (I will adjust original post).

Control, "ASD1, ASD2, etc.", Score
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
^^^Yes sorry this is what is meant (I will adjust original post).

Control, "ASD1, ASD2, etc.", Score
I still think that is possible. For example, I might do something like this:

SQL:
SELECT SomeField, etc., [ControNomen] & ", " & SimpleCSV("SELECT for the ASDs") & ", " & Avg(Score) AS CalcColumn, etc.
FROM TableName
WHERE CriteriaHere
GROUP BY blah...
etc.
Just my 2 cents...
 
Last edited:

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
I still think that is possible. For example, I might do something like this:

SQL:
SELECT SomeField, etc., [ControNomen] & ", " & SimpleCSV("SELECT for the ASDs") & ", " & Avg(Score) AS CalcColumn, etc.
FROM TableName
WHERE CriteriaHere
GROUP BY blah...
etc.
Just my 2 cents...

Okay so you would insert that query in replace of what? The initial query?

Also sorry I don't want the actual quotes -> control, ASD concatenated column, score
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Okay so you would insert that query in replace of what? The initial query?

Also sorry I don't want the actual quotes -> control, ASD concatenated column, score
Based on my limited understanding of your table structure, I would the above sample to "replace" your query. Now, if that doesn't work, then you can use it as additional query on top of it. Or, if you simply want to display the combined data on a form in an unbound Textbox, then you would only take the calculated column part of that example. For instance:

=[ControlNomen] & ", " & SimpleCSV(blah...) & ", " & Avg([Score])

I don't understand what you mean by "don't want actual quotes."
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Based on my limited understanding of your table structure, I would the above sample to "replace" your query. Now, if that doesn't work, then you can use it as additional query on top of it. Or, if you simply want to display the combined data on a form in an unbound Textbox, then you would only take the calculated column part of that example. For instance:

=[ControlNomen] & ", " & SimpleCSV(blah...) & ", " & Avg([Score])

I don't understand what you mean by "don't want actual quotes."

Sorry for the difficulty explaining...

I threw together some test data that should hopefully help clarify - trying to attach the file but it is saying that the "uploaded file does not have an allowed extension"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Sorry for the difficulty explaining...

I threw together some test data that should hopefully help clarify - trying to attach the file but it is saying that the "uploaded file does not have an allowed extension"
Hi. Try zipping it up first.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Hi that did the trick! Please see attached for my db.
Hi. I'm on my way out of the office, so I just did this quickly. It runs slow right now, but we could probably work on that later. For now, I'm just trying to show you what could be possible. Cheers!
 

Attachments

  • ConcatTest (2).zip
    69.4 KB · Views: 141

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Hi. I'm on my way out of the office, so I just did this quickly. It runs slow right now, but we could probably work on that later. For now, I'm just trying to show you what could be possible. Cheers!
Hey appreciate the help! Just looking at it now - sorry I forgot to add the modules in that file so thanks for doing that for me.

But other than that, is changing the record source of the form the only change you made?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Hey appreciate the help! Just looking at it now - sorry I forgot to add the modules in that file so thanks for doing that for me.

But other than that, is changing the record source of the form the only change you made?
Hi. Yes, that's it, if I remember correctly. I'll have to check when I get back in the office this morning.

Sent from phone...
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Hi. Yes, that's it, if I remember correctly. I'll have to check when I get back in the office this morning.

Sent from phone...
Awesome thanks again!

When I implemented the module into the original file I get this error when using the form:

Run-time error '3061': Too few parameters. Expected 1 ->I get the error for every row of the results, and it almost seems like its caught in a loop. It occurs on this line of the module:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Any ideas as to what this could be? The results of the form are all displaying expect the ASDs which show #Error, but the rows are grouping/concatenating correctly it seems!
---
ADDING: FYI I am not getting this error in the file you posted - that works cleanly.
 
Last edited:

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Awesome thanks again!

When I implemented the module into the original file I get this error when using the form:

Run-time error '3061': Too few parameters. Expected 1 ->I get the error for every row of the results, and it almost seems like its caught in a loop. It occurs on this line of the module:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Any ideas as to what this could be? The results of the form are all displaying expect the ASDs which show #Error, but the rows are grouping/concatenating correctly it seems!
---
ADDING: FYI I am not getting this error in the file you posted - that works cleanly.
Wait I'm stupid - I had misspelled one of the fields! It looks like its working now. THANK YOU!!!!!

I'll let you know if anything else breaks - hopefully not :) - but once again thanks, I really appreciate this forum and your help.
 

newdbguy

Member
Local time
Today, 17:22
Joined
Mar 9, 2020
Messages
30
Posting the solution here (credit to @theDBguy) in case other people run into this issue. This code was implemented into the recordsource of the form:

Code:
SELECT [Pivoted Data].Process, [Pivoted Data].[Control ID], SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "'") AS ASDs, Round(Avg([Pivoted Data].[Pass]),2)*100 AS AvgPass
FROM [Pivoted Data]
WHERE ((([Pivoted Data].Place)=[Forms]![Pass Percentage Form]![Place_combo]) AND (([Pivoted Data].Unit)=[Forms]![Pass Percentage Form]![U_combo]) AND (([Pivoted Data].[The_Date])=[Forms]![Pass Percentage Form]![Date_combo]))
GROUP BY [Pivoted Data].Process, [Pivoted Data].[Control ID], SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "'")
ORDER BY [Pivoted Data].[Control ID];
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Posting the solution here (credit to @theDBguy) in case other people run into this issue. This code was implemented into the recordsource of the form:

SELECT [Pivoted Data].Process, [Pivoted Data].[Control ID], SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "'") AS ASDs, Round(Avg([Pivoted Data].[Pass]),2)*100 AS AvgPass
FROM [Pivoted Data]
WHERE ((([Pivoted Data].Place)=[Forms]![Pass Percentage Form]![Place_combo]) AND (([Pivoted Data].Unit)=[Forms]![Pass Percentage Form]![U_combo]) AND (([Pivoted Data].[The_Date])=[Forms]![Pass Percentage Form]![Date_combo]))
GROUP BY [Pivoted Data].Process, [Pivoted Data].[Control ID], SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "'")
ORDER BY [Pivoted Data].[Control ID];
Hi. Congratulations! Glad to hear you got it working on your end. Good luck with your project.
 

Users who are viewing this thread

Top Bottom