将多行连接成一行 [英] Joining Multiple Rows Into One Row

问题描述

大家好！

我将多行连接成一行时遇到问题。我将不胜感激任何帮助。


对于查询中的列，我有：invID（autot number），entryDate，invDate，vendor，invoiceAmount来自名为tblInvoice的表，以及building，account，percent（以百分比表示的百分比）每个建筑物）来自名为tblAllocation的表。


这是表格的样子：

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 /沃尔玛/ 0.7

3 / 8/16/07 / 8/2/07 / CCC / $ 2,000.00 / 1 / Target / 0.3


我想把同一个InvID中的所有字段放在一行，所以上面的例子是这样的：


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 /沃尔玛/ 0.7 / 1 /目标/ 0.3


我知道SQL，DAO和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....

推荐答案

1,000.00 / 1 / eBay / 0.5

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

1,000.00 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC /

这篇关于将多行连接成一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！