Is it possible top do an Append or Update inside the same Query

[wcrimi]

I have an existing process that looks at a table. If several conditions are met, it creates a new extract table with some of the information. About 10% of all the records meet all the criteria to become part of the extract.



Sometimes, at a later date, I want to override those results.

Here's the catch.

Sometimes that means I have to override a field on an existing extract record (do an update) and sometimes that means I have to add record that didn't formerly quality but now does (append a new one). I don't necessarily know at the time of the override if the record is already on the extract file.

If there a way to write a query where I give it the info it needs to look on the extract file, if it finds a match, just update the record and if it doesn't find the record add a new one?

[ranman256]

no, but you can run both an append & update queries 1 after the other.

[pbaldy]

I've heard it can be done, never tried it. See:

https://thedbguy.blogspot.com/2016/0...th-append.html

[isladogs]

Yes it can definitely be done. Its sometimes called an UPSERT query though I prefer to call it an UPEND query.
Its an APPEND query with an OUTER join
See this link for more details and an example app http://www.mendipdatasystems.co.uk/u...ery/4594428616.

It can be useful when synchronising data http://www.mendipdatasystems.co.uk/s...a-1/4594514001
However, in my tests, I've found that its usually slightly slower than running each query separately

[wcrimi]

The tricky part is that at the time I create my override table I don't know whether it's going to wind up being an update or an append.

If I run the Update first, it will only update those records that matched on the key fields and more or less disregard the ones that need to be added.

Then when I run my append, if the record doesn't exist it will add it, but if it does exist already (an update), it will just get a dupe trying to add the same key and error out.

I guess I can live with that, but it's not ideal

[isladogs]

The tricky part is that at the time I create my override table I don't know whether it's going to wind up being an update or an append.

If I run the Update first, it will only update those records that matched on the key fields and more or less disregard the ones that need to be added.

Then when I run my append, if the record doesn't exist it will add it, but if it does exist already (an update), it will just get a dupe trying to add the same key and error out.

I guess I can live with that, but it's not ideal

There should be no issue whether you do this as two separate queries or as an 'upend' query
Design the append part so it only appends new records not in the existing table.
To do this you create an unmatched select query then change it to an append query

The update part will only work on records that already exist

If you look at my second link, you will see how I do this both as separate queries & then combined in order to synchronise data

[wcrimi]

There should be no issue whether you do this as two separate queries or as an 'upend' query
Design the append part so it only appends new records not in the existing table.
To do this you create an unmatched select query then change it to an append query

The update part will only work on records that already exist

If you look at my second link, you will see how I do this both as separate queries & then combined in order to synchronise data

Thanks I'll see if I can figure that out this week. My Access knowledge is somewhat limited. I never did an umatched select query.

[isladogs]

You're welcome. Just follow the approach shown in the links.