Another SQL Question - Update Value with Column Name

**U N Me** · Nov 12 '05, 05:42 PM

Re: Another SQL Question - Update Value with Column Name

Colleyville Alan wrote:
[color=blue]
> 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.[/color]

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.SetWarnin gs False
For intFor = 1 To 12
strMon = Format(DateSeri al(Year(Date), intFor, 1), "mmm")
strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
"SELECT Table1.Name, " & strMon & ", Table1." & strMon & " From
Table1"
CurrentDb.Execu te strSQL
Next
DoCmd.SetWarnin gs True
MsgBox "Done"

End Sub

**Colleyville Alan** · Nov 12 '05, 05:42 PM

Re: Another SQL Question - Update Value with Column Name

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" <unme@together. com> wrote in message
news:3FDAE6C6.1 E16892A@togethe r.com...[color=blue]
> Colleyville Alan wrote:
>[color=green]
> > I am trying to turn a short and fat (63 columns) table into one that is[/color][/color]
tall[color=blue][color=green]
> > 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[/color][/color]
take[color=blue][color=green]
> > 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[/color][/color]
real[color=blue][color=green]
> > 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[/color][/color]
value?[color=blue][color=green]
> > I am assigning the column name to a variable as follows: "[" &
> > tdf.Fields(fld) .Name & "]"
> >
> > Thanks.[/color]
>
> 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[/color]
Dec,[color=blue]
> each time appending records for the specific column. I turned warning to[/color]
false[color=blue]
> because I didn't want to see that I was appending the records each time[/color]
the next[color=blue]
> iteration of the loop was performed.
>
> Sub AppendIt()
> Dim strSQL As String
> Dim strMon As String
> Dim intFor As Integer
> DoCmd.SetWarnin gs False
> For intFor = 1 To 12
> strMon = Format(DateSeri al(Year(Date), intFor, 1), "mmm")
> strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
> "SELECT Table1.Name, " & strMon & ", Table1." & strMon & "[/color]
From[color=blue]
> Table1"
> CurrentDb.Execu te strSQL
> Next
> DoCmd.SetWarnin gs True
> MsgBox "Done"
>
> End Sub
>
>
>[/color]

**Matthew Sullivan** · Nov 12 '05, 05:43 PM

Re: Another SQL Question - Update Value with Column Name

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.Table Defs("FatTableN ame")

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, "Colleyvill e Alan"
<aecharbonneau@ nospam.comcast. net> wrote:
[color=blue]
>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" <unme@together. com> wrote in message
>news:3FDAE6C6. 1E16892A@togeth er.com...[color=green]
>> Colleyville Alan wrote:
>>[color=darkred]
>> > I am trying to turn a short and fat (63 columns) table into one that is[/color][/color]
>tall[color=green][color=darkred]
>> > 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[/color][/color]
>take[color=green][color=darkred]
>> > 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[/color][/color]
>real[color=green][color=darkred]
>> > 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[/color][/color]
>value?[color=green][color=darkred]
>> > I am assigning the column name to a variable as follows: "[" &
>> > tdf.Fields(fld) .Name & "]"
>> >
>> > Thanks.[/color]
>>
>> 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[/color]
>Dec,[color=green]
>> each time appending records for the specific column. I turned warning to[/color]
>false[color=green]
>> because I didn't want to see that I was appending the records each time[/color]
>the next[color=green]
>> iteration of the loop was performed.
>>
>> Sub AppendIt()
>> Dim strSQL As String
>> Dim strMon As String
>> Dim intFor As Integer
>> DoCmd.SetWarnin gs False
>> For intFor = 1 To 12
>> strMon = Format(DateSeri al(Year(Date), intFor, 1), "mmm")
>> strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
>> "SELECT Table1.Name, " & strMon & ", Table1." & strMon & "[/color]
>From[color=green]
>> Table1"
>> CurrentDb.Execu te strSQL
>> Next
>> DoCmd.SetWarnin gs True
>> MsgBox "Done"
>>
>> End Sub
>>
>>
>>[/color]
>[/color]

