Data from multiple rows into one string

[orsoncart65]

Hi all
very very basic user here so please don't laugh!
I need to get into one text box, dog names from the same field but in multiple rows in an Access data table:
e.g.
row 1, name Jack
row 2 name Jill
row 3 name Bob

Jack and Jill belong to the same owner (same unique ID 273 for owner) Bob is seperate

I want to put into a report all the dog's names belonging to one person in one text field
e.g.
unique ID 273, dog name(s) Jack & Jill

At the moment, I am getting a seperate page for each dog.
Hope I have explained it clear enough and thanks in advance
Steve

[bartonc]

Not laughing, merely directing; to the Access Forum.

Good luck.

[ADezii]

Hi all
very very basic user here so please don't laugh!
I need to get into one text box, dog names from the same field but in multiple rows in an Access data table:
e.g.
row 1, name Jack
row 2 name Jill
row 3 name Bob

Jack and Jill belong to the same owner (same unique ID 273 for owner) Bob is seperate

I want to put into a report all the dog's names belonging to one person in one text field
e.g.
unique ID 273, dog name(s) Jack & Jill

At the moment, I am getting a seperate page for each dog.
Hope I have explained it clear enough and thanks in advance
Steve

ASSUMPTIONS (make changes where necessary):

1. Table Name: tblDogs
   1. [Owner_ID] - INTEGER
   2. [Dog Name] - STRING
2. Query Name: qryDogsAndOwner s
3. Function Name: fAppendDogNames

1. Create the following Function in a Standard Code Module
   [CODE=vb]Public Function fAppendDogNames (intOwnerID As Integer) As String
   Dim intNoOfDogs As Integer, strNames As String
   
   intNoOfDogs = DCount("*", "tblDogs", "[Owner_ID]=" & intOwnerID)
   
   If intNoOfDogs = 1 Then
   fAppendDogNames = DLookup("[Dog Name]", "tblDogs", "[Owner_ID]=" & intOwnerID)
   Exit Function
   Else
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecord set("Select * From tblDogs Where [Owner_ID]=" & intOwnerID, dbOpenSnapshot)
   MyRS.MoveFirst
   Do While Not MyRS.EOF
   If Len(strNames) = 0 Then
   strNames = MyRS![Dog Name]
   Else
   strNames = strNames & " & " & MyRS![Dog Name]
   End If
   MyRS.MoveNext
   Loop
   fAppendDogNames = strNames
   End If
   
   MyRS.Close
   Set MyRS = Nothing[/CODE]
2. Create the following Query:
   [CODE=sql]SELECT DISTINCT tblDogs.Owner_I D, fAppendDogNames ([Owner_ID]) AS [Names]
   FROM tblDogs;[/CODE]
3. Execute the Query to produce the results from Sample Data as listed below:
   Sample Data:
   [CODE=text]
   Owner_ID Dog Name
   273 Jack
   273 Jill
   273 Tom
   456 Bob
   456 Monique
   879 Joe
   999 Sam
   999 Dave Fred
   999 Linus
   999 Frederick
   999 Sally[/CODE]
   Query Results:
   [CODE=text]
   Owner_ID Names
   273 Jack & Jill & Tom
   456 Bob & Monique
   879 Joe
   999 Sam & Dave Fred & Linus & Frederick & Sally[/CODE]
4. Create a Report and set its RecordSource = qryDogsAndOwner s
5. Any questions, please feel free to ask. Either myself or one of the other Moderators/Experts will be glad to assist you.

[thomasfelton]

Hi,

I wonder if you could also help me - your posted answer was very easy to follow (Thanks from someone soooooo new to this stuff) but I have a slightly different senario. I run a voluntary children's camping organisation and am redoing the database. I want to be able to list in one query row (so I can use this for reports, forms, etc) a list of the camps each camper has attended along with whether they were a leader, camper, junior leader, etc for that particular camp each separated by a slash. Instead of having a whole page taken up with what camp each person has come to.

