另一个SQL问题 - 使用列名更新值 [英] Another SQL Question - Update Value with Column Name

问题描述

我正在尝试将一个短而胖（63列）的表变成一个很高的表：
和skinny（7列）。基本上，我正在尝试创建一个反向

交叉表。在VBA中使用循环结构和SQL。我想把

列的名称输入到descritor字段。


这不是表，但是会作为一个比真实的

交易更好的例证。


如果表格如下：


Salesman 1月2月3月

安德森410 747 142

琼斯64 981 828

史密斯837 583 561

但我想要这个：


推销员月份金额

史密斯1月837

Jones Jan 64

Anderson Jan 410

Smith 2月583

Jones 2月981

Anderson 2月747

Smith Mar 561

Jones Mar 828

Anderson Mar 142


我将列名称分配给变量，如下所示：" [" &

tdf.Fields（fld）.Name& "]"


谢谢。

I am trying to turn a short and fat (63 columns) table into one that is tall
and skinny (7 columns). Basically, I am trying to create a "reverse
crosstab" using a looping structure in VBA along with SQL. I''d like to take
the name of the column and input it into a descritor field.

This isn''t the table, but will serve as a better illustration than the real
deal.

If the table looks like this:

Salesman Jan Feb Mar
Anderson 410 747 142
Jones 64 981 828
Smith 837 583 561
But I want this:

Salesman Month Amount
Smith Jan 837
Jones Jan 64
Anderson Jan 410
Smith Feb 583
Jones Feb 981
Anderson Feb 747
Smith Mar 561
Jones Mar 828
Anderson Mar 142

How can I create a new column with the name of the old column as the value?
I am assigning the column name to a variable as follows: "[" &
tdf.Fields(fld).Name & "]"

Thanks.

推荐答案

Colleyville Alan写道：

Colleyville Alan wrote:

我正在尝试将一个短而胖（63列）的表变成一个高而又瘦的（7列）表。基本上，我正在尝试创建一个反向交叉表。在VBA中使用循环结构和SQL。我想把这个专栏的名字带到一个descritor字段中。

这不是桌子，但它将作为一个比真实更好的插图


如果表格如下：

推销员Jan Feb Mar
Anderson 410 747 142
Jones 64 981 828 <史密斯837 583 561

但我想要这个：

推销员月份金额
史密斯Jan 837
Jones Jan 64
安德森Jan 410
史密斯2月583
琼斯2月981
安德森2月747
史密斯3月561
琼斯3月828
安德森3月142

如何创建一个新列，其中旧列的名称为值？
我将列名称分配给变量，如下所示：" [" &
tdf.Fields（fld）.Name& "]"

谢谢。

I am trying to turn a short and fat (63 columns) table into one that is tall
and skinny (7 columns). Basically, I am trying to create a "reverse
crosstab" using a looping structure in VBA along with SQL. I''d like to take
the name of the column and input it into a descritor field.

This isn''t the table, but will serve as a better illustration than the real
deal.

If the table looks like this:

Salesman Jan Feb Mar
Anderson 410 747 142
Jones 64 981 828
Smith 837 583 561

But I want this:

Salesman Month Amount
Smith Jan 837
Jones Jan 64
Anderson Jan 410
Smith Feb 583
Jones Feb 981
Anderson Feb 747
Smith Mar 561
Jones Mar 828
Anderson Mar 142

How can I create a new column with the name of the old column as the value?
I am assigning the column name to a variable as follows: "[" &
tdf.Fields(fld).Name & "]"

Thanks.

我只想创建第二个表。然后使用追加查询。


我创建Table1，其中包含字段名称和Jan ... Dec。


然后我创建了Table2字段Name，MonthCol和Amt。


然后我运行了这段代码。它从1月到12月循环几个月（12次），每次追加特定列的记录时，每次都是
。我把警告变为假

因为我不想看到每次执行循环的下一次

迭代时我都会附加记录。


Sub AppendIt（）

Dim strSQL As String

