combining the data from two tables

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

Guest

Hi,

I have two tables (10 columns by 400000 rows) that have been created by
importing text data files into access. The tables are identical in size and
I just want to add them together (ie sum the top left value in one table with
the top left value in the other table, etc), then export them back into text
format. There are no identifiers in the tables, just data. I've thought of
using Excel, but it's difficult to use Excel with such a large amount of
data. Is there an easy way of doing this in Access?

t.roff
 
Unless you've got some way of correlating the two tables, it's not going to
be reliable in Access.

Just because the rows were inserted into the table in a specific order does
not mean that you will be able to retrieve the rows in that same order. The
only way to guarantee the order in which the rows are returned is to use an
ORDER BY clause.
 
t.roff said:
Hi,

I have two tables (10 columns by 400000 rows) that have been created
by importing text data files into access. The tables are identical
in size and I just want to add them together (ie sum the top left
value in one table with the top left value in the other table, etc),
then export them back into text format. There are no identifiers in
the tables, just data. I've thought of using Excel, but it's
difficult to use Excel with such a large amount of data. Is there an
easy way of doing this in Access?

t.roff
Click to expand...

As Douglas said, you are going to need a unique line ID assuming all the
lines are in both spreadsheets and in the exact same order in each.

Line numbers from Excel might do it.

The problem is Access like more databases does not keep records in any
specific order. Most of the time they look in order, but don't count on it.
 
t.roff said:
Hi,

I have two tables (10 columns by 400000 rows) that have been created
by importing text data files into access. The tables are identical
in size and I just want to add them together (ie sum the top left
value in one table with the top left value in the other table, etc),
then export them back into text format. There are no identifiers in
the tables, just data. I've thought of using Excel, but it's
difficult to use Excel with such a large amount of data. Is there an
easy way of doing this in Access?

t.roff
Click to expand...

Oh yea, once you have a field to order and to build a relationship
between the two tables, what you want to do is easy with a update query.
 
Joseph said:
As Douglas said, you are going to need a unique line ID assuming all the
lines are in both spreadsheets and in the exact same order in each.
Click to expand...

I think theory may be clouding practical judgement. Jet may provide a
relational view of the data but the 'databases' involved here are
files.

Allow me to turn this on its head. For example:

CREATE TABLE [Excel 8.0;Database=C:\Book1.xls;].MyTable
(data_col FLOAT NULL)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (1)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (2)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (3)
;
SELECT data_col
INTO [MS Access;Database=C:\db1.mdb;].MyTable
FROM [Excel 8.0;Database=C:\Book1.xls;].MyTable
;
UPDATE [MS Access;Database=C:\db1.mdb;].MyTable
SET data_col = data_col + 0.1
;
SELECT data_col
INTO [Excel 8.0;Database=C:\Book2.xls;].MyTable
FROM [MS Access;Database=C:\db1.mdb;].MyTable
;

Can anyone suggest any reason why the order of the rows in Book2 would
differ from those in Book1?

Jamie.

--
 
Jamie Collins said:
Joseph said:
As Douglas said, you are going to need a unique line ID assuming all the
lines are in both spreadsheets and in the exact same order in each.
Click to expand...

I think theory may be clouding practical judgement. Jet may provide a
relational view of the data but the 'databases' involved here are
files.

Allow me to turn this on its head. For example:

CREATE TABLE [Excel 8.0;Database=C:\Book1.xls;].MyTable
(data_col FLOAT NULL)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (1)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (2)
;
INSERT INTO [Excel 8.0;Database=C:\Book1.xls;].MyTable
VALUES (3)
;
SELECT data_col
INTO [MS Access;Database=C:\db1.mdb;].MyTable
FROM [Excel 8.0;Database=C:\Book1.xls;].MyTable
;
UPDATE [MS Access;Database=C:\db1.mdb;].MyTable
SET data_col = data_col + 0.1
;
SELECT data_col
INTO [Excel 8.0;Database=C:\Book2.xls;].MyTable
FROM [MS Access;Database=C:\db1.mdb;].MyTable
;

Can anyone suggest any reason why the order of the rows in Book2 would
differ from those in Book1?
Click to expand...

Without thorough testing, I wouldn't want to bet the company on it...

You're (probably) correct that it will work, but I don't believe there's any
way to be sure.
 
Without thorough testing, I wouldn't want to bet the company on it...


Remember we're talking about a spreadsheet table without an identifier
column. I think this company is prepared for the risk <g>.

Jamie.

--
 
You must log in or register to reply here.
Back
Top