Concentate and Group by

[Guest]

I would like to take the following Query and Concentate (I believe that is
the term) the last three fields into one value and group by the CanID field
and the order of Concentation should be by the LayerID. Anyway of doing this
in a query?

SELECT BOM.CanID, BOM.LayerID, BOM.LayerAmount, BOM.LayerUnits,
BOM.LayerMaterial
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

 

[OfficeDev18 via AccessMonster.com]

Hi, David,

Seems easy enough,

SELECT BOM.CanID, BOM.LayerID, LayerAmount + LayerUnits + LayerMaterial AS
LayerDetail
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

or am I missing something?

Sam

ps the term is 'concatenate.'

I would like to take the following Query and Concentate (I believe that is
the term) the last three fields into one value and group by the CanID field
and the order of Concentation should be by the LayerID. Anyway of doing this
in a query?

SELECT BOM.CanID, BOM.LayerID, BOM.LayerAmount, BOM.LayerUnits,
BOM.LayerMaterial
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

 

[Guest]

I didn't explain what I was trying to do very well.

The solution below works for a given recordset ( I needed to replace "+"
with "&"). However what I failed to not is that I have several CanID's that
have the same value and I want to that into one desciptive value called
"Structure".

So query would take current query results:


CanID LayerID Amount Units Material
1 1 20 lbs LDPE
1 2 1 mil Adh
1 3 35 Ga Alum

and make field "Structure"

20 lbs LDPE/1 mil Adh/35 Ga Alum/

the spaces and backslashes will be added with proper & "/" and " "'s

thanks




--
David McKnight

Hi, David,

Seems easy enough,

SELECT BOM.CanID, BOM.LayerID, LayerAmount + LayerUnits + LayerMaterial AS
LayerDetail
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

or am I missing something?

Sam

ps the term is 'concatenate.'

I would like to take the following Query and Concentate (I believe that is
the term) the last three fields into one value and group by the CanID field
and the order of Concentation should be by the LayerID. Anyway of doing this
in a query?

SELECT BOM.CanID, BOM.LayerID, BOM.LayerAmount, BOM.LayerUnits,
BOM.LayerMaterial
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

 

[Guest]

After I re-read my post I didn't even undersand what I was trying to say.
I'll try again.

The solution Sam gave below works for a given recordset ( I needed to replace "+"
with "&"). However what I failed to explain in my orginal post was that I have several CanID's that have the same value and I want those concatenated into one desciptive value called "Structure". So that the query result in table example below:

CanID LayerID Amount Units Material
1 1 20 lbs LDPE
1 2 1 mil Adh
1 3 35 Ga Alum

would have finshed value of :

20 lbs LDPE/1 mil Adh/35 Ga Alum/

the spaces and backslashes will be added with proper & "/" and " "'s

--
David McKnight

--
David McKnight

Hi, David,

Seems easy enough,

SELECT BOM.CanID, BOM.LayerID, LayerAmount + LayerUnits + LayerMaterial AS
LayerDetail
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

or am I missing something?

Sam

ps the term is 'concatenate.'

I would like to take the following Query and Concentate (I believe that is
the term) the last three fields into one value and group by the CanID field
and the order of Concentation should be by the LayerID. Anyway of doing this
in a query?

SELECT BOM.CanID, BOM.LayerID, BOM.LayerAmount, BOM.LayerUnits,
BOM.LayerMaterial
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

 

[OfficeDev18 via AccessMonster.com]

Hi, David,

In that case, it won't happen easily in a query. it's much easier in VBA.

Open the original query (that you mentioned in your first post) as a
recordset, and write the appropriate code to go from record to record, adding
to a string variable with each record. When you come to the last record in
that ID, you can update your new Structure field with the contents of your
variable.

Hope this helps,

Sam

I didn't explain what I was trying to do very well.

The solution below works for a given recordset ( I needed to replace "+"
with "&"). However what I failed to not is that I have several CanID's that
have the same value and I want to that into one desciptive value called
"Structure".

So query would take current query results:

CanID LayerID Amount Units Material
1 1 20 lbs LDPE
1 2 1 mil Adh
1 3 35 Ga Alum

and make field "Structure"

20 lbs LDPE/1 mil Adh/35 Ga Alum/

the spaces and backslashes will be added with proper & "/" and " "'s

thanks

Hi, David,

[quoted text clipped - 22 lines]

Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

 

[Michel Walsh]

Hi,


If you have a table with two fields, CanID and Concat (text 255), filled
with all possible values, no dup, under CanID and NULLs under Concat, call
that table Temp, then:


UPDATE Temp INNER JOIN myTable ON temp.canID=myTable.ID
SET Concat = (Concat + "/") & Amout & " " & Units & " " & Material



After having run it, your answer is in table temp.


oh, well, I don't have the final /, is that required? If so, then use

UPDATE Temp INNER JOIN myTable ON temp.canID=myTable.ID
SET Concat = Concat & Amout & " " & Units & " " & Material & "/"




Hoping it may help,
Vanderghast, Access MVP

After I re-read my post I didn't even undersand what I was trying to say.

I'll try again.

The solution Sam gave below works for a given recordset ( I needed to
replace "+"
with "&"). However what I failed to explain in my orginal post was that I
have several CanID's that have the same value and I want those
concatenated into one desciptive value called "Structure". So that the
query result in table example below:

CanID LayerID Amount Units Material
1 1 20 lbs LDPE
1 2 1 mil Adh
1 3 35 Ga Alum

would have finshed value of :

20 lbs LDPE/1 mil Adh/35 Ga Alum/

the spaces and backslashes will be added with proper & "/" and " "'s

--
David McKnight

--
David McKnight

Hi, David,

Seems easy enough,

SELECT BOM.CanID, BOM.LayerID, LayerAmount + LayerUnits + LayerMaterial
AS
LayerDetail
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material
Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;

or am I missing something?

Sam

ps the term is 'concatenate.'

David McKnight wrote:
I would like to take the following Query and Concentate (I believe
that is
the term) the last three fields into one value and group by the CanID
field
and the order of Concentation should be by the LayerID. Anyway of
doing this
in a query?

SELECT BOM.CanID, BOM.LayerID, BOM.LayerAmount, BOM.LayerUnits,
BOM.LayerMaterial
FROM (BOM LEFT JOIN Can ON BOM.CanID = Can.CanID) LEFT JOIN [Material
Class
Properties] ON BOM.LayerMaterial = [Material Class Properties].ID
ORDER BY BOM.CanID, BOM.LayerID;