By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Another SQL Question - Update Value with Column Name

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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.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

Nov 12 '05 #2

P: n/a
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 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.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

Nov 12 '05 #3

P: n/a
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:
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 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.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


Nov 12 '05 #4

P: n/a
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.com> wrote in message
news:jd********************************@4ax.com...
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:
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***************@together.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.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

Nov 12 '05 #5

P: n/a
-----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.openrecordset(SQL_FIELDS)

dim i as integer
dim strSQL as string
for i = 6 to 63 ' ordinal positions of "variable" columns
strSQL = Replace(SQL_XFER, "|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 programmatically 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP9ubd4echKqOuFEgEQJBdgCg2CXWhjrY4Oat9AnfeS49TO jXQ6gAoKlY
1IOnmMrFGN4JiC89pRfmdkOJ
=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

P: n/a
Thanks - I will give it a shot.
"MGFoster" <me@privacy.com> wrote in message
news:7_*****************@newsread1.news.pas.earthl ink.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.openrecordset(SQL_FIELDS)

dim i as integer
dim strSQL as string
for i = 6 to 63 ' ordinal positions of "variable" columns
strSQL = Replace(SQL_XFER, "|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 programmatically 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP9ubd4echKqOuFEgEQJBdgCg2CXWhjrY4Oat9AnfeS49TO jXQ6gAoKlY
1IOnmMrFGN4JiC89pRfmdkOJ
=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

P: n/a
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_*****************@newsread1.news.pas.earthl ink.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.openrecordset(SQL_FIELDS)

dim i as integer
dim strSQL as string
for i = 6 to 63 ' ordinal positions of "variable" columns
strSQL = Replace(SQL_XFER, "|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 programmatically 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP9ubd4echKqOuFEgEQJBdgCg2CXWhjrY4Oat9AnfeS49TO jXQ6gAoKlY
1IOnmMrFGN4JiC89pRfmdkOJ
=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

P: n/a
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...especially 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

P: n/a
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.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

P: n/a
Colleyville Alan wrote:
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.


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.
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.