Joining Multiple Rows Into One Row

**ADezii** · Aug 17 '07, 11:34 PM

Originally posted by Apple001

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?

**Apple001** · Aug 20 '07, 03:02 PM

Originally posted by ADezii

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.

**ADezii** · Aug 20 '07, 07:57 PM

Originally posted by Apple001

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?
[CODE=text]building2 / account2 / percent2
building3 / account3 / percent3
building4 / account4 / percent4
building5 / account5 / percent5
building6 / account6 / percent6 [/CODE]

**Apple001** · Aug 20 '07, 08:17 PM

Originally posted by ADezii

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?
[CODE=text]building2 / account2 / percent2
building3 / account3 / percent3
building4 / account4 / percent4
building5 / account5 / percent5
building6 / account6 / percent6 [/CODE]

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.

**ADezii** · Aug 21 '07, 12:00 AM

Originally posted by Apple001

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:
[CODE=text]building2/account2/percent2 thru building7/account7/percent7[/CODE]
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.

**ADezii** · Aug 25 '07, 01:12 AM

Originally posted by ADezii

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:
[CODE=text]building2/account2/percent2 thru building7/account7/percent7[/CODE]
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.

**Apple001** · Sep 6 '07, 08:19 PM

Originally posted by ADezii

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!!

**ADezii** · Sep 7 '07, 12:17 AM

Originally posted by Apple001

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.

**Apple001** · Sep 7 '07, 05:51 PM

Originally posted by ADezii

I'll get started on a solution tomorrow but please be patient since I am very busy.

Ok. Thank you ADezii!

**ADezii** · Sep 7 '07, 11:40 PM

Originally posted by Apple001

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_N ew. Have fun and get back to me on this one.

Create tblAllocation_N ew 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.
[CODE=vb]Dim strSQL As String, MyDB As DAO.Database, rstIDs As DAO.Recordset
Dim rstMain As DAO.Recordset, intNoOfRecs As Integer, intCounter As Integer
Dim rstNew As DAO.Recordset

Set MyDB = CurrentDb()

DoCmd.SetWarnin gs False
'DELETE ALL Records in tblAllocation_N ew
DoCmd.RunSQL "Delete * From tblAllocation_N ew;"

'Generate a Recordset of Unique Inventory IDs
strSQL = "SELECT DISTINCT InvID FROM tblAllocation;"
Set rstIDs = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)

'Create a Recordset based on tblAllocation
Set rstMain = MyDB.OpenRecord set("tblAllocat ion", dbOpenDynaset)
rstMain.MoveLas t: rstMain.MoveFir st

'Create a Recordset based on tblAllocation_N ew
Set rstNew = MyDB.OpenRecord set("tblAllocat ion_New", dbOpenDynaset)

Do While Not rstIDs.EOF
'Create a Recordset based on tblAllocation
Set rstMain = MyDB.OpenRecord set("Select * From tblAllocation Where [InvID]=" & rstIDs![InvID], dbOpenDynaset)
rstMain.MoveLas t: rstMain.MoveFir st
intNoOfRecs = rstMain.RecordC ount
For intCounter = 1 To intNoOfRecs '7 is the MAX
Select Case intCounter 'intCounter = Record Number given [InvID]
Case 1 '1st Record - Add ALL Fields
rstNew.AddNew
rstNew![InvID] = rstMain![InvID]
rstNew![EntryDate] = rstMain![EntryDate]
rstNew![InvDate] = rstMain![InvDate]
rstNew![Vendor] = rstMain![Vendor]
rstNew![InvoiceAmount] = rstMain![InvoiceAmount]
rstNew![Building1] = rstMain![Building]
rstNew![Account1] = rstMain![Account]
rstNew![Percent1] = rstMain![Percent]
rstNew.Update
Case 2 'now starts the Building, Account, and Percent increments
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building2] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account2] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent2] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case 3
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building3] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account3] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent3] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case 4
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building4] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account4] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent4] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case 5
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building5] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account5] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent5] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case 6
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building6] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account6] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent6] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case 7
DoCmd.RunSQL "Update tblAllocation_N ew Set [Building7] =" & rstMain![Building] & _
" Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Account7] ='" & rstMain![Account] & _
"' Where [InvID]=" & rstIDs![InvID]
DoCmd.RunSQL "Update tblAllocation_N ew Set [Percent7] =" & rstMain![Percent] & _
" Where [InvID]=" & rstIDs![InvID]
Case Else
'OP stated that he never saw more than 7
End Select
rstMain.MoveNex t
Next
rstIDs.MoveNext
Loop

rstNew.Close
rstMain.Close
rstIDs.Close
Set rstMain = Nothing
Set rstMain = Nothing
Set rstIDs = Nothing

DoCmd.SetWarnin gs True

'All your efforts should pay off here!
DoCmd.OpenTable "tblAllocation_ New", acViewNormal, acReadOnly
DoCmd.Maximize[/CODE]
Let me know how you make out.

**underscore** · Sep 8 '07, 03:12 AM

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

**ADezii** · Sep 8 '07, 11:36 AM

Originally posted by underscore

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.

**Apple001** · Sep 12 '07, 06:03 PM

Originally posted by ADezii

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 :-)

**ADezii** · Sep 12 '07, 06:06 PM

Originally posted by Apple001

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.

Joining Multiple Rows Into One Row

Joining Multiple Rows Into One Row

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment