Field Concatenation Question

  • Thread starter Thread starter NeonSky via AccessMonster.com
  • Start date Start date
N

NeonSky via AccessMonster.com

Good Afternoon,

Would anyone know how to do the following? First please view my sample
dataset....

Name Address Phone TransNumber
Joe 64 Lane 123 45
Joe 64 Lane 123 92
Sam 24House 456 87
Sam 24House 456 21

I would like an output that performs a "group by" on the like data, and
concatenates/formats the differing data from the same field....So the output
would look like this....

Name Address Phone TransNumber
Joe 64 Lane 123 45, 92
Sam 24House 456 87, 21

Thanks for your consideration!
 
NeonSky said:
Good Afternoon,

Would anyone know how to do the following? First please view my sample
dataset....

Name Address Phone TransNumber
Joe 64 Lane 123 45
Joe 64 Lane 123 92
Sam 24House 456 87
Sam 24House 456 21

I would like an output that performs a "group by" on the like data, and
concatenates/formats the differing data from the same field....So the output
would look like this....

Name Address Phone TransNumber
Joe 64 Lane 123 45, 92
Sam 24House 456 87, 21


That kind of thing can be done by creating a function to do
the concatenating. There's a nice one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
 
You can do it with a query too, if you first have the right temporary table
(to collect the result).


Have a temp table, Temp, with all the fields defining the groups, plus one
extra field, Concat, a string nullable field.

Fill Temp with values for the groups, leave Concat filled with NULLs. Can be
done in one step (creating the table, and filling it) with something like:


SELECT name, address, phone, iif(false, " " , null ) AS concat
FROM yourTable
GROUP BY name, address, phone
INTO temp



You may add index on each of the fields which define the groups, if you have
a lot of records (if the next query is too slow).

Next, the following query will produce the concatenation itself:


--------------------------------------
UPDATE temp INNER JOIN yourTable
ON (temp.name=yourTable.name
AND temp.address=yourTable.address
AND temp.phone = yourTable.phone )

SET temp.concat = (temp.concat + ", ") & CStr(yourTable.TransNumber)
--------------------------------------


Note that the ON clause just match each fields which define the 'groups'.



That technique works only with Jet, not with MS SQL Server.


The problem is that you have to re-initialize the table Temp each time you
want to concatenate a new time.


Hoping it may help,
Vanderghast, Access MVP



NeonSky via AccessMonster.com said:
Marshall said:
Good Afternoon,
[quoted text clipped - 14 lines]
Joe 64 Lane 123 45, 92
Sam 24House 456 87, 21

That kind of thing can be done by creating a function to do
the concatenating. There's a nice one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
Hello Marshall, thank you for your response though the link does not work?
 
NeonSky said:
[quoted text clipped - 5 lines]

Hello Marshall, thank you for your response though the link does not work?3
Just in case anyone knows how to remedy the incorrect "solution" I have
reached please consider the following. At present the data is outputting as
follows...

Name Address Phone TransNumber
Joe 64 Lane 123 45, 92
Joe 64 Lane 123 92, 45
Sam 24House 456 87, 21
Sam 24House 456 21, 87

Which is close though clearly I do not need the "reversed" record sets.....
Any thoughts anyone?

I have achieved the above by creating a query with my source table, as well
as a copy of the source table in the same query, creating a one to one
relationship on the like fields (name, address), Then creating a new field
which formats as follows.....

TransNumber1: Format([tableA.transnumber]) & Format(", ") & Format([tableA_1.
transnumber])
 
That could be very easy if there is ALWAYS EXACTLY two records with the same
{ name, address, phone } :


SELECT a.name, a.address, a.phone, a.transNumber & ", " & b.transNumber
FROM tableName AS a INNER JOIN tableName AS b
ON a.name=b.name
AND a.address=b.address
AND a.phone =b.phone
AND a.transNumber < b.transNumber


It won't work with:


Name Address Phone TransNumber
Joe 64 Lane 123 45
Joe 64 Lane 123 92
Joe 64 Lane 123 95 ' 3 records for { Joe, 64
Lane ,123 }
Sam 24House 456 87 ' only one record for { Sam,
24House, 456 }
Mary 65Street 123 16
...



Vanderghast, Access MVP

NeonSky via AccessMonster.com said:
NeonSky said:
Good Afternoon,
[quoted text clipped - 5 lines]

Hello Marshall, thank you for your response though the link does not
work?3
Just in case anyone knows how to remedy the incorrect "solution" I have
reached please consider the following. At present the data is outputting
as
follows...

Name Address Phone TransNumber
Joe 64 Lane 123 45, 92
Joe 64 Lane 123 92, 45
Sam 24House 456 87, 21
Sam 24House 456 21, 87

Which is close though clearly I do not need the "reversed" record
sets.....
Any thoughts anyone?

I have achieved the above by creating a query with my source table, as
well
as a copy of the source table in the same query, creating a one to one
relationship on the like fields (name, address), Then creating a new field
which formats as follows.....

TransNumber1: Format([tableA.transnumber]) & Format(", ") &
Format([tableA_1.
transnumber])
 
Hello Michel,

Thank you for your response! Though I do have question, I have created the
temp table that shares all fields/data types from my original table, then I
created a "concat" field that is a number datatype where the "Required" field
is set to No (satisfiying the string nullable field setting?). Though when I
try to create the statement to fill my temp table I get an error. This is my
SQL...

SELECT testtest.Name, testtest.Address, testtest.Transnumber, iif(false, " ",
null) As Concat
FROM testtest
Group BY testtest.Name, testtest.Address
Into temp;

The error reads...."Syntax error (missing operator) in query expression
'testtest.Address Into Temp'

Thank you for your assistance!

Michel said:
You can do it with a query too, if you first have the right temporary table
(to collect the result).

Have a temp table, Temp, with all the fields defining the groups, plus one
extra field, Concat, a string nullable field.

Fill Temp with values for the groups, leave Concat filled with NULLs. Can be
done in one step (creating the table, and filling it) with something like:

SELECT name, address, phone, iif(false, " " , null ) AS concat
FROM yourTable
GROUP BY name, address, phone
INTO temp

You may add index on each of the fields which define the groups, if you have
a lot of records (if the next query is too slow).

Next, the following query will produce the concatenation itself:

--------------------------------------
UPDATE temp INNER JOIN yourTable
ON (temp.name=yourTable.name
AND temp.address=yourTable.address
AND temp.phone = yourTable.phone )

SET temp.concat = (temp.concat + ", ") & CStr(yourTable.TransNumber)
--------------------------------------

Note that the ON clause just match each fields which define the 'groups'.

That technique works only with Jet, not with MS SQL Server.

The problem is that you have to re-initialize the table Temp each time you
want to concatenate a new time.

Hoping it may help,
Vanderghast, Access MVP
Good Afternoon,
[quoted text clipped - 7 lines]
Hello Marshall, thank you for your response though the link does not work?
 
Hello Michel,

Indeed you are correct, it works with two records, though if there are more
than two it does not work (which is the case with my data). I created a temp
table and manually pasted the records in leaving the Concat field with a "0"
populating every row (which is a manual means of the step that you previously
outlined?). Here is my sql to perform the concantenation (please note that
Confo is used in substitution of Transnumber per my previous examples)......

UPDATE temp INNER JOIN testtest
ON (temp.name =testtest.name
AND temp.address = testtest.address)
SET temp.concat = (temp.concat + ", ") & CStr(testtest.Confo)

Which creates the following result.....

Name Address Confo Concat
Joe House12 123 999
Joe House12 176 999
Frank Lane34 988
Frank Lane34 629
Joe House12 999 999
Chris House3 3 3

Please assist, thank you!




Michel said:
That could be very easy if there is ALWAYS EXACTLY two records with the same
{ name, address, phone } :

SELECT a.name, a.address, a.phone, a.transNumber & ", " & b.transNumber
FROM tableName AS a INNER JOIN tableName AS b
ON a.name=b.name
AND a.address=b.address
AND a.phone =b.phone
AND a.transNumber < b.transNumber

It won't work with:

Name Address Phone TransNumber
Joe 64 Lane 123 45
Joe 64 Lane 123 92
Joe 64 Lane 123 95 ' 3 records for { Joe, 64
Lane ,123 }
Sam 24House 456 87 ' only one record for { Sam,
24House, 456 }
Mary 65Street 123 16
...

Vanderghast, Access MVP
[quoted text clipped - 28 lines]
Format([tableA_1.
transnumber])
 
The Concat field should be a string field filled with 'blank' (NULL) . My INTO was at the wrong place, should be:


SELECT name, address, iif(false, " " , null ) AS concat
INTO temp
FROM yourTable
GROUP BY name, address



(I also removed the field phone, it seems you don't need it)




In Northwind, as example, we can do :


SELECT Employees.City, IIf(False,"",Null) AS Concat INTO tempCities
FROM Employees
GROUP BY Employees.City;


to create the table tempCities (delete it if it already exists, before running this make table query) then,


UPDATE tempCities INNER JOIN Employees
ON tempCities.City = Employees.City
SET tempCities.Concat = ([concat] + ", ") & [LastName];



produces:


tempCities
City Concat
Kirkland Leverling
London Dodsworth, King, Suyama, Buchanan
Redmond Peacock
Seattle Callahan, Davolio
Tacoma Fuller






Vanderghast, Access MVP
 
Thank you Michel for your patience and effort in helping me to figure this
out. I truly appreciate it and hope you have a wonderful holiday!
Michel said:
The Concat field should be a string field filled with 'blank' (NULL) . My INTO was at the wrong place, should be:

SELECT name, address, iif(false, " " , null ) AS concat
INTO temp
FROM yourTable
GROUP BY name, address

(I also removed the field phone, it seems you don't need it)

In Northwind, as example, we can do :

SELECT Employees.City, IIf(False,"",Null) AS Concat INTO tempCities
FROM Employees
GROUP BY Employees.City;

to create the table tempCities (delete it if it already exists, before running this make table query) then,

UPDATE tempCities INNER JOIN Employees
ON tempCities.City = Employees.City
SET tempCities.Concat = ([concat] + ", ") & [LastName];

produces:

tempCities
City Concat
Kirkland Leverling
London Dodsworth, King, Suyama, Buchanan
Redmond Peacock
Seattle Callahan, Davolio
Tacoma Fuller

Vanderghast, Access MVP
 
NeonSky said:
Marshall said:
Good Afternoon,
[quoted text clipped - 14 lines]
Joe 64 Lane 123 45, 92
Sam 24House 456 87, 21

That kind of thing can be done by creating a function to do
the concatenating. There's a nice one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
Hello Marshall, thank you for your response though the link does not work?


I checked the link before I posted it and again just now and
it works for me. Perhaps the page was temporarily down or
maybe it was mangled by your newsreader program.
 
Back
Top