Update and append queries

[Ryan]

I have an ODBC connection to an AS/400 from Microsoft Access. I have made
tables in Access 2003 that have all the same fields in the AS/400 by running
a make table query. Now I would like to keep these tables current by running
update queries to find the changes made to records that are already in my
access table, and an append query to get new records. I understand how to
create the update query. I use the query design view, but here is the update
SQL.

UPDATE Trans INNER JOIN Trans ON Trans.PART = Trans.PART
SET Trans.Tract# = [Trans].[Tract#];

I need help with the append query. Im not sure how to tell the query to
look for Trans.Tract# that dont exists. I have read most of the posts about
append queries, but none seem to be trying to duplicate, or keep an exact
copy of a table that is getting updated daily. In short, I want to run a set
of queries to update an access table to make it current with a table on an
AS/400 for reporting purposes. I have tried running the queries on the
AS/400 table links, but join queries take forever to run. Any assistance
with the append query will be greatly apprieciated.

 

[Ryan]

Correction on update query

UPDATE Trans INNER JOIN Trans ON Trans.Tract# = Trans.Tract#
SET Trans.Tract# = [Trans].[Tract#];

I have an ODBC connection to an AS/400 from Microsoft Access. I have made
tables in Access 2003 that have all the same fields in the AS/400 by running
a make table query. Now I would like to keep these tables current by running
update queries to find the changes made to records that are already in my
access table, and an append query to get new records. I understand how to
create the update query. I use the query design view, but here is the update
SQL.

UPDATE Trans INNER JOIN Trans ON Trans.PART = Trans.PART
SET Trans.Tract# = [Trans].[Tract#];

I need help with the append query. Im not sure how to tell the query to
look for Trans.Tract# that dont exists. I have read most of the posts about
append queries, but none seem to be trying to duplicate, or keep an exact
copy of a table that is getting updated daily. In short, I want to run a set
of queries to update an access table to make it current with a table on an
AS/400 for reporting purposes. I have tried running the queries on the
AS/400 table links, but join queries take forever to run. Any assistance
with the append query will be greatly apprieciated.

 

[John Spencer]

Assuming TWO tables, one named TransA (the one to add new records to) and
TransB (the one with the AS/400 records)

INSERT Into TransA([Tract#])
SELECT TransB.[Tract#]
FROM TransA LEFT JOIN TransB
ON TransA.[Tract#] = TransB.[Tract#]
WHERE TransB.[Tract#] is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Correction on update query

UPDATE Trans INNER JOIN Trans ON Trans.Tract# = Trans.Tract#
SET Trans.Tract# = [Trans].[Tract#];

I have an ODBC connection to an AS/400 from Microsoft Access. I have
made
tables in Access 2003 that have all the same fields in the AS/400 by
running
a make table query. Now I would like to keep these tables current by
running
update queries to find the changes made to records that are already in my
access table, and an append query to get new records. I understand how
to
create the update query. I use the query design view, but here is the
update
SQL.

UPDATE Trans INNER JOIN Trans ON Trans.PART = Trans.PART
SET Trans.Tract# = [Trans].[Tract#];

I need help with the append query. Im not sure how to tell the query to
look for Trans.Tract# that dont exists. I have read most of the posts
about
append queries, but none seem to be trying to duplicate, or keep an exact
copy of a table that is getting updated daily. In short, I want to run a
set
of queries to update an access table to make it current with a table on
an
AS/400 for reporting purposes. I have tried running the queries on the
AS/400 table links, but join queries take forever to run. Any assistance
with the append query will be greatly apprieciated.