Joining Multiple Rows Into One Row - Microsoft Access / VBA

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

Subscribe Post Reply

3941

ADezii

8,834

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

Apple001

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,834

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

Apple001

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

building2 / account2 / percent2

building3 / account3 / percent3

building4 / account4 / percent4

building5 / account5 / percent5

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,834

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

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,834

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

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

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,834

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

Apple001

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,834

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.

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
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
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
5. Set MyDB = CurrentDb()
7. DoCmd.SetWarnings False
8. 'DELETE ALL Records in tblAllocation_New
9. DoCmd.RunSQL "Delete * From tblAllocation_New;"
11. 'Generate a Recordset of Unique Inventory IDs
12. strSQL = "SELECT DISTINCT InvID FROM tblAllocation;"
13. Set rstIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
15. 'Create a Recordset based on tblAllocation
16. Set rstMain = MyDB.OpenRecordset("tblAllocation", dbOpenDynaset)
17. rstMain.MoveLast: rstMain.MoveFirst
19. 'Create a Recordset based on tblAllocation_New
20. Set rstNew = MyDB.OpenRecordset("tblAllocation_New", dbOpenDynaset)
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
90. rstNew.Close
91. rstMain.Close
92. rstIDs.Close
93. Set rstMain = Nothing
94. Set rstMain = Nothing
95. Set rstIDs = Nothing
97. DoCmd.SetWarnings True
99. 'All your efforts should pay off here!
100. DoCmd.OpenTable "tblAllocation_New", acViewNormal, acReadOnly
101. DoCmd.Maximize
Let me know how you make out.

Sep 7 '07 #11

underscore

in MS-Sql we can use group by; distinct row; just take a look in this aggreagate functions.

Sep 8 '07 #12

ADezii

8,834

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

Apple001

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,834

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

Apple001

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,834

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

Apple001

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,834

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

Similar topics

Joining DataTables

by: Job Lot | last post by:

Is there anyway of Joining two or more DataTable with similar structure? I have three DataTables with following structures Data, AmountB/F, Repayments, InterestCharged and AmountC/F i want...

.NET Framework

performance hit when joining with a view?

by: Palle Girgensohn | last post by:

Hi! A performance question: I have some tables: Tabell "public.person" Kolumn | Typ | Modifierare ------------------+--------------------------+---------------...

PostgreSQL Database

joining several tables

by: james | last post by:

Hi, I would like to get all the records from 9 tables that have the same field value in one field (it is a unique field)that is shared by all the tables. Would this method of joining work: ...

DB2 Database

comparing & then joining 2 tables in ms-access

by: laxmiuk | last post by:

I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed, i need to raise an ECN (Engineering CHange Note)...

Microsoft Access / VBA

Problem joining child data (JOIN, subquery, or something else?)

by: Beowulf | last post by:

The problem: I'm updating a report to be "multi-language" capable. Previously, any items that had text associated with them were unconditionally pulling in the English text. The database has...

Microsoft SQL Server

conditional joining two table's data in one oledb query, is this possible?

by: pmouseca | last post by:

Hello all, I have two tables, one call Assignments, the other call Grades. In Assignment, the columns are: Asn_ID, Asn_Name, Asn_MaxGrade In Grades, the columns are: Grade_ID,...

Microsoft Access / VBA

How to delete multiple rows in a GridView?

by: Michael | last post by:

It seems that a gridview allows us to delete only a single row at a time. How to extend this functionality to select multiple rows and delete all of the selected rows in a single stroke? just like...

ASP.NET

Cloning and Deleting Multiple Rows with Javascript

by: nigelesquire | last post by:

Please help! I'm trying to clone and delete multiple rows with JavaScript. I need two delete buttons that work...! I only have one for now, but it's not working properly, the output count is...

Javascript

482

displaying previously entered multiple fields

by: bonneylake | last post by:

Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...

ColdFusion

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server