469,347 Members | 18,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

Joining Multiple Rows Into One Row

Hi all!
I am having trouble with joining multiple rows into one row. I will appreciate any help.

For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation.

This is how the table looks like:
InvID / entryDate / invDate / vendor / invoiceAmount / building / account / percent
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /2 /BushBeans/ 0.25
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Target / 0.3

I want to make all the fields from same InvID in one row, so the above example would be like this:

InvID / entryDate / invDate / vendor / invoiceAmount / building1 / account1 / percent1 / building2 / account2 / percent2 / building3 / account3 / percent3/
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5 /2 /BushBeans/ 0.25 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7 / 1 /Target / 0.3

I know SQL, DAO, and VBA a little. It seems like I need to combine multiple functions, but I am having hardtime figureing out....
Aug 17 '07 #1
18 3604
ADezii
8,800 Expert 8TB
Hi all!
I am having trouble with joining multiple rows into one row. I will appreciate any help.

For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation.

This is how the table looks like:
InvID / entryDate / invDate / vendor / invoiceAmount / building / account / percent
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /2 /BushBeans/ 0.25
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Target / 0.3

I want to make all the fields from same InvID in one row, so the above example would be like this:

InvID / entryDate / invDate / vendor / invoiceAmount / building1 / account1 / percent1 / building2 / account2 / percent2 / building3 / account3 / percent3/
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5 /2 /BushBeans/ 0.25 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7 / 1 /Target / 0.3

I know SQL, DAO, and VBA a little. It seems like I need to combine multiple functions, but I am having hardtime figureing out....
For what possible reason would you want to use this logic?
Aug 17 '07 #2
For what possible reason would you want to use this logic?
ADezii,
thank you for your reply. I want to use this to be able to make labels function in report in Access... To fit all the record I want in single label, I have to have all record in one row.
Aug 20 '07 #3
ADezii
8,800 Expert 8TB
ADezii,
thank you for your reply. I want to use this to be able to make labels function in report in Access... To fit all the record I want in single label, I have to have all record in one row.
This is little more complex than I think you realize:
  • Is there a Maximum Number of the same Inventory IDs? If there is, what is this Number?
  • Do the following Fields already exist?
    Expand|Select|Wrap|Line Numbers
    1. building2 / account2 / percent2 
    2. building3 / account3 / percent3 
    3. building4 / account4 / percent4 
    4. building5 / account5 / percent5 
    5. building6 / account6 / percent6 
Aug 20 '07 #4
This is little more complex than I think you realize:
  • Is there a Maximum Number of the same Inventory IDs? If there is, what is this Number?
  • Do the following Fields already exist?
    Expand|Select|Wrap|Line Numbers
    1. building2 / account2 / percent2 
    2. building3 / account3 / percent3 
    3. building4 / account4 / percent4 
    4. building5 / account5 / percent5 
    5. building6 / account6 / percent6 
There is no maximum number of the same Inventory IDs, but I have never seen a label that has more than 7 rows. So, I would say maximum could be 7.

No, the fields you asked me do not exist in my database.
Aug 20 '07 #5
ADezii
8,800 Expert 8TB
There is no maximum number of the same Inventory IDs, but I have never seen a label that has more than 7 rows. So, I would say maximum could be 7.

No, the fields you asked me do not exist in my database.
It can be done but it would be much easier if these Fields pre-existed rather than checking for their existence, then dynamically creating them at Runtime, when and if needed. The Fields I am referring to are:
Expand|Select|Wrap|Line Numbers
  1. building2/account2/percent2 thru building7/account7/percent7
The price you'll pay will the the overhead relating to the additional 18 Fields. Just realized that I wasn't too clear on something - these new Fields will exist in a New Table that will be created and contain in a single Row, the data for mutilple IDs.

I'm going on Vacation for a few days but when I return I'll check in with you and if you are still interested, I'll work on it for you. Someone will probably come up with a better solution by then anyway.
Aug 21 '07 #6
ADezii
8,800 Expert 8TB
It can be done but it would be much easier if these Fields pre-existed rather than checking for their existence, then dynamically creating them at Runtime, when and if needed. The Fields I am referring to are:
Expand|Select|Wrap|Line Numbers
  1. building2/account2/percent2 thru building7/account7/percent7
The price you'll pay will the the overhead relating to the additional 18 Fields. Just realized that I wasn't too clear on something - these new Fields will exist in a New Table that will be created and contain in a single Row, the data for mutilple IDs.

