concatenating columns from many tables 1

[boodito]

Hello,
I have a table 'MASTER' connected to two other tables 'DESC' and 'INCL' where each element in 'MASTER' can have 0 to n elements in 'DESC' and 'INCL'.
What I need is to create a table (MASTER2) similar to (MASTER) with 2 more columns 'DESCRIPTION' and 'INCLUSION' where the values of 'DESC' for a given element of 'MASTER' (if exist), are concatenated in the column 'DESCRIPTION' and same for 'INCL'.
Thanx a lot
Boudi

 

[SkipVought]

hi,

No need to do this in VB, it seems.

What you do need to do is join the tables in a query using the query designer and then add a new column with the concatenated values. BTW the ampersand cahracter is the concatenate operator (check help if necessary)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[boodito]

hi,
Well if we know what we are concatenating, that will be ok.
I'll be more specific coz maybe i missed to mention something:
let's say we have 3 tables:'master', 'desc', 'libelle'
where 'master' has the key sid, beside other columns
'libelle' has teh key lid, beside other columns
and 'desc' has 2 columns: sid and lid and it shows for every value of sid, the corresponding lids (if they exist). i thought that we need a loop that fetches for every item of 'master' the corresponding 'libelles and concatenates them in one column and that's coz we don't know how many items in 'libelle' we have for each item of 'master'
Regards
boudi

 

[SkipVought]

[tt]
MASTER DESC LIBELLE
sid = sid
lid = lid
[/tt]

Select.....
From MASTER M
   , DESC D
   , LIB L
Where M.sid=D.sid
  AND D.lid=L.lid

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[boodito]

hello,
i think that u miss-understood me... or i explained bad..
what i need is to add a column to the table master called 'description' and where i want to concatenate the values of the descriptions associated with a every master row... so i think i need a vb script to do it.. knowing that a master row may have many descriptions associated with it and that's what i want to concatenate..
what's am thinkin about is considering i have 3 rows in 'DESC' associated to the first row of 'MASTER'
i.e.:
DESC
SID LID
1 1
1 2
1 3
and in LIBELLE
LID LIBELLE
1 A
2 B
3 C
what i want is to UPDATE the column description for the first row in table MASTER to have the value: 'A B C'
why i thought that i need a vb script is that i don't know how many descritions are associated with a given row in MASTER table.
Regards..
Boudi

 

[MajP]

I think I understand, but not sure. What I interpret is different from what Skip is interpreting. In this example I only demonstrate concatenating multiple records from DESC, but just expand the codes for other related tables. See if my example is right?

Master Table:

masterID ItemName
1 Item1
2 Item 2


DESC Table:

masterForiegnKey strDescription
1 2000 pounds
1 50 mph
1 Yellow paint
2 Four legs
2 Brown

The desired result.
New Master Table (with the concatenated data from DESC):

masterID ItemName concatenatedDescription
1 Item1 200 pounds, 50 mph, Yellow paint
2 Item 2 Four legs, Brown

If this is what you want to do, you may be able to use a combination of transform queries, but I think that doing this in code would be simpler. This would be my approach:

1) I would just build the concatenated fields right in the original Master table (ex concatenatedDescription), unless you really need a new table.

2) Build a seperate query for each related table that will be concatenated. (ex. qryMasterDESC and qryMasterINCL).

3)Now in code return a recordset, "rstMaster", from your Master table. All you really need is the primaryKey field returned, because you will loop trough each item in your Master table.

4) Now loop through your rstMaster getting each MasterID. Then create a new recordset, "rstQryMasterDesc", from your qryMasterDesc where the masterForiegnKey eqauls the MasterID.

5) Loop through rstQryMasterDESC and concatenate each strDESC value into a string variable.

6) When you have read each record in rstQryMasterDESC enter the string variable result into the concatenatedDescription field of the Master Table

7) Continue to loop through each item in the Master.

Here is some pseudo-like code to give you an idea. Not proper access code:

dim rstMaster as recordset
dim rstQryMasterDESC as recordset
dim strDESC as string
dim intMasterID as integer
set rstMaster = CurrentDb.OpenRecordset("tblMaster")

do while not rstMaster.end of file
intMasterID = rstMaster(“masterID”)
set rstQryMasterDESC = CurrentDb.OpenRecordset
("qryMasterDESC") where masterID = intMasterID
do while not rstQryMasterDESC.end of file
strDESC = strDESC & “,” & rstQryMasterDESC(“
strDescription”)
rstQryMasterDESC move to next record
loop “to next DESC”
rstMaster .edit
rstMaster(“concatenatedDescription”) = strDESC
rstMaster.update
loop “to next Item

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boodito]

Hello,
u got me right but except for table DESCR it does not contain the items i want to concatenate.. these items are in another table 'LIBELLE'
i will rewrite the example u gave:
MASTER:
SID ItemName Descriptions
1 item1
2 item2

DESCR:
SID LID
1 1
1 2
1 3
2 4
2 5

LIBELLE
LID DESC
1 A
2 B
3 C
4 D
5 E

I did create a field in the MASTER table that will contain the concatenated descriptions in a way that table MASTER will look like:
MASTER:
SID ItemName Description
1 item1 A, B, C
2 item2 D, E

Since am not a VBA programmer, It would be very nice to give me more specific VBA code. it will be very helpful.
thanx in advance.
boudi

 

[MajP]

The response from PHV uses the same logic except done as a function vice sub routine. His example contains the correct VB code. Since the information is in the LIBELLE table joined through the DESC you just need to do the same as I showed above except the recordset that I called "qryMasterDESC" would be built from joining Master, DESC, and LIBELLE. Call it qryMasterDescLibelle. I will try to send the correct code later, if you can not figure it out from PHV example.

 

[boodito]

well thanx,
i will try to work on it until u send the code
but i can't promise.. now am checkin the code sent by PHV and try to apply it on my case..
thanx a lot
Boudi

 

[MajP]

Boodito,
The following sub routine will do the problem as you described it.

Sub subConcatenate()

Dim rsItems As DAO.Recordset
' I built a query in the database linking MASTER, DESC
' and LIBELLE by LID and SID and
' called it qryMASTER_DESC_LIBELLE
Dim rsMASTER_DESC_LIBELLE As DAO.Recordset
Dim intCurrentItem As Integer
Dim strWhere
Dim strConcat As String

'Make a recordset from the MASTER table
Set rsItems = CurrentDb.OpenRecordset("MASTER", dbOpenDynaset)
'Move to the first record then Read through each item in
'the MASTER table
If Not rsItems.EOF Then
rsItems.MoveFirst
End If
Do While Not rsItems.EOF
'Get the SID of each item
intCurrentItem = rsItems("SID")
strWhere = "where [SID] = " & intCurrentItem
'Create a recordset from the qryMASTER_DESC_LIBELLE
' where the SID equals the current SID
Set rsMASTER_DESC_LIBELLE = CurrentDb.OpenRecordset("Select * from qryMASTER_DESC_LIBELLE " & strWhere, dbOpenDynaset)

'Loop through the descriptions
Do While Not rsMASTER_DESC_LIBELLE.EOF
strConcat = strConcat & _
rsMASTER_DESC_LIBELLE("DESC") & ", "
rsMASTER_DESC_LIBELLE.MoveNext
Loop
'Get rid of a comma at the end of the description
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - 2)
End If
'Put the strConcat in the MASTER table Descriptions
' field
rsItems.Edit
rsItems("Descriptions") = strConcat
rsItems.Update
rsItems.MoveNext
'set strConcat back to nothing
strConcat = ""
Loop

End Sub