Creating new table by combining fields from old

R

roy_ware

I am importing a comma delimintated file into an Access 2000 table called
"Import." Then I need to combine several fields from this table to create a
new table, "Sales Tracking," that the will be used to build forms & queries
for the end-user application.

Everything is in place except for an easy way to create this second table.
For the life of me, I just can't figure out how to do it automatically! Any
ideas?
 
R

Randy Balbuena

Roy_ware wrote:
..
I am importing a comma delimintated file into an Access 2000 table called
"Import." Then I need to combine several fields from this table to create
a
new table, "Sales Tracking," that the will be used to build forms &
queries
for the end-user application.

Everything is in place except for an easy way to create this second table.
For the life of me, I just can't figure out how to do it automatically!
Any
ideas?
Click to expand...

Roy,

You can create new tables based on the "Import" table by creating a
Make-Table query. Just create a query in Design View, select the table
"Import" from the list, and change the query type to "Make-Table query"
using the option "Query" from the system menu. You will be prompt to type
the name of the new table to be created.

Select all the fields you want on your new table by using the query design
grid. To create new fields, based on other fields, specify any of the
following instead of just the field name:

NewFieldName: [Fieldname4] & [Fieldname5]
NewFieldName: [Fieldname4] & [Fieldname5] & [Fieldname6]
NewFieldName: [Fieldname4] & " " & [Fieldname5]
NewFieldName: Left([Fieldname4], 2)

The 3rd example creates a new field combining two existing fields into one,
but separated by a space. The 4th example creates a new field using only the
first 2 characters of Fieldname4.

You can limit the number of records, by specifying a "criteria" like: "USA"
on a field "Country", or ">=#1/1/2000#" on a date field, "Is Null", etc. For
more details please visit:

http://office.microsoft.com/en-us/assistance/CH063653171033.aspx

Let us know if that helps.
 
J

John Vinson

I am importing a comma delimintated file into an Access 2000 table called
"Import." Then I need to combine several fields from this table to create a
new table, "Sales Tracking," that the will be used to build forms & queries
for the end-user application.

Everything is in place except for an easy way to create this second table.
For the life of me, I just can't figure out how to do it automatically! Any
ideas?
Click to expand...

Two ways come to mind:

- Create a MakeTable query based on [Import], selecting the fields as
needed. There is a Query menu option; MakeTable is one of the choices.
Or you can use the Query Type icon dropdown to select MakeTable query.

- Probably better, since MakeTable queries must make guesses about
field sizes and types: create your SalesTracking table (blanks are
undesirable in table or fieldnames), empty, with the desired field
sizes and types, and run an Append query to populate it.

John W. Vinson[MVP]
 
Top