Importing from 2 seperate Excell tables with some common and uncommon columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I'm a relative beginner with Access. This question is quite similar to another I found here but with an important difference. I am trying to create a single database from 2 excell documents. There are more columns in one document than the other, some of them being common with the other document. I want to place these common columns in line after their namesake columns in the database, and the uncommon columns will be new fields.

How can this be done, or am I making sense

Brian.
 
There are three posibilities here.
1) You have two tables where each row in one table
describes an item that is not described in the other table
and vice versa.
2) You have two tables where each row in each table
describes different aspects of an item that is also
described in the other table and vice versa.
3) You have two tables where some of the items described
in one table have different aspects described in the other
table and some are not described in the other table.

In 1) you want all of the information in one table
added 'below' the information in the other table.

In 2) you want some of the information in one table added
beside the information in the other table and some of the
information is redundant.

In 3) you want some of teh information in one table added
beside the information in the other table and some added
below.

In any case I would begin by importing each excel sheet to
a separate table. Open the first table in design view and
add all of the uncommon fields (columns) to it. You can
actually do this by openning both in design view one
beside the other and then copying and pasting the fields
you need from the second to the first. This will only
create blanks though so it is but a first step.

For 1 or 3 create an Append query that adds all of the
records from the second table to the first. For 1 you
should be done at this point for three you have more work
to do.

For 2 or 3 create an Update query that populates all the
uncommon fields in the first table from the second table
based on one or more common fields.

In case 2 you may want to consider leaving the two tables
separate and creating a relationship between them or a
Select query that brings their data together. This is
where the true power of Access and relational databases in
general comes in.

Cheers
-----Original Message-----
Hello,

I'm a relative beginner with Access. This question is
Click to expand...
quite similar to another I found here but with an
important difference. I am trying to create a single
database from 2 excell documents. There are more columns
in one document than the other, some of them being common
with the other document. I want to place these common
columns in line after their namesake columns in the
database, and the uncommon columns will be new fields.
 
Thanks, that has half solved the problem. Now i have all the data in one database, but i have to move the columns from field 3 to the right by one column, but only after, say, row 50. Is there any way I can freeze the above rows and insert a column below?
 
Back
Top