How2 move 1 field from 97 recrds to create 1 table with 97 fields FunInProgramming 05.02.2006 17:16:28 I have table with 97 records containing data that I wish to use to populate another table with the first record containing all 97 fields from the other table across in 97 fields. There must be some sort of loop reading the first table (all 97 records) and then populating one record in the second table with 97 fields. How do I do that?

Re: How2 move 1 field from 97 recrds to create 1 table with 97 fields "tina" <nospam[ at ]address.com> 05.02.2006 19:26:26 if you're trying to set up data from a normalized table to display it in a non-normalized format, suggest you take a look at Crosstab queries to see if you can use one to achieve your goal, instead of creating a non-normalized table. hth "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in message news:C11C859B-7B22-4358-830D-CADC2FCE3862[ at ]microsoft.com... [Quoted Text] > I have table with 97 records containing data that I wish to use to populate > another table with the first record containing all 97 fields from the other > table across in 97 fields. There must be some sort of loop reading the first > table (all 97 records) and then populating one record in the second table > with 97 fields. How do I do that? > >

Re: How2 move 1 field from 97 recrds to create 1 table with 97 fie FunInProgramming 05.02.2006 22:22:27 This does not solve my problem since I am trying to convert one field in 197 rows to 1 row with 197 fields. I have just tried crosstab and it gives me an error message stating that the one record that is being "crosstabed" needs to have at least 3 fields. It currently only has 2. Any other suggestions? Thanks. "tina" wrote: [Quoted Text] > if you're trying to set up data from a normalized table to display it in a > non-normalized format, suggest you take a look at Crosstab queries to see if > you can use one to achieve your goal, instead of creating a non-normalized > table. > > hth > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > message news:C11C859B-7B22-4358-830D-CADC2FCE3862[ at ]microsoft.com... > > I have table with 97 records containing data that I wish to use to > populate > > another table with the first record containing all 97 fields from the > other > > table across in 97 fields. There must be some sort of loop reading the > first > > table (all 97 records) and then populating one record in the second table > > with 97 fields. How do I do that? > > > > > > >

Re: How2 move 1 field from 97 recrds to create 1 table with 97 fie "tina" <nospam[ at ]address.com> 06.02.2006 00:30:32 if you can tell us what you're trying to achieve with the conversion, maybe we can come up with a solution that will suit your needs. hth "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in message news:F38AA2FE-78E3-4FD0-A785-E12C729EF6D9[ at ]microsoft.com... [Quoted Text] > This does not solve my problem since I am trying to convert one field in 197 > rows to 1 row with 197 fields. I have just tried crosstab and it gives me an > error message stating that the one record that is being "crosstabed" needs to > have at least 3 fields. It currently only has 2. Any other suggestions? > Thanks. > > > "tina" wrote: > > > if you're trying to set up data from a normalized table to display it in a > > non-normalized format, suggest you take a look at Crosstab queries to see if > > you can use one to achieve your goal, instead of creating a non-normalized > > table. > > > > hth > > > > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > > message news:C11C859B-7B22-4358-830D-CADC2FCE3862[ at ]microsoft.com... > > > I have table with 97 records containing data that I wish to use to > > populate > > > another table with the first record containing all 97 fields from the > > other > > > table across in 97 fields. There must be some sort of loop reading the > > first > > > table (all 97 records) and then populating one record in the second table > > > with 97 fields. How do I do that? > > > > > > > > > > > >