**Colleyville Alan** · Nov 12 '05, 05:43 PM

Re: Another SQL Question - Update Value with Column Name

Thanks, but I know how to *get* the field names, what I need to know is,
using SQL, what command I use to take that field name and have it appear as
a value in the table to which I am appending. Going back to my example,
[color=blue][color=green][color=darkred]
> >> > 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[/color][/color][/color]

I can get the name of the field (e.g. Jan, Feb, Mar...), but how do I
structure the query so that the table to which I am appending has "Jan" for
the first 3 records, "Feb" for the next 3 and so forth?. Do I need to have
a separate SQL statement, an "Update" statement in the loop?

"Matthew Sullivan" <Matt@NoSpam.co m> wrote in message
news:jd1ntvg301 53l2i0jcpfqmt5u lrc74obu9@4ax.c om...[color=blue]
> 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.Table Defs("FatTableN ame")
>
> 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, "Colleyvill e Alan"
> <aecharbonneau@ nospam.comcast. net> wrote:
>[color=green]
> >Thanks. THis will not work for me, but I do appreciate the reply. I[/color][/color]
have[color=blue][color=green]
> >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[/color][/color]
63-column[color=blue][color=green]
> >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[/color][/color]
that[color=blue][color=green]
> >contains the name of the old field (the name of the month in the[/color][/color]
simplified[color=blue][color=green]
> >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[/color][/color]
able[color=blue][color=green]
> >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" <unme@together. com> wrote in message
> >news:3FDAE6C6. 1E16892A@togeth er.com...[color=darkred]
> >> Colleyville Alan wrote:
> >>
> >> > I am trying to turn a short and fat (63 columns) table into one that[/color][/color][/color]
is[color=blue][color=green]
> >tall[color=darkred]
> >> > 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[/color][/color][/color]
to[color=blue][color=green]
> >take[color=darkred]
> >> > 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[/color][/color][/color]
the[color=blue][color=green]
> >real[color=darkred]
> >> > 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[/color]
> >value?[color=darkred]
> >> > 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[/color]
> >Dec,[color=darkred]
> >> each time appending records for the specific column. I turned warning[/color][/color][/color]
to[color=blue][color=green]
> >false[color=darkred]
> >> because I didn't want to see that I was appending the records each time[/color]
> >the next[color=darkred]
> >> iteration of the loop was performed.
> >>
> >> Sub AppendIt()
> >> Dim strSQL As String
> >> Dim strMon As String
> >> Dim intFor As Integer
> >> DoCmd.SetWarnin gs False
> >> For intFor = 1 To 12
> >> strMon = Format(DateSeri al(Year(Date), intFor, 1), "mmm")
> >> strSQL = "INSERT INTO Table2 ( Name, MonthCol, Amt ) " & _
> >> "SELECT Table1.Name, " & strMon & ", Table1." & strMon & "[/color]
> >From[color=darkred]
> >> Table1"
> >> CurrentDb.Execu te strSQL
> >> Next
> >> DoCmd.SetWarnin gs True
> >> MsgBox "Done"
> >>
> >> End Sub
> >>
> >>
> >>[/color]
> >[/color]
>[/color]

**MGFoster** · Nov 12 '05, 05:43 PM

Re: Another SQL Question - Update Value with Column Name

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd create the 7-column table using the table design view. Then I'd
fill the table using a VBA loop like this:

== begin air code ==

' Query to get data from original table - 1 column at-a-time
' Change field1-5 to real column names.
' The |1 will be replaced w/ a 'string' that represents
' the column name of the orig. table.
' The second |1 will be replaced w/ the column name of the orig.
table.

Const SQL_XFER = "INSERT INTO NewTable " & _
"SELECT field1, field2, field3, field4, " & _
"field5, '|1', [|1] " & _
"FROM OrigTable"

' Query to return only metadata of the original table
Const SQL_FIELDS = "SELECT * FROM OrigTable WHERE FALSE

' Get metadata
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordse t(SQL_FIELDS)

dim i as integer
dim strSQL as string
for i = 6 to 63 ' ordinal positions of "variable" columns
strSQL = Replace(SQL_XFE R, "|1", rs(i).Name)
db.execute strSQL, dbFailOnError
next i

== end air code ==

You'll want to put in error traps.

The idea is to programmaticall y change the INSERT INTO query for each
column that you are transferring from. In the For...Next loop the
columns 6 thru 63 of the original table will be read (I'm assuming
these are the "mutual funds" columns). The append query will change
like this (just a rough sketch - for columns 6 and 7 in the original
table - if columns 6 & 7 were named [First Fidelity] and [Last Bank of
Nashville]):

INSERT INTO NewTable
SELECT field1, field2, field3, field4, field5,
'First Fidelity', [First Fidelity]
FROM MyTable

'First Fidelity' will put the string "First Fidelity" in the New Table
in column 6. The Value of the original table's column [First
Fidelity] will be put in column 7.