I'm going on Vacation for a few days but when I return I'll check in with you and if you are still interested, I'll work on it for you. Someone will probably come up with a better solution by then anyway.
Apple001:
If you're still interested, let me know and I'll show you how to accomplish this.
Aug 25 '07 #7
Apple001:
If you're still interested, let me know and I'll show you how to accomplish this.
Sorry for the late reply. I have been busy with other projects, but YES, I am still interested of making this work. I really appreciate your help!!
Sep 6 '07 #8
ADezii
8,800 Expert 8TB
Sorry for the late reply. I have been busy with other projects, but YES, I am still interested of making this work. I really appreciate your help!!
I'll get started on a solution tomorrow but please be patient since I am very busy.
Sep 7 '07 #9
I'll get started on a solution tomorrow but please be patient since I am very busy.
Ok. Thank you ADezii!
Sep 7 '07 #10
ADezii
8,800 Expert 8TB
Ok. Thank you ADezii!
As promised, I've arrived at a solution for your particular dilemma. I'm sure that it is not the most elegant, efficient, or even the simplest solution, but the important factor is that it is fully operational and tested. I basically threw it together, and if I ever get more time, I'll try to improve it or drop it altogether and replace it with an alternative approach. The logic for this solution is implemented by 3 independent Recordsets, and a series of SQL Update Statements. Just follow the steps below exactly as indicated, and you should have no problem. The code is basically self-documenting, and admittedly confusing but should you have any questions at all, please do not hesitate to ask. The last line of code displays the results in tblAllocation_New. Have fun and get back to me on this one.
  1. Create tblAllocation_New which will consist of the following Fields and their respective Data Types:
    1. InvID - [LONG]
    2. EntryDate - [DATE]
    3. InvDate - [DATE]
    4. Vendor - [STRING]
    5. InvoiceAmount - [CURRENCY]
    6. Building1 - INTEGER
    7. Account1 - STRING
    8. Percent1 - SINGLE
    9. Building2 - INTEGER
    10. Account2 - STRING
    11. Percent2 - SINGLE
    12. Building3 - INTEGER
    13. Account3 - STRING
    14. Percent3 - SINGLE
    15. Building4 - INTEGER
    16. Account4 - STRING
    17. Percent4 - SINGLE
    18. Building5 - INTEGER
    19. Account5 - STRING
    20. Percent5 - SINGLE
    21. Building6 - INTEGER
    22. Account6 - STRING
    23. Percent6 - SINGLE
    24. Building7 - INTEGER
    25. Account7 - STRING
    26. Percent7 - SINGLE
  2. Make sure tblAllocation has the following structure:
    1. InvID - [LONG]
    2. EntryDate - [DATE]
    3. InvDate - [DATE]
    4. Vendor - [STRING]
    5. InvoiceAmount - [CURRENCY]
    6. Building - INTEGER
    7. Account - STRING
    8. Percent - SINGLE
  3. Copy and Paste the following code wherever you think is appropriate. You are probably better off placing it in a Sub-Routine or Function Procedure Call, where you can execute it from a specific Event.
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String, MyDB As DAO.Database, rstIDs As DAO.Recordset
    2. Dim rstMain As DAO.Recordset, intNoOfRecs As Integer, intCounter As Integer
    3. Dim rstNew As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb()
    6.  
    7. DoCmd.SetWarnings False
    8. 'DELETE ALL Records in tblAllocation_New
    9. DoCmd.RunSQL "Delete * From tblAllocation_New;"
    10.  
    11. 'Generate a Recordset of Unique Inventory IDs
    12. strSQL = "SELECT DISTINCT InvID FROM tblAllocation;"
    13. Set rstIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    14.  
    15. 'Create a Recordset based on tblAllocation
    16. Set rstMain = MyDB.OpenRecordset("tblAllocation", dbOpenDynaset)
    17. rstMain.MoveLast: rstMain.MoveFirst
    18.  
    19. 'Create a Recordset based on tblAllocation_New
    20. Set rstNew = MyDB.OpenRecordset("tblAllocation_New", dbOpenDynaset)
    21.  
    22. Do While Not rstIDs.EOF
    23.   'Create a Recordset based on tblAllocation
    24.   Set rstMain = MyDB.OpenRecordset("Select * From tblAllocation Where [InvID]=" & rstIDs![InvID], dbOpenDynaset)
    25.   rstMain.MoveLast: rstMain.MoveFirst
    26.   intNoOfRecs = rstMain.RecordCount
    27.     For intCounter = 1 To intNoOfRecs       '7 is the MAX
    28.       Select Case intCounter        'intCounter = Record Number given [InvID]
    29.         Case 1      '1st Record - Add ALL Fields
    30.           rstNew.AddNew
    31.             rstNew![InvID] = rstMain![InvID]
    32.             rstNew![EntryDate] = rstMain![EntryDate]
    33.             rstNew![InvDate] = rstMain![InvDate]
    34.             rstNew![Vendor] = rstMain![Vendor]
    35.             rstNew![InvoiceAmount] = rstMain![InvoiceAmount]
    36.             rstNew![Building1] = rstMain![Building]
    37.             rstNew![Account1] = rstMain![Account]
    38.             rstNew![Percent1] = rstMain![Percent]
    39.           rstNew.Update
    40.         Case 2      'now starts the Building, Account, and Percent increments
    41.           DoCmd.RunSQL "Update tblAllocation_New Set [Building2] =" & rstMain![Building] & _
    42.                        " Where [InvID]=" & rstIDs![InvID]
    43.           DoCmd.RunSQL "Update tblAllocation_New Set [Account2] ='" & rstMain![Account] & _
    44.                        "' Where [InvID]=" & rstIDs![InvID]
    45.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent2] =" & rstMain![Percent] & _
    46.                        " Where [InvID]=" & rstIDs![InvID]
    47.         Case 3
    48.           DoCmd.RunSQL "Update tblAllocation_New Set [Building3] =" & rstMain![Building] & _
    49.                        " Where [InvID]=" & rstIDs![InvID]
    50.           DoCmd.RunSQL "Update tblAllocation_New Set [Account3] ='" & rstMain![Account] & _
    51.                        "' Where [InvID]=" & rstIDs![InvID]
    52.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent3] =" & rstMain![Percent] & _
    53.                        " Where [InvID]=" & rstIDs![InvID]
    54.         Case 4
    55.           DoCmd.RunSQL "Update tblAllocation_New Set [Building4] =" & rstMain![Building] & _
    56.                        " Where [InvID]=" & rstIDs![InvID]
    57.           DoCmd.RunSQL "Update tblAllocation_New Set [Account4] ='" & rstMain![Account] & _
    58.                        "' Where [InvID]=" & rstIDs![InvID]
    59.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent4] =" & rstMain![Percent] & _
    60.                        " Where [InvID]=" & rstIDs![InvID]
    61.         Case 5
    62.           DoCmd.RunSQL "Update tblAllocation_New Set [Building5] =" & rstMain![Building] & _
    63.                        " Where [InvID]=" & rstIDs![InvID]
    64.           DoCmd.RunSQL "Update tblAllocation_New Set [Account5] ='" & rstMain![Account] & _
    65.                        "' Where [InvID]=" & rstIDs![InvID]
    66.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent5] =" & rstMain![Percent] & _
    67.                        " Where [InvID]=" & rstIDs![InvID]
    68.         Case 6
    69.           DoCmd.RunSQL "Update tblAllocation_New Set [Building6] =" & rstMain![Building] & _
    70.                        " Where [InvID]=" & rstIDs![InvID]
    71.           DoCmd.RunSQL "Update tblAllocation_New Set [Account6] ='" & rstMain![Account] & _
    72.                        "' Where [InvID]=" & rstIDs![InvID]
    73.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent6] =" & rstMain![Percent] & _
    74.                        " Where [InvID]=" & rstIDs![InvID]
    75.         Case 7
    76.           DoCmd.RunSQL "Update tblAllocation_New Set [Building7] =" & rstMain![Building] & _
    77.                        " Where [InvID]=" & rstIDs![InvID]
    78.           DoCmd.RunSQL "Update tblAllocation_New Set [Account7] ='" & rstMain![Account] & _
    79.                        "' Where [InvID]=" & rstIDs![InvID]
    80.           DoCmd.RunSQL "Update tblAllocation_New Set [Percent7] =" & rstMain![Percent] & _
    81.                        " Where [InvID]=" & rstIDs![InvID]
    82.         Case Else
    83.           'OP stated that he never saw more than 7
    84.       End Select
    85.       rstMain.MoveNext
    86.     Next
    87.   rstIDs.MoveNext
    88. Loop
    89.  
    90. rstNew.Close
    91. rstMain.Close
    92. rstIDs.Close
    93. Set rstMain = Nothing
    94. Set rstMain = Nothing
    95. Set rstIDs = Nothing
    96.  
    97. DoCmd.SetWarnings True
    98.  
    99. 'All your efforts should pay off here!
    100. DoCmd.OpenTable "tblAllocation_New", acViewNormal, acReadOnly
    101. DoCmd.Maximize
  4. Let me know how you make out.
Sep 7 '07 #11
in MS-Sql we can use group by; distinct row; just take a look in this aggreagate functions.
Sep 8 '07 #12
ADezii
8,800 Expert 8TB
in MS-Sql we can use group by; distinct row; just take a look in this aggreagate functions.
How can the result set be returned in a Linear fashion as requested by the OP? These entities also exist in Access SQL also.
Sep 8 '07 #13
How can the result set be returned in a Linear fashion as requested by the OP? These entities also exist in Access SQL also.
ADezii ,
Thank you for your solution!! I am going to try it now and I will let you know how it goes :-)
Sep 12 '07 #14
ADezii
8,800 Expert 8TB
ADezii ,
Thank you for your solution!! I am going to try it now and I will let you know how it goes :-)
Please keep me informed to to your progress. I tested it with sample data. and it worked fine.
Sep 12 '07 #15
Please keep me informed to to your progress. I tested it with sample data. and it worked fine.
ADezii, it works like magic!!!! YOU ARE THE BEST!!! Thank you so much!

I am gonna type what I did in here. I was not sure what "string" data type is, so I just picked "text." I insert the code on form's "On Close" event, so whenever somebody opens the form, save, and close, the table is updated. From there, I created query that calclates dollar amount for each building, and I made label report.

ADezii, why are you so nice to take your time to answer people's question in here? I am just curious.

