通过创建新列来组合多个行 [英] Combining multiple rows by creating new columns

问题描述

大家好，我花了几个小时搜索，找不到一个与我合作的相似的例子。


是否可以通过创建新的组合多行列？搜索论坛让我相信它需要一些VBA代码。


我有什么：

Number |人|电子邮件|电话|传真|是/否|时间

1 |鲍勃| b.com | 123 | 123 | Y | 5:00

8 |鲍勃| b.com | 888 | 888 | N |

4 |鲍勃| b.com | 123 | 123 | Y | 5:00

1 |起诉| s.com | 456 | 456 | N |

5 |起诉| s.com | 456 | 456 | N |


我在找什么：

person |电子邮件|电话|传真| 1 | in_1 | 4 | in_4 | 5 | in_5 | 8 | in_8

bob | b.com | 888 | 888 | 5：00 | Y | Y | 5:00 | NULL | N | N | Y

起诉| s.com | 456 | 456 | N | Y | NULL | N | N | Y | NULL | N $


它将在数字列中获取每个不同的值，并创建一个带有该值的新列标题。


它将使用最新值更新电话和传真（数字列中的最高数字将始终是最新值。）


如果值为是，则会将时间而不是是。如果值为no，则表示否。


如果此人没有其他人拥有的号码，则会在in_＃列中输入no，否则将为yes 。


这可能需要多次查询才能更好地分解数据。

任何帮助都将非常感激，我是新来的访问和只创建了基本查询。

Hi everyone, I have spent a few hours searching and cannot find an example that is similar enough for me to work with.

Is it possible to combine multiple rows by creating new columns? Searching the forum makes me believe it will take some VBA code.

What I have:
Number | person | email | phone | fax | Y/N | time
1 | bob | b.com | 123 | 123 | Y | 5:00
8 | bob | b.com | 888 | 888 | N |
4 | bob | b.com | 123 | 123 | Y | 5:00
1 | sue | s.com | 456 | 456 | N |
5 | sue | s.com | 456 | 456 | N |

What I''m looking for:
person | email | phone | fax | 1 | in_1 | 4 | in_4 | 5 | in_5 | 8 | in_8
bob | b.com | 888 | 888 | 5:00| Y | Y | 5:00 |NULL | N | N | Y
sue | s.com | 456 | 456 | N | Y |NULL | N | N | Y |NULL | N


It will take each different value in the number column and create a new column titles with the value.

It will update the phone and fax with the most recent value (The highest number in the number column will always be the most recent value.)

If the value is Yes, it will put the time instead of yes. If the value is no, it will put no.

If the person does not have a number that someone else has it will put no in the in_# column, else it will put yes.


This may take several queries to break the data apart better.
Any help would be greatly appreciated, I am new to access and have only created basic queries.

推荐答案

您实际上希望在相关表中执行此操作作为记录而不是新字段。请参阅数据库规范化和表结构。通过这种方式，您可以添加无限数量的值，而无需编写任何代码或更改任何设计。

You would actually want to do this in a related table as records and not as new fields. See Database Normalization and Table Structures. By doing it this way, you can add unlimited number of values without having to code anything or change the design of anything.

您要求的内容可能变得相当复杂，很可能会导致结果在一些VBA中。它可能变得复杂的原因是它不是数据库中的标准实践，并且类似于非规范化。您正在寻找的可能性很大，可以通过交叉表查询完成，但我不会抱有希望。


所以，在开始前往开发路径之前，你可能最好问一下你试图用这个来完成什么？可能有一个更简单的解决方案;更符合Access中可用工具的解决方案。

What you are asking for could get pretty complicated and would most likely result in some VBA. The reason it could get complicated is that it isn''t a standard practice in a Database and is akin to de-normalization. There is a slim chance what you are looking for could be accomplished by a Crosstab Query, but I wouldn''t get your hopes up.

So, before heading down a development path, it would probably be best to ask what you are attempting to accomplish with this? There may be a simpler solution; a solution more inline with the tools available in Access.

我会调查你们两个人的建议，谢谢。

现在还没有''这似乎是一种简单的方法。


我能够弄清楚如何用时间替换是，所以不要使用是/否列和时间列新栏目是No / Time，如果是，则会显示时间而不是工作是。

I will look into what both of you have suggested, thank you.
For now there doesn''t seem to be an easy way of doing this.

I was able to figure out how to replace the Yes with the time so instead of having a Yes/No column and a time column the new column is No/Time where if it is a yes it will show the time instead of the work yes.

这篇关于通过创建新列来组合多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！