INSERT INTO NewTable
SELECT field1, field2, field3, field4, field5,
'Last Bank of Nashville', [Last Bank of Nashville]
FROM MyTable

.... etc. ...

HTH,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9ubd4echKq OuFEgEQJBdgCg2C XWhjrY4Oat9Anfe S49TOjXQ6gAoKlY
1IOnmMrFGN4JiC8 9pRfmdkOJ
=Q4MJ
-----END PGP SIGNATURE-----

Colleyville Alan wrote:[color=blue]
> 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).
>[/color]

**Colleyville Alan** · Nov 12 '05, 05:43 PM

Re: Another SQL Question - Update Value with Column Name

Thanks - I will give it a shot.

"MGFoster" <me@privacy.com > wrote in message
news:7_MCb.3053 $Pg1.1766@newsr ead1.news.pas.e arthlink.net...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I'd create the 7-column table using the table design view. Then I'd
> fill the table using a VBA loop like this:
>
> == begin air code ==
>
> ' Query to get data from original table - 1 column at-a-time
> ' Change field1-5 to real column names.
> ' The |1 will be replaced w/ a 'string' that represents
> ' the column name of the orig. table.
> ' The second |1 will be replaced w/ the column name of the orig.
> table.
>
> Const SQL_XFER = "INSERT INTO NewTable " & _
> "SELECT field1, field2, field3, field4, " & _
> "field5, '|1', [|1] " & _
> "FROM OrigTable"
>
> ' Query to return only metadata of the original table
> Const SQL_FIELDS = "SELECT * FROM OrigTable WHERE FALSE
>
> ' Get metadata
> dim db as dao.database
> dim rs as dao.recordset
> set db = currentdb
> set rs = db.openrecordse t(SQL_FIELDS)
>
> dim i as integer
> dim strSQL as string
> for i = 6 to 63 ' ordinal positions of "variable" columns
> strSQL = Replace(SQL_XFE R, "|1", rs(i).Name)
> db.execute strSQL, dbFailOnError
> next i
>
> == end air code ==
>
> You'll want to put in error traps.
>
> The idea is to programmaticall y change the INSERT INTO query for each
> column that you are transferring from. In the For...Next loop the
> columns 6 thru 63 of the original table will be read (I'm assuming
> these are the "mutual funds" columns). The append query will change
> like this (just a rough sketch - for columns 6 and 7 in the original
> table - if columns 6 & 7 were named [First Fidelity] and [Last Bank of
> Nashville]):
>
> INSERT INTO NewTable
> SELECT field1, field2, field3, field4, field5,
> 'First Fidelity', [First Fidelity]
> FROM MyTable
>
> 'First Fidelity' will put the string "First Fidelity" in the New Table
> in column 6. The Value of the original table's column [First
> Fidelity] will be put in column 7.
>
>
> INSERT INTO NewTable
> SELECT field1, field2, field3, field4, field5,
> 'Last Bank of Nashville', [Last Bank of Nashville]
> FROM MyTable
>
> ... etc. ...
>
> HTH,
>
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBP9ubd4echKq OuFEgEQJBdgCg2C XWhjrY4Oat9Anfe S49TOjXQ6gAoKlY
> 1IOnmMrFGN4JiC8 9pRfmdkOJ
> =Q4MJ
> -----END PGP SIGNATURE-----
>
>
> Colleyville Alan wrote:[color=green]
> > Thanks. THis will not work for me, but I do appreciate the reply. I[/color][/color]
have[color=blue][color=green]
> > 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[/color][/color]
63-column[color=blue][color=green]
> > table with 50+ columns of data that I want to turn into a 7-column[/color][/color]
table.[color=blue][color=green]
> > That means I have 5 key fields, one filed with the amount and one field[/color][/color]
that[color=blue][color=green]
> > contains the name of the old field (the name of the month in the[/color][/color]
simplified[color=blue][color=green]
> > example I showed).
> >
> > Since there are over 50 fields and they have uncommon names (the names[/color][/color]
of[color=blue][color=green]
> > mutual funds), I need a way to actually get their names. I used to be[/color][/color]
able[color=blue][color=green]
> > to do this in Paradox for DOS, so it must be doable in Access, though[/color][/color]
the[color=blue][color=green]
> > Access 2000 Bible does not show me how (at least I have not been able to
> > find it).
> >[/color]
>[/color]

