By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,281 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,281 IT Pros & Developers. It's quick & easy.

Data from multiple rows into one string

P: 1
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
Sep 8 '07 #1
Share this Question
Share on Google+
10 Replies


bartonc
Expert 5K+
P: 6,596
Not laughing, merely directing; to the Access Forum.

Good luck.
Sep 8 '07 #2

ADezii
Expert 5K+
P: 8,669
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: qryDogsAndOwners
  3. Function Name: fAppendDogNames
  1. Create the following Function in a Standard Code Module
    Expand|Select|Wrap|Line Numbers
    1. Public Function fAppendDogNames(intOwnerID As Integer) As String
    2. Dim intNoOfDogs As Integer, strNames As String
    3.  
    4. intNoOfDogs = DCount("*", "tblDogs", "[Owner_ID]=" & intOwnerID)
    5.  
    6. If intNoOfDogs = 1 Then
    7.   fAppendDogNames = DLookup("[Dog Name]", "tblDogs", "[Owner_ID]=" & intOwnerID)
    8.     Exit Function
    9. Else
    10.   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    11.   Set MyDB = CurrentDb()
    12.   Set MyRS = MyDB.OpenRecordset("Select * From tblDogs Where [Owner_ID]=" & intOwnerID, dbOpenSnapshot)
    13.     MyRS.MoveFirst
    14.     Do While Not MyRS.EOF
    15.       If Len(strNames) = 0 Then
    16.         strNames = MyRS![Dog Name]
    17.       Else
    18.         strNames = strNames & " & " & MyRS![Dog Name]
    19.       End If
    20.         MyRS.MoveNext
    21.     Loop
    22.     fAppendDogNames = strNames
    23. End If
    24.  
    25. MyRS.Close
    26. Set MyRS = Nothing
  2. Create the following Query:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblDogs.Owner_ID, fAppendDogNames([Owner_ID]) AS [Names]
    2. FROM tblDogs;
  3. Execute the Query to produce the results from Sample Data as listed below:
    Sample Data:
    Expand|Select|Wrap|Line Numbers
    1. Owner_ID    Dog Name
    2. 273            Jack
    3. 273            Jill
    4. 273            Tom
    5. 456            Bob
    6. 456            Monique
    7. 879            Joe
    8. 999            Sam
    9. 999            Dave Fred
    10. 999            Linus
    11. 999            Frederick
    12. 999            Sally
    Query Results:
    Expand|Select|Wrap|Line Numbers
    1. Owner_ID    Names
    2. 273            Jack & Jill & Tom
    3. 456            Bob & Monique
    4. 879            Joe
    5. 999            Sam & Dave Fred & Linus & Frederick & Sally
  4. Create a Report and set its RecordSource = qryDogsAndOwners
  5. Any questions, please feel free to ask. Either myself or one of the other Moderators/Experts will be glad to assist you.
Sep 9 '07 #3