Re: How2 move 1 field from 97 recrds to create 1 table with 97 fie FunInProgramming 06.02.2006 01:20:27 I have one simple TABLE_1 with 2 columns and 197 rows. The first column is auto-generated index (key) from 1 to 197. The second column, SECTOR_NAME contains text which I would like to use as field names in TABLE_2 that will have 198 columns consisting of the 197 "sector_name"s plus one column used as a unique key containing a date. The idea is that once each business day, I will be receiving an excel spreadsheet containing DATE, SECOTR_NAME, and third column, SECTOR_POSITION containing a numerical value from 1-197. The format will ALWAYS be the same. Always new business date and always the same 197 sectors. The only thing that changes on daily basis is the numerical value of the SECTOR_POSITION. I need to track the value of the SECTOR_POSITION as to moves up or down for each SECTOR_NAME on daily basis. "tina" wrote: [Quoted Text] > if you can tell us what you're trying to achieve with the conversion, maybe > we can come up with a solution that will suit your needs. > > hth > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > message news:F38AA2FE-78E3-4FD0-A785-E12C729EF6D9[ at ]microsoft.com... > > This does not solve my problem since I am trying to convert one field in > 197 > > rows to 1 row with 197 fields. I have just tried crosstab and it gives me > an > > error message stating that the one record that is being "crosstabed" needs > to > > have at least 3 fields. It currently only has 2. Any other suggestions? > > Thanks. > > > > > > "tina" wrote: > > > > > if you're trying to set up data from a normalized table to display it in > a > > > non-normalized format, suggest you take a look at Crosstab queries to > see if > > > you can use one to achieve your goal, instead of creating a > non-normalized > > > table. > > > > > > hth > > > > > > > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > > > message news:C11C859B-7B22-4358-830D-CADC2FCE3862[ at ]microsoft.com... > > > > I have table with 97 records containing data that I wish to use to > > > populate > > > > another table with the first record containing all 97 fields from the > > > other > > > > table across in 97 fields. There must be some sort of loop reading the > > > first > > > > table (all 97 records) and then populating one record in the second > table > > > > with 97 fields. How do I do that? > > > > > > > > > > > > > > > > > > > >

Re: How2 move 1 field from 97 recrds to create 1 table with 97 fie "tina" <nospam[ at ]address.com> 06.02.2006 01:58:56 since you're getting an Excel file that has three columns of data in a normalized format, suggest you simply dump the Excel file into an Access table with the same three columns (i'd change the DATE column name to something else in the Access table, though). set a combination primary key using the date and sector name fields. each day, dump the new spreadsheet data into *the same table*. since your test of a crosstab query told you that three columns are required, the three columns of data should work fine. i don't know if the Crosstab query will process 197 column headings, but i'd test it before going to all the trouble of creating a non-normalized table. hth "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in message news:A2BE452B-4636-4C74-8EB6-A41C5D602EA6[ at ]microsoft.com... [Quoted Text] > I have one simple TABLE_1 with 2 columns and 197 rows. The first column is > auto-generated index (key) from 1 to 197. The second column, SECTOR_NAME > contains text which I would like to use as field names in TABLE_2 that will > have 198 columns consisting of the 197 "sector_name"s plus one column used as > a unique key containing a date. The idea is that once each business day, I > will be receiving an excel spreadsheet containing DATE, SECOTR_NAME, and > third column, SECTOR_POSITION containing a numerical value from 1-197. The > format will ALWAYS be the same. Always new business date and always the same > 197 sectors. The only thing that changes on daily basis is the numerical > value of the SECTOR_POSITION. I need to track the value of the > SECTOR_POSITION as to moves up or down for each SECTOR_NAME on daily basis. > > > > "tina" wrote: > > > if you can tell us what you're trying to achieve with the conversion, maybe > > we can come up with a solution that will suit your needs. > > > > hth > > > > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > > message news:F38AA2FE-78E3-4FD0-A785-E12C729EF6D9[ at ]microsoft.com... > > > This does not solve my problem since I am trying to convert one field in > > 197 > > > rows to 1 row with 197 fields. I have just tried crosstab and it gives me > > an > > > error message stating that the one record that is being "crosstabed" needs > > to > > > have at least 3 fields. It currently only has 2. Any other suggestions? > > > Thanks. > > > > > > > > > "tina" wrote: > > > > > > > if you're trying to set up data from a normalized table to display it in > > a > > > > non-normalized format, suggest you take a look at Crosstab queries to > > see if > > > > you can use one to achieve your goal, instead of creating a > > non-normalized > > > > table. > > > > > > > > hth > > > > > > > > > > > > "FunInProgramming" <FunInProgramming[ at ]discussions.microsoft.com> wrote in > > > > message news:C11C859B-7B22-4358-830D-CADC2FCE3862[ at ]microsoft.com... > > > > > I have table with 97 records containing data that I wish to use to > > > > populate > > > > > another table with the first record containing all 97 fields from the > > > > other > > > > > table across in 97 fields. There must be some sort of loop reading the > > > > first > > > > > table (all 97 records) and then populating one record in the second > > table > > > > > with 97 fields. How do I do that? > > > > > > > > > > > > > > > > > > > > > > > > > > > >