Dim strMon As String

Dim intFor As Integer

DoCmd.SetWarnings错误

对于intFor = 1到12

strMon =格式（DateSerial（年（日期），intFor，1），" ; mmm"）

strSQL =" INSERT INTO Table2（Name，MonthCol，Amt）" &安培; _

" SELECT Table1.Name，" &安培; strMon& ，表1。 &安培; strMon& "来自

表1"

CurrentDb.Execute strSQL

下一页

DoCmd.SetWarnings True

MsgBox完成


结束子

I would simply create the second table. Then use an append query.

I created Table1 with the fields Name and Jan...Dec.

I then created Table2 with the fields Name, MonthCol, and Amt.

I then ran this code. It loops thru the months (12 times), from Jan to Dec,
each time appending records for the specific column. I turned warning to false
because I didn''t want to see that I was appending the records each time the next
iteration of the loop was performed.

Sub AppendIt()
Dim strSQL As String
Dim strMon As String
Dim intFor As Integer
DoCmd.SetWarnings False
For intFor = 1 To 12
strMon = Format(DateSerial(Year(Date), intFor, 1), "mmm")
strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
"SELECT Table1.Name, " & strMon & ", Table1." & strMon & " From
Table1"
CurrentDb.Execute strSQL
Next
DoCmd.SetWarnings True
MsgBox "Done"

End Sub

谢谢。这对我不起作用，但我很感激回复。我有
创建了第二个表，我确实有代码循环和插入。但是当我提到
时，我展示的例子不是真正的表格，只是比我面对的真正问题更容易理解的东西。我有一个63列的

表，包含50多列数据，我想把它变成一个7列的表。

这意味着我有5个关键字段，一个提交金额和一个字段

包含旧字段的名称（我所显示的简化

示例中的月份名称）。


由于有超过50个字段并且它们有不常见的名称（

共同基金的名称），我需要一种方法来实际获取他们的名字。我曾经能够在Paradox for DOS中做到这一点，所以它必须在Access中可行，尽管

Access 2000圣经没有告诉我如何（至少我还没有能够找到它。

UN Me <未** @ together.com>在消息中写道

新闻：3F *************** @ together.com ...

Thanks. THis will not work for me, but I do appreciate the reply. I have
created a 2nd table and I do have code to loop and insert. But as I
mentioned, the example I showed was not the real table, just something
easier to understand than the real problem I am facing. I have a 63-column
table with 50+ columns of data that I want to turn into a 7-column table.
That means I have 5 key fields, one filed with the amount and one field that
contains the name of the old field (the name of the month in the simplified
example I showed).

Since there are over 50 fields and they have uncommon names (the names of
mutual funds), I need a way to actually get their names. I used to be able
to do this in Paradox for DOS, so it must be doable in Access, though the
Access 2000 Bible does not show me how (at least I have not been able to
find it).
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...

Colleyville Alan写道：

Colleyville Alan wrote:

我正在尝试将一个短而胖（63列）的表转换成
高和瘦（7列）的表。基本上，我正在尝试创建一个反向交叉表。在VBA中使用循环结构和SQL。我想
取名字的名称并将其输入到descritor字段。

这不是表，但将作为一个比$更好的例证b $ b真实交易。

如果表格如下：

推销员Jan Feb Mar
Anderson 410 747 142
Jones 64 981 828 <史密斯837 583 561

但我想要这个：

推销员月份金额
史密斯Jan 837
Jones Jan 64
安德森Jan 410
史密斯2月583
琼斯2月981
安德森2月747
史密斯3月561
琼斯3月828
安德森3月142

如何使用旧列的名称创建一个新列作为
值？我将列名称分配给变量，如下所示：" [" &
tdf.Fields（fld）.Name& "]"

谢谢。
我只想创建第二个表。然后使用追加查询。

我创建了Table1，其中包含Name和Jan ... Dec.

然后我创建了Table2，其中包含Name，MonthCol和Amt字段。

然后我运行了这段代码。它从月份（12次）循环到

