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. 10 6524
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
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
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
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
-----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).
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).
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).
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
"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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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
|
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 -...
|
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...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |