Create one table based on another table.

[KJT]

This is a little more in-depth than the subject line alluded to. I may be in
over my head. I am trying to create a table (we’ll call this table 1) based
on another table (which we’ll call table 2). Table 2 consists of 7 fields.
Each field is set as TEXT. I want to combine all 7 fields into one 23
character field in table 1; this field will be set as a general number. Table
1 will be used to upload information to another database.

 

[KJT]

A General Number (Long Integer) is limited to two billion odd - far fewer than
23 characters. Your situation is still obscure.

Could you post an example of the seven fields, the nature of the concatenation
you want (an example of the desired result), and the nature of the database to
which you want to upload? I don't know what the exact solution will be, but
I'm willing to bet that creating a new table is NOT going to be necessary.

This is the layout of the two tables. Ultimately Table 1 will be uploaded into a preexisting Orical database and separated back into its six basic components. I hope this helps. This upload will take place on a monthly basis.

Table2
vehnumber vehmileage dayused daysinshop hoursused funccode
0205241 0006543 26 05 036
00

Table 1
veut
02052410006543260503600


Thanks

 

[John W. Vinson]

Table2
vehnumber vehmileage dayused daysinshop hoursused funccode
0205241 0006543 26 05 036
00

Table 1
veut
02052410006543260503600


Thanks

In that case you can upload a Query - it is NOT necessary to create a separate
table. In fact I'd suggest using Oracle's very capable ODBC connection to link
to the Oracle table, and simply run an Append query. The field should not be
any type of Number but rather a Text field. You can concatenate the seven
fields in a query by using the & operator. Assuming (based on the leading
zeroes) that they are Text fields, you can create a query:

SELECT vehnumber & vehmileage & dayused & daysinshop & hoursused & funccode
FROM Table2;

Save this query as qryConcat if you wish, or just change it to an Append query
into the linked Oracle table.

If - for some strange reason - you really need a separate local Table1, just
create Table1 with one 26 byte (or whatever) Text field, and change the above
query into an Append query. You can run a Delete query

DELETE * FROM Table1;

first if you want to empty it out. Using a prebuilt table will give you a
26-byte field rather than the default 255-byte field, and will be a lot more
efficient.