I am trying to turn a short and fat (63 columns) table into one that is tall and skinny (7 columns). Basically, I am trying to create a "reverse
crosstab" using a looping structure in VBA along with SQL. I''d like to take the name of the column and input it into a descritor field.

This isn''t the table, but will serve as a better illustration than the real deal.

If the table looks like this:

Salesman Jan Feb Mar
Anderson 410 747 142
Jones 64 981 828
Smith 837 583 561

But I want this:

Salesman Month Amount
Smith Jan 837
Jones Jan 64
Anderson Jan 410
Smith Feb 583
Jones Feb 981
Anderson Feb 747
Smith Mar 561
Jones Mar 828
Anderson Mar 142

How can I create a new column with the name of the old column as the value? I am assigning the column name to a variable as follows: "[" &
tdf.Fields(fld).Name & "]"

Thanks.
I would simply create the second table. Then use an append query.

I created Table1 with the fields Name and Jan...Dec.

I then created Table2 with the fields Name, MonthCol, and Amt.

I then ran this code. It loops thru the months (12 times), from Jan to

Dec，每次都附加特定列的记录。我向
false发出警告，因为我不想看到我每次在循环的下一次迭代中执行
时附加记录。

Sub AppendIt（ ）
Dim strSQL As String
Dim strMon As String
Dim intFor As Integer
DoCmd.SetWarnings False
for intFor = 1 to 12
strMon = Format （DateSerial（Year（Date），intFor，1），" mmm"）
strSQL =" INSERT INTO Table2（Name，MonthCol，Amt）" &安培; _
" SELECT Table1.Name，" &安培; strMon& ，表1。 &安培; strMon& "
来自Table1"
CurrentDb.Execute strSQL
下一页
DoCmd.SetWarnings True
MsgBox完成

End Sub

您可以执行以下操作来获取字段名称（警告：空气

代码）：


将字段调暗为DAO.Field

将theTable调暗为DAO.Table


设置theTable = CurrentDb.TableDefs（" FatTableName" ;）


每个theField inTable.Fields

Msgbox theField.Name

Next theTable

设置theField = Nothing

设置theTable = Nothing


2003年12月13日星期六14:04:36 GMT，Colleyville Alan ;

< ae *********** @ nospam.comcast.net>写道：

You can do something like this to get the field names (warning: air
code):

Dim theField as DAO.Field
Dim theTable as DAO.Table

Set theTable = CurrentDb.TableDefs("FatTableName")

For Each theField in theTable.Fields
Msgbox theField.Name
Next theTable

Set theField = Nothing
Set theTable = Nothing

On Sat, 13 Dec 2003 14:04:36 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:

谢谢。这对我不起作用，但我很感激回复。我已经创建了第二个表，我确实有循环和插入的代码。但正如我所提到的，我展示的例子不是真正的表格，只是比我面临的真正问题更容易理解的东西。我有一个63列的表格，其中包含50多列数据，我想把它变成一个7列的表格。
这意味着我有5个关键字段，一个存储量和一个字段
包含旧字段的名称（我在简化的示例中显示的月份名称）。

因为有超过50个字段，并且它们具有不常见的名称（
共同基金的名字，我需要一种方法来实际获得他们的名字。我曾经能够在Paradox for DOS中做到这一点，所以它必须在Access中可行，尽管Access 2000圣经没有告诉我如何（至少我没能够
UN Me <未** @ together.com>在消息中写道
新闻：3F *************** @ together.com ...

Thanks. THis will not work for me, but I do appreciate the reply. I have
created a 2nd table and I do have code to loop and insert. But as I
mentioned, the example I showed was not the real table, just something
easier to understand than the real problem I am facing. I have a 63-column
table with 50+ columns of data that I want to turn into a 7-column table.
That means I have 5 key fields, one filed with the amount and one field that
contains the name of the old field (the name of the month in the simplified
example I showed).

Since there are over 50 fields and they have uncommon names (the names of
mutual funds), I need a way to actually get their names. I used to be able
to do this in Paradox for DOS, so it must be doable in Access, though the
Access 2000 Bible does not show me how (at least I have not been able to
find it).
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...

Colleyville Alan写道：

Colleyville Alan wrote:

>我试图把一个短而胖的（63列）表变成一个istall>和瘦（7列）。基本上，我正在尝试创建一个反向
>交叉"在VBA中使用循环结构和SQL。我想要接受>列的名称并将其输入到descritor字段。
>
>这不是表格，但将作为一个比其他更好的例证>交易。
>
>如果表格如下：
>
>推销员Jan Feb Mar
>安德森410 747 142
> Jones 64 981 828
>史密斯837 583 561
>
>但我想要这个：
>
>推销员月金额
> Smith Jan 837
> Jones Jan 64
> Anderson Jan 410
> Smith 2月583
>琼斯2月981
>安德森2月747
> Smith Mar 561
> Jones Mar 828
> Anderson Mar 142
>
>如何使用旧列的名称作为值创建新列？ >我将列名称分配给变量，如下所示：" [" &
> tdf.Fields（fld）.Name& "]"
>
>谢谢。

> I am trying to turn a short and fat (63 columns) table into one that istall > and skinny (7 columns). Basically, I am trying to create a "reverse
> crosstab" using a looping structure in VBA along with SQL. I''d like totake > the name of the column and input it into a descritor field.
>
> This isn''t the table, but will serve as a better illustration than thereal > deal.
>
> If the table looks like this:
>
> Salesman Jan Feb Mar
> Anderson 410 747 142
> Jones 64 981 828
> Smith 837 583 561
>
> But I want this:
>
> Salesman Month Amount
> Smith Jan 837
> Jones Jan 64
> Anderson Jan 410
> Smith Feb 583
> Jones Feb 981
> Anderson Feb 747
> Smith Mar 561
> Jones Mar 828
> Anderson Mar 142
>
> How can I create a new column with the name of the old column as thevalue? > I am assigning the column name to a variable as follows: "[" &
> tdf.Fields(fld).Name & "]"
>
> Thanks.

我只想创建第二个表。然后使用追加查询。

我创建了Table1，其中包含Name和Jan ... Dec.

然后我创建了Table2，其中包含Name，MonthCol和Amt字段。

然后我运行了这段代码。它从月份（12次）循环，从1月到

I would simply create the second table. Then use an append query.

I created Table1 with the fields Name and Jan...Dec.

I then created Table2 with the fields Name, MonthCol, and Amt.

I then ran this code. It loops thru the months (12 times), from Jan to

12月，

Dec,

每次追加特定列的记录。我转向警告

each time appending records for the specific column. I turned warning to

false

false

因为我不想看到我每次都附加记录

because I didn''t want to see that I was appending the records each time

下一个

循环的迭代被执行。

Sub AppendIt（）
Dim strSQL As String
Dim strMon As String
Dim intFor As Integer
DoCmd.SetWarnings False
对于intFor = 1到12
strMon = Format（DateSerial（Year（Date），intFor，1），mmm）
strSQL =" INSERT INTO Table2（Name，MonthCol，Amt）" &安培; _
" SELECT Table1.Name，" &安培; strMon& ，表1。 &安培; strMon& "

iteration of the loop was performed.

Sub AppendIt()
Dim strSQL As String
Dim strMon As String
Dim intFor As Integer
DoCmd.SetWarnings False
For intFor = 1 To 12
strMon = Format(DateSerial(Year(Date), intFor, 1), "mmm")
strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
"SELECT Table1.Name, " & strMon & ", Table1." & strMon & "

来自

Table1"
CurrentDb.Execute strSQL
下一页
DoCmd.SetWarnings True
MsgBox" Done" ;

End Sub

Table1"
CurrentDb.Execute strSQL
Next
DoCmd.SetWarnings True
MsgBox "Done"

End Sub

这篇关于另一个SQL问题 - 使用列名更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！