Group value Query (1 Viewer)

[Babycat]

Hi Everyone

I want to build a query that group data as the attached picture. Can anyone please help me?

 

[arnelgp]

google Allen Browne ConcatRelated function.

 

[Babycat]

google Allen Browne ConcatRelated function.

Thank you very much, let me try these codes

 

[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.)

 

[theDBguy]

Hi Everyone

I want to build a query that group data as the attached picture. Can anyone please help me?

View attachment 103031

How did you come up with that result? I don't understand how the Resistor row got an ID of 2 instead of 4.

 

[Babycat]

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.)

Dear Plog,

Thank for your notice, actually there data it was made in Excel, just for demo purpose.

 

[Babycat]

How did you come up with that result? I don't understand how the Resistor got an ID of 2 instead of 4.

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.

 

[theDBguy]

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.

Hi. Thank you for the clarification. I would probably use something like this then.

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]

(untested)
You can find the SimpleCSV() function here.

Code Snippets

Hope that helps...

 

[Pat Hartman]

Naming all your autonumbers "ID" just causes confusion. That is what prompted question in #5.

 

[Babycat]

Naming all your autonumbers "ID" just causes confusion. That is what prompted question in #5.

well noted for next time posting the question.
Thank you.

 

[Babycat]

Hi. Thank you for the clarification. I would probably use something like this then.

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]

(untested)
You can find the SimpleCSV() function here.

Code Snippets

Hope that helps...

Hi theDBguy

It works,

The actual SQL in my case:

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;

 

[theDBguy]

Hi theDBguy

It works,

View attachment 103098

The actual SQL in my case:

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.