**Colleyville Alan** · Nov 12 '05, 05:44 PM

Finally got it to work!

My final solution to the problem:

For fld = 5 To 62
strMySql = "INSERT INTO Temp ( [FLD 1], [FLD 2], [FLD 3], "
strMySql = strMySql & "[FLD 4], [FLD 5], [FLD 6], [FLD 7] ) "
strMySql = strMySql & "Select Accts.[FLD 1], Accts.[FLD 2],
Accts.[FLD 3],"
strMySql = strMySql & "Accts.[FLD 4], Accts.[FLD 5], "
strMySql = strMySql & """" & tdf.Fields(fld) .Name & ""","
strMySql = strMySql & " [" & tdf.Fields(fld) .Name & "]"
strMySql = strMySql & " FROM Accts WHERE [" &
tdf.Fields(fld) .Name & "] "
strMySql = strMySql & " > 0"
DoCmd.RunSQL (strMySql)
Next fld

Getting the right number of quotation marks was the biggest challenge.
I do not know if there is a rule of thumb to follow other than the
"pound-away-for-10-hours-and-swear-at-the-screen-as-you-try-every-combinatio
n-you-can-think-of-and-then-save-extra-copies-everywhere-if-it-works"
method. In fact, it would not surprise me if I had too many double quotes
in there (4 double quotes together seems a bit much). But at long last, this
does exactly what I want.

Thanks to all who replied to this and to my other thread, it has been
most helpful.