P: 4
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 (PerActivityCampGroupInfoXPerType & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActivityCampGroupInfo"

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

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

These are linked in two tables

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

Table ="TablePerType" (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
Jan 3 '08 #4

ADezii
Expert 5K+
P: 8,669
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 (PerActivityCampGroupInfoXPerType & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActivityCampGroupInfo"

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

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

These are linked in two tables

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

Table ="TablePerType" (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.
Jan 4 '08 #5

ADezii
Expert 5K+
P: 8,669
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 (PerActivityCampGroupInfoXPerType & ActivityKey)
23, CSCOct95/LJCRJan96/LLCOct99

The main information is in one table "TableXPerActivityCampGroupInfo"

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

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

These are linked in two tables

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

Table ="TablePerType" (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.
Jan 4 '08 #6

P: 4
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
Jan 5 '08 #7

P: 4
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
Jan 7 '08 #8

P: 4
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]
Jan 7 '08 #9

ADezii
Expert 5K+
P: 8,669
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.
Jan 8 '08 #10

ADezii
Expert 5K+
P: 8,669
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
Expand|Select|Wrap|Line Numbers
  1. ' Keep all our Declarations in one place
  2. Dim strUniquePerKeys As String, MyDB As DAO.Database, rstUniqueKeys As DAO.Recordset
  3. Dim strCampActivities As String, rstCampActivities As DAO.Recordset, strBuild As String
  4. Dim sqlInsert As String
  5.  
  6. ' 1) Created a Table named _tblFinalResults which will contain just that, and consist of 3 Fields:
  7. '    [ID] {AutoNumber} (Primary Key)
  8. '    [Camper] {LONG} ==> parallels [PerKey]
  9. '    [Activity] {MEMO} ==> a combined Field consisting of [PerActivityCampGroupInfoXPerType] &
  10. '                              [ActivityKey]
  11.  
  12. ' 2) DELETE ALL Records (previous results, if any) from _tblFinalResults
  13. DoCmd.SetWarnings False     'no Prompts, please
  14.   DoCmd.RunSQL "DELETE * FROM _tblFinalResults;"
  15.  
  16. ' 3) Created _qryCampActivities which translates Numeric Values in [PerActivityCampGroupInfoXPerType]
  17. '    and [Activity] to corresponding Text Values and concatenates them in proper order
  18.  
  19. ' 4) Create the following SQL String which will generate a 'Unique' listing of PerKeys for what will
  20. ' soon be our outer Recordset Loop
  21. strUniquePerKeys = "SELECT DISTINCT TableXPerActivityCampGroupInfo.PerKey " & _
  22.                    "FROM TableXPerActivityCampGroupInfo;"
  23.  
  24. ' 5) Create the Recordset for the Unique PerKeys
  25. Set MyDB = CurrentDb()      'will be utilized for all Recordsets
  26. Set rstUniqueKeys = MyDB.OpenRecordset(strUniquePerKeys, dbOpenSnapshot)
  27.  
  28. ' 6) For each unique PerKey, string together all the Activities
  29. Do While Not rstUniqueKeys.EOF
  30.   ' 7) Create the Recordset for Camp Activities based on each unique PerKey
  31.     strCampActivities = "SELECT [PerKey], [Camp Activities] FROM _qryCampActivities WHERE [PerKey] = " & _
  32.                         rstUniqueKeys![PerKey]
  33.     Set rstCampActivities = MyDB.OpenRecordset(strCampActivities, dbOpenSnapshot)
  34.       Do While Not rstCampActivities.EOF
  35.         strBuild = strBuild & rstCampActivities![Camp Activities] & "/"
  36.         rstCampActivities.MoveNext
  37.       Loop
  38.         strBuild = Left$(strBuild, Len(strBuild) - 1)   'remove the trailing "/"
  39.         ' 8) Append this entire mess into _tblFinalResults
  40.         sqlInsert = "INSERT INTO _tblFinalResults([Camper], [Activity]) Values(" & rstUniqueKeys![PerKey] & _
  41.                     ", '" & strBuild & "')"
  42.         DoCmd.RunSQL sqlInsert      'do the actual Append Operation
  43.          'Debug.Print rstUniqueKeys![PerKey] & " ==> " & strBuild     Test String
  44.           strBuild = ""   'must Reset
  45.   rstUniqueKeys.MoveNext
  46. Loop
  47.  
  48. ' 9) Created _rptFinalResults which displays the Records in _tblFinalResults in the correct Format,
  49.      'so without further adieu, let's see the fruits of our labor
  50.      'DoCmd.OpenReport "_rptFinalResults", acViewPreview, , , acWindowNormal
  51.       DoCmd.OpenReport "_rptFinalResults", acViewPreview
  52.  
  53. DoCmd.SetWarnings True      'Reset - view Warning Messages
  54.  
  55. rstUniqueKeys.Close
  56. rstCampActivities.Close
  57. Set rstUniqueKeys = Nothing
  58. Set rstCampActivities = Nothing
Everytime you run this code, all existing Records (prior results) are deleted from _tblFinalResults, then Appended again later on. After code execution, Records will dynamically be generated and Appended to _tblFinalsResults. 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 _tblFinalResults 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 _tblFinalResults 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 _tblFinalResults, 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.
Jan 8 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.