Query that only adds new data

[breezett93]

I'm working on a query that will copy one column from table A to table B when a button is clicked. For efficiency, I'd like only new data added instead of copying every single value every time.

This is what I've started with.

UPDATE tableB


INNER
JOIN tableA
ON tableA.columnName = table2.columnName
SET tableB.columnName = table1.columnName
WHERE tableB.columnName = 'columnName'

[ranman256]

you have an update query.
queries that Add data are called append queries....
INSERT INTO TABLE ...

[John_G]

In your query SQL, if "columnName" is what it looks like, i.e. the same everywhere, that query won't work. You are using the same field you want to update as you are using to define the join, which makes no sense; the join won't select the records you need because the data isn't there yet.

It would be more helpful if you posted the SQL with the actual field names.

[breezett93]

you have an update query.
queries that Add data are called append queries....
INSERT INTO TABLE ...

So I am looking for something like:

INSERT INTO tableB (columnName)
Select columnName
From tableA
//Where only data that hasn't already been inserted is inserted

[breezett93]

It would be more helpful if you posted the SQL with the actual field names.

Certainly. ranman mentioned I should use an append query. So now I'm looking at:

Insert into IvcTbl (Promise Date)
Select Promise Date
From OrdTbl
//Where only data that hasn't been inserted yet is appended

[aytee111]

Use the Query Wizard to create an Unmatched query - this will find records on one table/query which do not exist on a second table/query. Then in design view change it to an Append query.

[breezett93]

Use the Query Wizard to create an Unmatched query - this will find records on one table/query which do not exist on a second table/query. Then in design view change it to an Append query.

Here is what Access gave me after doing what you suggested:

SELECT OrdTbl.ShpDt
FROM OrdTbl LEFT JOIN IvcTbl ON OrdTbl.[OrdId] = IvcTbl.[OrdId]
WHERE (((IvcTbl.OrdId) Is Null));

You're saying I need to just swap out SELECT for INSERT INTO? Why is the Where clause like that? I don't want blank orders.

[aytee111]

Change the query type to Append, either under Design at the top or right-click in the empty gray space - don't try and do it manually.

Access created the query for you, why don't you believe it?