473,881 Members | 1,759 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Another SQL Question - Update Value with Column 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.
Nov 12 '05 #1
10 6573
Colleyville Alan wrote:
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,
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

Nov 12 '05 #2
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******** *******@togethe r.com...
Colleyville Alan wrote:
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, 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

Nov 12 '05 #3
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"
<ae***********@ nospam.comcast. net> wrote:
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******* ********@togeth er.com...
Colleyville Alan wrote:
> 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.


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


Nov 12 '05 #4
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,
> 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

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" <Ma**@NoSpam.co m> wrote in message
news:jd******** *************** *********@4ax.c om...
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"
<ae***********@ nospam.comcast. net> wrote:
Thanks. THis will not work for me, but I do appreciate the reply. I

havecreated 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-columntable 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 thatcontains the name of the old field (the name of the month in the simplifiedexample 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 ableto 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******* ********@togeth er.com...
Colleyville Alan wrote:

> 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 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,
each time appending records for the specific column. I turned warning
tofalse
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

Nov 12 '05 #5
-----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:
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).


Nov 12 '05 #6
Thanks - I will give it a shot.
"MGFoster" <me@privacy.com > wrote in message
news:7_******** *********@newsr ead1.news.pas.e arthlink.net...
-----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:
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).

Nov 12 '05 #7
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_******** *********@newsr ead1.news.pas.e arthlink.net...
-----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:
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).

Nov 12 '05 #8
Colleyville Alan wrote:
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,
> > 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


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?


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
Nov 12 '05 #9
"U N Me" <un**@together. com> wrote in message
news:3F******** *******@togethe r.com...
Colleyville Alan wrote:
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,
>> > 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
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?


I'll assume you have heard of Sorting.


Yes
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.


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.



Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
248505
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a particular surname in a particular town. I can select the records fine with this syntax (testing in Oracle SQL* Plus) SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME, NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO...
8
89328
by: Lauren Quantrell | last post by:
In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID = tblMyTableTEMP.UniqueID SET tblMyTable.myField = tblMyTableTEMP.myField, tblMyTable.myField2 = tblMyTableTEMP.myField2,
6
6181
by: cyoung311 | last post by:
I'm trying to do an automatic update of one table from another through a form. Basically, when a selection is made for an item, in this case a particular workshop, I want to get the associated price of that workshop from the Workshop table and update my ScheduleDetails table. The tables are as follows: tblStudent StudentID LastName FirstName
2
9479
by: kmnotes04 | last post by:
Is it possible to link one drop-down box to another? For example, if a name is chosen from a drop-down list, can another drop-down list then automatically display the person's office as a result of the choice made in the first drop-down list? If so, how can that be done? I am working with 'Teach Yourself Microsoft Access' and I didn't see it discussed in that book (if it's even possible to do). Or would I just have to create linked tables...
3
2753
by: windandwaves | last post by:
Hi Gurus Does anyone know how I set the error trapping to option 2 in visual basic. I know that you can go to tools, options and then choose on unhandled errors only, but is there a VB command that I can use instead? Cheers Nicolaas
8
2704
by: Zorpiedoman | last post by:
I keep getting a concurrency exception the second time I make a change and attempt to update a dataadapter. It appears this is by design, so there must be something I can do to avoid it. Example: I have a dataadapter that contains one table with one row. I change the value of the 'FisrtName' column in that row from Jack to John. I call ..update on the dataadapter it goes through fine. Now I change that same column in that same row...
7
6124
by: JJ | last post by:
How do I set one field to have the updated timestamp, and another to have the created timestamp? I want to do this directly from code generated from DB Designer if possible?! JJ
4
7259
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which - "Modified" of type DateTime - is hidden since it should not be edited by a user. The system handles the update for this column. So, I have hidden (Visible=false) this column on the grid. In order to access the value in this field, I have created a...
22
3615
by: tonialbrown | last post by:
I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom. This is working for all the text fields & updates fine. Once I add in one of the fields that is a Date field I cannot get the update to work. ie) this line ". = #" & Nz(rst.Fields(9).Value, "") & "#," & _ Could someone assist me with the correct syntax to use. I also need to consider that the field may be...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10716
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.