update a table that has data inputted from an append query

[kniedens via AccessMonster.com]

Hi, fairly new at this....I have 6 files from 6 different plants. The field
names are all the same for each, and I want to make one table that is easily
updated as the 6 different tables (in Excel...linked into the Access Database)
are updated.

I thought I'd use append queries to update the table, but when I do that, and
an Excel file is changed, the table doesn't automatically update. If I re-run
the append query it doesn't get rid of the old information....just pastes new
data into the table.

Please let me know if you have any suggestions on obtaining the results for
this.

Thanks!
Kari

 

[John Vinson]

Hi, fairly new at this....I have 6 files from 6 different plants. The field
names are all the same for each, and I want to make one table that is easily
updated as the 6 different tables (in Excel...linked into the Access Database)
are updated.

I thought I'd use append queries to update the table, but when I do that, and
an Excel file is changed, the table doesn't automatically update. If I re-run
the append query it doesn't get rid of the old information....just pastes new
data into the table.

Please let me know if you have any suggestions on obtaining the results for
this.

An Append query does just that - makes a COPY of the data in the
source table into a new, completely independent table. The table
you're appending to doesn't update when you update the table you
copied from. It's like you had copied a Word document page into
another Word document; editing the old document, or the new document,
would of course have no effect on the other document.

Similarly, running an append query again does exactly that: appends
the data (all the data) from the source table into the target table.

If you want to maintain the Excel spreadsheets as the "master"
document but still be able to view the data all as one table, you can
use a UNION query:

SELECT thisfield, thatfield, theotherfield FROM Sheet1
UNION ALL
SELECT thisfield, thatfield, theotherfield FROM Sheet2
UNION ALL
SELECT thisfield, thatfield, theotherfield FROM Sheet3;

The resulting query will NOT be updatable - doubly so, because no
UNION query is ever updatable, and linked Excel tables cannot be
edited either.

Any chance you could turn this around, and store the data in one
single Access table (with an additional field indicating which of the
six plants is meant)? This data could be linked to Excel, or exported
to Excel, at will.

John W. Vinson[MVP]

 

[John Nurick]

Hi Kari,

The simplest approach would be to create a union query incorporating all
six linked tables
SELECT * FROM AAA
UNION
SELECT * FROM BBB
...
and use this instead of an actual table.

Other possibilities:

1) to update the main table, delete all records from it and then append
again from the linked tables (maybe in one hit using the union query).

2) run two queries based on the union query: an update query to update
existing records, and an append query to add any new ones.

 

[kniedens via AccessMonster.com]

Thanks a million...this table is up and running!

Hi, fairly new at this....I have 6 files from 6 different plants. The field
names are all the same for each, and I want to make one table that is easily

[quoted text clipped - 8 lines]

Please let me know if you have any suggestions on obtaining the results for
this.

An Append query does just that - makes a COPY of the data in the
source table into a new, completely independent table. The table
you're appending to doesn't update when you update the table you
copied from. It's like you had copied a Word document page into
another Word document; editing the old document, or the new document,
would of course have no effect on the other document.

Similarly, running an append query again does exactly that: appends
the data (all the data) from the source table into the target table.

If you want to maintain the Excel spreadsheets as the "master"
document but still be able to view the data all as one table, you can
use a UNION query:

SELECT thisfield, thatfield, theotherfield FROM Sheet1
UNION ALL
SELECT thisfield, thatfield, theotherfield FROM Sheet2
UNION ALL
SELECT thisfield, thatfield, theotherfield FROM Sheet3;

The resulting query will NOT be updatable - doubly so, because no
UNION query is ever updatable, and linked Excel tables cannot be
edited either.

Any chance you could turn this around, and store the data in one
single Access table (with an additional field indicating which of the
six plants is meant)? This data could be linked to Excel, or exported
to Excel, at will.

John W. Vinson[MVP]

 

[kniedens via AccessMonster.com]

Thanks a million...got it up and running!

Hi Kari,

The simplest approach would be to create a union query incorporating all
six linked tables
SELECT * FROM AAA
UNION
SELECT * FROM BBB
...
and use this instead of an actual table.

Other possibilities:

1) to update the main table, delete all records from it and then append
again from the linked tables (maybe in one hit using the union query).

2) run two queries based on the union query: an update query to update
existing records, and an append query to add any new ones.

Hi, fairly new at this....I have 6 files from 6 different plants. The field
names are all the same for each, and I want to make one table that is easily

[quoted text clipped - 11 lines]

Thanks!
Kari