append query

[kroenc17]

Hello all,



I'm trying to make a query that will append data from one table into another table. I've done this successfully, however I only want to append entries that have a new update. If a row is the same as it was before, I don't want it to be appended to the new table.

For example: My first table is a list of ongoing projects. One project may have an update added to a field, but another project may not. Right now my query is appending every row to the new table whether or not it is the exact same as it was previously (aka no updates, nothing new).

How do I ONLY APPEND NEW INFORMATION?

[Rixxe]

If you have a primary key, Say an ID like '0001'

And you append a table with another entry like '0001' and '0002' it won't append '0001' but will add '0002'.

So basically select a field to be a primary key, like an ID.

Good luck

[kroenc17]

I added the Primary Key ID field from the table that is being appended to to my query and when I tried running it I got an error saying that all rows could not be updated because of primary key violations. I also tried it the other way around ... appending the Primary Key ID field from the original table to the new table, and got the same error.

Can you walk me through your suggestion with a bit more detail? Thanks for the help

[Rixxe]

Well as far as i remember:

If you have a table with results from QueryA, You then change the ID to be a primary Key.
When you append the results of QueryB to the table with QueryA results, you should only be able to append the rows which have different ID's.

Please note, the fields will have to be exact. (Same type, same size, etc)

There may be other requirements, but i can't remember them at the moment.

Hope this is helpful. However in the case that it isnt, you might want to discover other way's (Or wait for other peoples replys.)

Good luck

[Rixxe]

I forgot to mention, that if the ID is duplicated (In some databases it happens) Then this will fail horribly, as the ID/Primary key has to be unique.

[kroenc17]

Both tables primary key is already an AutoNumber ID field ... I've tried appending that field into the second table but whether I append it from itself or from the previous table I get the above error message.

Thanks for the help, I'll continue hacking away ... any other suggestions? Is this possible to do?

[Rixxe]

This should help:

http://office.microsoft.com/en-us/ac...001186063.aspx

[kroenc17]

Access kills me. I'm working off of that link trying to use the Query Wizard to create an Unmatched Query ...

it asks "Select the matching field from each table."

it does not list all of the fields in either table.

the fields it does not list are the matching fields.

[Rixxe]

Did the append query work then?

Unmatched queries can be done better with SQL code:

Select a.*
From TableA as a
Left outer Join TableB as b
on a.ID=b.ID
where b.ID is null

If i made a mistake with the syntax don't hate me, just wrote it in 2 seconds.

Regards

Rixxe