Can I make a single query both append and update as applicable?

[securitywyrm]

Howdy folks. I"m hoping a certain thing is possible, and hope you can direct me on the right path.



TLR: Can I write a SQL query that will...
1. Check if a record in the "to import" table has a matching entry in the "Main data" table
2. Update the main data table if there's a match.
3. Append the record if there's not a match.
4. Loop back until all records have been processed.

I figure I can do an if-then command where if there's a match, run the update, if there's not then do the append. However this is an order of magnitude of SQl coding above anything I've ever done, so I want to be sure it's even possible before I start digging into it.

Long version

I've inherited a database with a defective update process. In theory, seven queries are run in order to update and append a main data table as appropriate. However the queries have NEVER worked outside of a thin slice of test data performed offline in a controlled environment, and last year I had to do about 80 hours of manual data entry to get the data in for the year. The new year's data is coming up, and I've learned a year's worth of Access in that time, and I'm hoping to do something other than do it manually. Our main data table is also on a Sharepoint that I think is running on an ISDN line in a basement somewhere, so any attempt to run a mass-update fails spectacularly.

What I'd like to do is replace those seven queries with a single robust query that will go through the 'import' table line by line, updating and appending as applicable. Is this something that can be done in a SQL query, or are SQL queries limited to just append OR update in a single query?

[John_G]

...or are SQL queries limited to just append OR update in a single query?

That is correct. An action query (which is what UPDATE and Append are) is either one or the other, and the syntax is quite different for each.

However, you can accomplish your goal with two queries, one to update the other to append, and it can be done without VBA code, which would be needed if you want to loop through records one by one.

When you say a mass update fails, what do you mean by "mass update"? Do you mean an update/append query that acts on many records at once?

[securitywyrm]

That is correct. An action query (which is what UPDATE and Append are) is either one or the other, and the syntax is quite different for each.

However, you can accomplish your goal with two queries, one to update the other to append, and it can be done without VBA code, which would be needed if you want to loop through records one by one.

When you say a mass update fails, what do you mean by "mass update"? Do you mean an update/append query that acts on many records at once?

Thank you for the response!

1. Could an if-then SQL that does both append and update be put into a subroutine then? I don't yet know the basics of SQL, I'm having to learn as I go. So one clicks the button to run the subroutine, it grabs first record, runs it through the evaluation to see if there's an existing record, if yes then does a update command, if not does an append command, loop to next record?
2. More than 10 records at a time tends to freeze it. I have no kind words for the system.

[davegri]

1. Could an if-then SQL that does both append and update be put into a subroutine then? I don't yet know the basics of SQL, I'm having to learn as I go. So one clicks the button to run the subroutine, it grabs first record, runs it through the evaluation to see if there's an existing record, if yes then does a update command, if not does an append command, loop to next record?

You can certainly write a 'hybrid' (my term) update routine.
Use VBA to read the import table, one line at a time. Then the VBA logic would decide whether to build an SQL string to either update an existing record or append a new record. Then fire off the SQL, read the next import and repeat until end of file.
The logic is pretty straightforward, but the SQL syntax can get a bit hairy if there are a lot of fields, a mixture of numeric, text, and nulls.
If this overloads the server, you can insert some delay logic.

[isladogs]

Davegri's solution will certainly work one record at a time ... as you requested.

More than 10 records at a time tends to freeze it. I have no kind words for the system.

I suggest you look at the actual queries used to determine whether these can be improved to overcome that issue
Alternatively, is there any possibility of the BE being moved to SQL server instead of Sharepoint?

[June7]

Maybe can be accomplished in one SQL action (call it an UPSERT). Bing: Access query UPSERT.

Here is one https://stackoverflow.com/questions/...g-in-ms-access

[ArviLaanemets]

I haven't used Access as backend for a lot of years anymore, but in SQL Server databases in such cases, I mostly prefer to truncate the table, and then query the whole table anew - usually it will be much faster that running 3 separate queries (Delete, Update, and Append). Of course it is applicable only, when table doesn't have autonumeric PK (p.e. data are read from some another program).

Sometimes will be there some field (record no, date, etc.) which allows to limit updatable scope. Then I delete only last records which possibly will be updated, and insert them anew.

[securitywyrm]

Davegri's solution will certainly work one record at a time ... as you requested.



I suggest you look at the actual queries used to determine whether these can be improved to overcome that issue
Alternatively, is there any possibility of the BE being moved to SQL server instead of Sharepoint?

Unfortunately I'm a low level minion in a government beuracracy. As I put it, "I am not qualified to do this database work, but I am the most qualified of everyone available."

[davegri]

Do you want our help? You have one transaction table (your import) that you want to use to add/edit a main table.
How many fields are in the import table? the Main table?
How do you determine if a record in the import table is to be added or used to update an existing record in the main table?
Are there any duplicate records in the main table?
Are the tables residing on a server?

If you are still hanging in there, give us the record layout of both the main table and import table.