Again, thank you so much!!! I really really appreciate your help :-) :-) :-)
Sep 12 '07 #16
ADezii
8,800 Expert 8TB
ADezii, it works like magic!!!! YOU ARE THE BEST!!! Thank you so much!

I am gonna type what I did in here. I was not sure what "string" data type is, so I just picked "text." I insert the code on form's "On Close" event, so whenever somebody opens the form, save, and close, the table is updated. From there, I created query that calclates dollar amount for each building, and I made label report.

ADezii, why are you so nice to take your time to answer people's question in here? I am just curious.

Again, thank you so much!!! I really really appreciate your help :-) :-) :-)
ADezii, why are you so nice to take your time to answer people's question in here? I am just curious
It's not just myself, but the other Moderators/Experts/Members who assist individuals on a daily basis - it is this environment that attracted me to TheScripts, and it is because of this environment that I'll probably be here until the day I die. It's a pleasure to work with such men and women who donate their time and ask nothing in return. As far as your question itself, I can't speak for anyone else, but for me it is the sheer pleasure that comes from helping others solve their problems, some of which seem insurmountable to them at times.
Sep 13 '07 #17
It's not just myself, but the other Moderators/Experts/Members who assist individuals on a daily basis - it is this environment that attracted me to TheScripts, and it is because of this environment that I'll probably be here until the day I die. It's a pleasure to work with such men and women who donate their time and ask nothing in return. As far as your question itself, I can't speak for anyone else, but for me it is the sheer pleasure that comes from helping others solve their problems, some of which seem insurmountable to them at times.
Wow...that is awesome. I thought you program for your job. Your comment part on VBA code was very helpful for me to understand the code. Some of the functions in there were not familiar to me, so I looked up reference to learn what they do. I learned a lot from your code. Thank you very much and I hope to talk to you here again!
Sep 13 '07 #18
ADezii
8,800 Expert 8TB
Wow...that is awesome. I thought you program for your job. Your comment part on VBA code was very helpful for me to understand the code. Some of the functions in there were not familiar to me, so I looked up reference to learn what they do. I learned a lot from your code. Thank you very much and I hope to talk to you here again!
The pleasure is all mine. If there is 1 trick that I learned over the years, its knowing what specific Function, Method, Event, Property, etc. to use in specific cases. It's a simple matter to reference the proper syntax - knowing exactly what will get the job done is not!

I thought you program for your job.
No, if I had to do this for a living, it would not be fun.

Your comment part on VBA code was very helpful for me to understand the code.
I feel that properly commenting your code is very important in programming, not only for yourself should you need to return to a code segment in the future, but for anyone else who may later need to modify your code. The trick here is to not over comment, this can be nearly as bad as not commenting at all.
Sep 13 '07 #19

Post your reply

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

Similar topics

4 posts views Thread by Job Lot | last post: by
reply views Thread by Palle Girgensohn | last post: by
3 posts views Thread by james | last post: by
2 posts views Thread by Michael | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.