i.e. PerKey, Combined Field (PerActivityCam pGroupInfoXPerT ype & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActiv ityCampGroupInf o"

This has many fields listing payment, the colour group they were in, etc. However the fields needed for this: PerKey, ActivityKey, PerActivityCamp GroupInfoXPerTy pe

Each of these are numbers - so therefore I would need to get the corresponding text value for the PerActivityCamp GroupInfoXPerTy pe & ActivityKey

These are linked in two tables

Table ="TableActivity " (There are many fields - but relating ones are)
FieldName = ActivityKey (Key - autonumber)
FieldName= ActivityShortNa me (This contains the short camp or camp reunion name i.e. COct95.

Table ="TablePerTy pe" (There are many fields - but relating ones are)
FieldName = PerTypeKey (Key - autonumber)
FieldName= PerTypeShort (This contains the short camper type i.e. instead of Camper Senior its CS.

I hope I've given enough information and I hope there is an easy solution. In our past database we just used one table and so I recorded campers attendence inone field - but moving to a relational database has thrown this curveball in trying to give the leaders for each team information on how many previous camps each camper has been to.

I really appreciate you reading this and any help you could give.

Blessings

Thomas Felton

[ADezii]

Hi,

I wonder if you could also help me - your posted answer was very easy to follow (Thanks from someone soooooo new to this stuff) but I have a slightly different senario. I run a voluntary children's camping organisation and am redoing the database. I want to be able to list in one query row (so I can use this for reports, forms, etc) a list of the camps each camper has attended along with whether they were a leader, camper, junior leader, etc for that particular camp each separated by a slash. Instead of having a whole page taken up with what camp each person has come to.

i.e. PerKey, Combined Field (PerActivityCam pGroupInfoXPerT ype & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActiv ityCampGroupInf o"

This has many fields listing payment, the colour group they were in, etc. However the fields needed for this: PerKey, ActivityKey, PerActivityCamp GroupInfoXPerTy pe

Each of these are numbers - so therefore I would need to get the corresponding text value for the PerActivityCamp GroupInfoXPerTy pe & ActivityKey

These are linked in two tables

Table ="TableActivity " (There are many fields - but relating ones are)
FieldName = ActivityKey (Key - autonumber)
FieldName= ActivityShortNa me (This contains the short camp or camp reunion name i.e. COct95.

Table ="TablePerTy pe" (There are many fields - but relating ones are)
FieldName = PerTypeKey (Key - autonumber)
FieldName= PerTypeShort (This contains the short camper type i.e. instead of Camper Senior its CS.

I hope I've given enough information and I hope there is an easy solution. In our past database we just used one table and so I recorded campers attendence inone field - but moving to a relational database has thrown this curveball in trying to give the leaders for each team information on how many previous camps each camper has been to.

I really appreciate you reading this and any help you could give.

Blessings

Thomas Felton

Just subscribing, will return later.

[ADezii]

Hi,

I wonder if you could also help me - your posted answer was very easy to follow (Thanks from someone soooooo new to this stuff) but I have a slightly different senario. I run a voluntary children's camping organisation and am redoing the database. I want to be able to list in one query row (so I can use this for reports, forms, etc) a list of the camps each camper has attended along with whether they were a leader, camper, junior leader, etc for that particular camp each separated by a slash. Instead of having a whole page taken up with what camp each person has come to.

i.e. PerKey, Combined Field (PerActivityCam pGroupInfoXPerT ype & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActiv ityCampGroupInf o"

This has many fields listing payment, the colour group they were in, etc. However the fields needed for this: PerKey, ActivityKey, PerActivityCamp GroupInfoXPerTy pe

Each of these are numbers - so therefore I would need to get the corresponding text value for the PerActivityCamp GroupInfoXPerTy pe & ActivityKey

These are linked in two tables

Table ="TableActivity " (There are many fields - but relating ones are)
FieldName = ActivityKey (Key - autonumber)
FieldName= ActivityShortNa me (This contains the short camp or camp reunion name i.e. COct95.

Table ="TablePerTy pe" (There are many fields - but relating ones are)
FieldName = PerTypeKey (Key - autonumber)
FieldName= PerTypeShort (This contains the short camper type i.e. instead of Camper Senior its CS.

I hope I've given enough information and I hope there is an easy solution. In our past database we just used one table and so I recorded campers attendence inone field - but moving to a relational database has thrown this curveball in trying to give the leaders for each team information on how many previous camps each camper has been to.

I really appreciate you reading this and any help you could give.

Blessings

Thomas Felton

This would be a much easier process if a had a copy of the Database with a sub-set of the data. If you are willing to do this, simply let me know and I'll send you my E-Mail Address in a PM (Private Message). You could then send me the Database as an Attachment.

[thomasfelton]

This would be a much easier process if a had a copy of the Database with a sub-set of the data. If you are willing to do this, simply let me know and I'll send you my E-Mail Address in a PM (Private Message). You could then send me the Database as an Attachment.

I certainly can - that would be WONDERFUL - THANK YOU!!! I struggled to find a solution and have had to admit that this is beyond me :-)

I'll private message you with my email address - thank you again!!!

Blessings

Thomas Felton

[thomasfelton]

All I can say is THANK YOU!!!!!!!!!! The script you sent works wonderfully and I have referenced queries, etc from the new table so I can print out the one line on reports.

It has been such a blessing to have someone share their experience and knowledge in such a selfless way. Thank you from our whole organisation - this is going to help the leaders give the kids a much better camp experience!!!

I guess I only have one question on the script. How can I include this script in the queries, etc so that it updates the table each time?

Thank you sooooo much and every blessings!!!

Thomas Felton

[thomasfelton]

Sorry... I have one more question. The view solution form opens when the database first opens. However the form isn't loaded in the start up options - how do I stop it from loading each time I open the database???????

Thank you sooooo much and every blessings!!!

Thomas Felton[/QUOTE]

[ADezii]

Sorry... I have one more question. The view solution form opens when the database first opens. However the form isn't loaded in the start up options - how do I stop it from loading each time I open the database???????

Thank you sooooo much and every blessings!!!

Thomas Felton

Delete the AutoExec Macro.

[ADezii]

All I can say is THANK YOU!!!!!!!!!! The script you sent works wonderfully and I have referenced queries, etc from the new table so I can print out the one line on reports.

It has been such a blessing to have someone share their experience and knowledge in such a selfless way. Thank you from our whole organisation - this is going to help the leaders give the kids a much better camp experience!!!

I guess I only have one question on the script. How can I include this script in the queries, etc so that it updates the table each time?

Thank you sooooo much and every blessings!!!

Thomas Felton

[CODE=vb]
' Keep all our Declarations in one place
Dim strUniquePerKey s As String, MyDB As DAO.Database, rstUniqueKeys As DAO.Recordset
Dim strCampActiviti es As String, rstCampActiviti es As DAO.Recordset, strBuild As String
Dim sqlInsert As String

' 1) Created a Table named _tblFinalResult s which will contain just that, and consist of 3 Fields:
' [ID] {AutoNumber} (Primary Key)
' [Camper] {LONG} ==> parallels [PerKey]
' [Activity] {MEMO} ==> a combined Field consisting of [PerActivityCamp GroupInfoXPerTy pe] &
' [ActivityKey]

' 2) DELETE ALL Records (previous results, if any) from _tblFinalResult s
DoCmd.SetWarnin gs False 'no Prompts, please
DoCmd.RunSQL "DELETE * FROM _tblFinalResult s;"

' 3) Created _qryCampActivit ies which translates Numeric Values in [PerActivityCamp GroupInfoXPerTy pe]
' and [Activity] to corresponding Text Values and concatenates them in proper order

' 4) Create the following SQL String which will generate a 'Unique' listing of PerKeys for what will
' soon be our outer Recordset Loop
strUniquePerKey s = "SELECT DISTINCT TableXPerActivi tyCampGroupInfo .PerKey " & _
"FROM TableXPerActivi tyCampGroupInfo ;"

' 5) Create the Recordset for the Unique PerKeys
Set MyDB = CurrentDb() 'will be utilized for all Recordsets
Set rstUniqueKeys = MyDB.OpenRecord set(strUniquePe rKeys, dbOpenSnapshot)

' 6) For each unique PerKey, string together all the Activities
Do While Not rstUniqueKeys.E OF
' 7) Create the Recordset for Camp Activities based on each unique PerKey
strCampActiviti es = "SELECT [PerKey], [Camp Activities] FROM _qryCampActivit ies WHERE [PerKey] = " & _
rstUniqueKeys![PerKey]
Set rstCampActiviti es = MyDB.OpenRecord set(strCampActi vities, dbOpenSnapshot)
Do While Not rstCampActiviti es.EOF
strBuild = strBuild & rstCampActiviti es![Camp Activities] & "/"
rstCampActiviti es.MoveNext
Loop
strBuild = Left$(strBuild, Len(strBuild) - 1) 'remove the trailing "/"
' 8) Append this entire mess into _tblFinalResult s
sqlInsert = "INSERT INTO _tblFinalResult s([Camper], [Activity]) Values(" & rstUniqueKeys![PerKey] & _
", '" & strBuild & "')"
DoCmd.RunSQL sqlInsert 'do the actual Append Operation
'Debug.Print rstUniqueKeys![PerKey] & " ==> " & strBuild Test String
strBuild = "" 'must Reset
rstUniqueKeys.M oveNext
Loop

' 9) Created _rptFinalResult s which displays the Records in _tblFinalResult s in the correct Format,
'so without further adieu, let's see the fruits of our labor
'DoCmd.OpenRepo rt "_rptFinalResul ts", acViewPreview, , , acWindowNormal
DoCmd.OpenRepor t "_rptFinalResul ts", acViewPreview

DoCmd.SetWarnin gs True 'Reset - view Warning Messages

rstUniqueKeys.C lose
rstCampActiviti es.Close
Set rstUniqueKeys = Nothing
Set rstCampActiviti es = Nothing[/CODE]
Everytime you run this code, all existing Records (prior results) are deleted from _tblFinalResult s, then Appended again later on. After code execution, Records will dynamically be generated and Appended to _tblFinalsResul ts. It is at that time, that the true State of the Database can be viewed as far as Campers and joined Activities are concerned. For instance: if you Add new Campers or Add new Activities for existing Campers, execute the code and the results in _tblFinalResult s will be current. I hope this is what you were asking, if not, just let me know.

Points to Mention:

1. Because of the DELETE/APPEND process involved concerning _tblFinalResult s every time the code is executed, I would recommend periodically running a Compact and Repair on the Database. Be sure to always Backup the Database first as a precaution!
2. As far as what I was saying concerning _tblFinalResult s, take a look at Code Lines 6 to 14, and 39 to 42, in order to get a clearer picture of everything.
3. As always, any questions please do not hesitate to ask.
4. You may also wish to substitute Line #51 for Line #50. This should give you a clearer view of the Report as well as avoiding the problem of an Error being generated in earlier Versions of Access since the Window Mode Argument is not supported. Try it out yourself, and see what you think.