Thank you very much, let me try these codesgoogle Allen Browne ConcatRelated function.
How did you come up with that result? I don't understand how the Resistor row got an ID of 2 instead of 4.Hi Everyone
I want to build a query that group data as the attached picture. Can anyone please help me?
View attachment 103031
Dear Plog,In Access both Name and Type are reserved words:
List of reserved words in Access - Microsoft 365 Apps
Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.docs.microsoft.com
You shouldn't name any object or field with any of those on that list--makes coding and querying a little more dificult. I recommend prefixing both those fields with what they are names and types of (ComponentType, ComponentName, etc.)
The ID is not essential there, they can be changed, the desired data might store in a new table...How did you come up with that result? I don't understand how the Resistor got an ID of 2 instead of 4.
Hi. Thank you for the clarification. I would probably use something like this then.The ID is not essential there, they can be changed, the desired data might store in a new table...
The main idea is to combine the items having same type into single row with comma between.
SELECT Min(ID) AS ID, SimpleCSV("SELECT [Name] FROM TableName WHERE [Type]='" & [Type] & "'") AS [Name], [Type]
FROM TableName
GROUP BY SimpleCSV("SELECT [Name] FROM TableName WHERE [Type]='" & [Type] & "'"), [Type]
well noted for next time posting the question.Naming all your autonumbers "ID" just causes confusion. That is what prompted question in #5.
Hi theDBguyHi. Thank you for the clarification. I would probably use something like this then.
(untested)Code:SELECT Min(ID) AS ID, SimpleCSV("SELECT [Name] FROM TableName WHERE [Type]='" & [Type] & "'") AS [Name], [Type] FROM TableName GROUP BY SimpleCSV("SELECT [Name] FROM TableName WHERE [Type]='" & [Type] & "'"), [Type]
You can find the SimpleCSV() function here.
Hope that helps...
SELECT SimpleCSV("SELECT [Designator] FROM TBLBOMDETAIL WHERE [BOMID]= 1 and [IKYPN] = '" & [IKYPN] & "'",", ") AS Designator_N, TBLBOMDETAIL.IKYPN AS PartType, Count(TBLBOMDETAIL.IKYPN) AS BomCount
FROM TBLBOMDETAIL
GROUP BY SimpleCSV("SELECT [Designator] FROM TBLBOMDETAIL WHERE [BOMID]= 1 and [IKYPN] = '" & [IKYPN] & "'",", "), TBLBOMDETAIL.IKYPN;
Hi. Glad to hear you got it sorted out. Good luck with your project.Hi theDBguy
It works,
View attachment 103098
The actual SQL in my case:
Code:SELECT SimpleCSV("SELECT [Designator] FROM TBLBOMDETAIL WHERE [BOMID]= 1 and [IKYPN] = '" & [IKYPN] & "'",", ") AS Designator_N, TBLBOMDETAIL.IKYPN AS PartType, Count(TBLBOMDETAIL.IKYPN) AS BomCount FROM TBLBOMDETAIL GROUP BY SimpleCSV("SELECT [Designator] FROM TBLBOMDETAIL WHERE [BOMID]= 1 and [IKYPN] = '" & [IKYPN] & "'",", "), TBLBOMDETAIL.IKYPN;