"MGFoster" <me@privacy.com > wrote in message
news:7_MCb.3053 $Pg1.1766@newsr ead1.news.pas.e arthlink.net...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I'd create the 7-column table using the table design view. Then I'd
> fill the table using a VBA loop like this:
>
> == begin air code ==
>
> ' Query to get data from original table - 1 column at-a-time
> ' Change field1-5 to real column names.
> ' The |1 will be replaced w/ a 'string' that represents
> ' the column name of the orig. table.
> ' The second |1 will be replaced w/ the column name of the orig.
> table.
>
> Const SQL_XFER = "INSERT INTO NewTable " & _
> "SELECT field1, field2, field3, field4, " & _
> "field5, '|1', [|1] " & _
> "FROM OrigTable"
>
> ' Query to return only metadata of the original table
> Const SQL_FIELDS = "SELECT * FROM OrigTable WHERE FALSE
>
> ' Get metadata
> dim db as dao.database
> dim rs as dao.recordset
> set db = currentdb
> set rs = db.openrecordse t(SQL_FIELDS)
>
> dim i as integer
> dim strSQL as string
> for i = 6 to 63 ' ordinal positions of "variable" columns
> strSQL = Replace(SQL_XFE R, "|1", rs(i).Name)
> db.execute strSQL, dbFailOnError
> next i
>
> == end air code ==
>
> You'll want to put in error traps.
>
> The idea is to programmaticall y change the INSERT INTO query for each
> column that you are transferring from. In the For...Next loop the
> columns 6 thru 63 of the original table will be read (I'm assuming
> these are the "mutual funds" columns). The append query will change
> like this (just a rough sketch - for columns 6 and 7 in the original
> table - if columns 6 & 7 were named [First Fidelity] and [Last Bank of
> Nashville]):
>
> INSERT INTO NewTable
> SELECT field1, field2, field3, field4, field5,
> 'First Fidelity', [First Fidelity]
> FROM MyTable
>
> 'First Fidelity' will put the string "First Fidelity" in the New Table
> in column 6. The Value of the original table's column [First
> Fidelity] will be put in column 7.
>
>
> INSERT INTO NewTable
> SELECT field1, field2, field3, field4, field5,
> 'Last Bank of Nashville', [Last Bank of Nashville]
> FROM MyTable
>
> ... etc. ...
>
> HTH,
>
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBP9ubd4echKq OuFEgEQJBdgCg2C XWhjrY4Oat9Anfe S49TOjXQ6gAoKlY
> 1IOnmMrFGN4JiC8 9pRfmdkOJ
> =Q4MJ
> -----END PGP SIGNATURE-----
>
>
> Colleyville Alan wrote:[color=green]
> > Thanks. THis will not work for me, but I do appreciate the reply. I[/color][/color]
have[color=blue][color=green]
> > 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[/color][/color]
63-column[color=blue][color=green]
> > table with 50+ columns of data that I want to turn into a 7-column[/color][/color]
table.[color=blue][color=green]
> > That means I have 5 key fields, one filed with the amount and one field[/color][/color]
that[color=blue][color=green]
> > contains the name of the old field (the name of the month in the[/color][/color]
simplified[color=blue][color=green]
> > example I showed).
> >
> > Since there are over 50 fields and they have uncommon names (the names[/color][/color]
of[color=blue][color=green]
> > mutual funds), I need a way to actually get their names. I used to be[/color][/color]
able[color=blue][color=green]
> > to do this in Paradox for DOS, so it must be doable in Access, though[/color][/color]
the[color=blue][color=green]
> > Access 2000 Bible does not show me how (at least I have not been able to
> > find it).
> >[/color]
>[/color]

**U N Me** · Nov 12 '05, 05:44 PM

Re: Another SQL Question - Update Value with Column Name

Colleyville Alan wrote:
[color=blue]
> Thanks, but I know how to *get* the field names, what I need to know is,
> using SQL, what command I use to take that field name and have it appear as
> a value in the table to which I am appending. Going back to my example,
>[color=green][color=darkred]
> > >> > 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[/color][/color]
>
> I can get the name of the field (e.g. Jan, Feb, Mar...), but how do I
> structure the query so that the table to which I am appending has "Jan" for
> the first 3 records, "Feb" for the next 3 and so forth?. Do I need to have
> a separate SQL statement, an "Update" statement in the loop?[/color]

I'll assume you have heard of Sorting.

I've asked questions before where the reasoning why is irrelevent to the
question but people want to know why. In my world, I'd simply assume I could
query it and sort it on the fly. So I'm sure I don;t know why you want to do it
your way.

By the way, in order to property sort, I'd store the month number (1-12) instead
of the 3 char abbrev you prefer. The reason is that with the DateSerial command
and format() function (seen in my code), you can display the month easily and it
will sort correctly. At best, with your scheme, you will have to fiddle and
fart to come up with a decent sort method...especi ally if you add one more
record to a sequential file. Then if you create a report, it will sort
strangely so again you have a fiddle and fart.

Just my $.02

**Colleyville Alan** · Nov 12 '05, 05:44 PM

Re: Another SQL Question - Update Value with Column Name

"U N Me" <unme@together. com> wrote in message
news:3FDBAF07.1 783FEFD@togethe r.com...[color=blue]
> Colleyville Alan wrote:
>[color=green]
> > Thanks, but I know how to *get* the field names, what I need to know is,
> > using SQL, what command I use to take that field name and have it appear[/color][/color]
as[color=blue][color=green]
> > a value in the table to which I am appending. Going back to my example,
> >[color=darkred]
> > > >> > 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[/color]
> >
> > I can get the name of the field (e.g. Jan, Feb, Mar...), but how do I
> > structure the query so that the table to which I am appending has "Jan"[/color][/color]
for[color=blue][color=green]
> > the first 3 records, "Feb" for the next 3 and so forth?. Do I need to[/color][/color]
have[color=blue][color=green]
> > a separate SQL statement, an "Update" statement in the loop?[/color]
>
> I'll assume you have heard of Sorting.[/color]

Yes
[color=blue]
> I've asked questions before where the reasoning why is irrelevent to the
> question but people want to know why. In my world, I'd simply assume I[/color]
could[color=blue]
> query it and sort it on the fly. So I'm sure I don;t know why you want to[/color]
do it[color=blue]
> your way.[/color]

I showed the sales by month as an easy-to-understand example of what I was
trying to achieve - i.e. turning a short, fat table into a long, skinny one.
The real application had a table with 63 columns of data; there were 5 key
fields and 58 data fields. I wanted to end up with 7 fields, the 5 key
fields, one amount field and one field that contained the name of the field
from whence the amount came. In the real application, the 58 columns
represented the names of mutual funds and the values in those columns
represented the amounts invested in each. I used the sales-by-month as an
example with which people might be familiar - a typical spreadsheet layout
turned into an Access table.

**U N Me** · Nov 12 '05, 05:46 PM

Re: Another SQL Question - Update Value with Column Name

Colleyville Alan wrote:
[color=blue][color=green]
> > I've asked questions before where the reasoning why is irrelevent to the
> > question but people want to know why. In my world, I'd simply assume I[/color]
> could[color=green]
> > query it and sort it on the fly. So I'm sure I don;t know why you want to[/color]
> do it[color=green]
> > your way.[/color]
>
> I showed the sales by month as an easy-to-understand example of what I was
> trying to achieve - i.e. turning a short, fat table into a long, skinny one.
> The real application had a table with 63 columns of data; there were 5 key
> fields and 58 data fields. I wanted to end up with 7 fields, the 5 key
> fields, one amount field and one field that contained the name of the field
> from whence the amount came. In the real application, the 58 columns
> represented the names of mutual funds and the values in those columns
> represented the amounts invested in each. I used the sales-by-month as an
> example with which people might be familiar - a typical spreadsheet layout
> turned into an Access table.[/color]

You can have a sequential table but once you add a record (sorted) that is out
of sequence your scheme falls apart. If you won't be adding records and your
reports don't need sorts, your scheme may work.

What I was also getting at, using 1-12 instead of Jan-Dec, is that if you use
Mon as a sort, you will end up with Apr, Aug, Feb, Jan, Jul, Jun...order in a
report..

I guess I've gotten used to the fact I don't really care how records are brough
into my database since I never assume I'll see the records in sequential order
unless I have no sort and have an autonumber field. But in the grand scheme of
programming, there are many ways to accomplish a similar task. Good luck.

Another SQL Question - Update Value with Column Name

Another SQL Question - Update Value with Column Name

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment