Solved Conditional Update Query (1 Viewer)

LtWorf33

New member
Local time
Today, 09:26
Joined
May 14, 2020
Messages
13
Hi all. A new week and a new hurdle for the system I am working on. I am running an "UPSERT/UPPEND" query. I found a couple of resources that detailed the process and it is working exactly as intended. I would like to expand on this query and include a 'conditional update'.

SQL:
UPDATE tbl_OutlookLink LEFT JOIN tbl_AllRequests ON tbl_OutlookLink.Received = tbl_AllRequests.date_rec SET tbl_AllRequests.email_sender = [tbl_OutlookLink].[From], tbl_AllRequests.date_rec = [tbl_OutlookLink].[Received], tbl_AllRequests.email_contents = [tbl_OutlookLink].[Contents], tbl_AllRequests.email_subject = [tbl_OutlookLink].[Normalized Subject];

I have an additional field that is part of tbl_AllRequests that I would like to add to this update, but I only want it to update conditionally. I have a field called [sorting_ID]. If the [sorting_ID] field has been selected (it is a lookup field), then I do NOT want the UPSERT query to update anything.

Here is the logic:
- Run UPSERT query
- Update records IF [sorting_ID] is null/unchanged.

Context:
tbl_OutlookLink is a linked table that monitors an Outlook folder. I have a menu with a button that runs the UPSERT query. The idea behind the UPSERT query is if there is a new email in the linked folder, it will update and add it to tbl_AllRequests.

Hopefully all that makes sense. Thanks in advance for your assistance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:26
Joined
Oct 29, 2018
Messages
21,357
Hi. When you run the UPSERT query, you update existing records and append new ones. But in your "logic" above, you say to run the UPSERT query and then "update" null sorting_ID records. Update to what? You should be able to run the UPSERT and UPDATE queries in sequence.
 

LtWorf33

New member
Local time
Today, 09:26
Joined
May 14, 2020
Messages
13
Hopefully I can explain the context and the use case a little better. Obviously, I would love suggestions if you have a better solution.

- tbl_OutlookLink is essentially a 'hidden' table. Its purpose is to monitor the inbox.

- I run the UPSERT query to check for changes, and append any new records to tbl_AllRequests

- tbl_AllRequests is a table that allows me to have additional fields. Since tbl_OutlookLink is linked to the outlook folder, there are limitations. Including not being able to add/edit fields. So, the idea is that I can run the UPSERT request to update and append to a table that I can modify.

- When viewing records in tbl_AllRequests, it will be my job to set the [sorting_ID] field. It is a combobox/lookup. I will choose [in_house_service] OR [vendor_request].

- After the [sorting_ID] field has been set, I would like to exclude those records from the UPSERT query. Below is a screenshot of the design view. It may provide some more context. In the red box is the logic I am trying to get to work. I want it to update the sorting_ID to 'unsorted' only if a value in the combobox has not been set.

1589811917043.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:26
Joined
Oct 29, 2018
Messages
21,357
Hi. Use a test copy of your table and change the criteria in your query for sorting_ID to simply say: Is Null

Hope that helps...
 

LtWorf33

New member
Local time
Today, 09:26
Joined
May 14, 2020
Messages
13
Unbelievable. The solution was that simple. THANK YOU. I tried that every way but that one. Sometimes it helps to just step away for minute and get another set of (more experienced) eyes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:26
Joined
Oct 29, 2018
Messages
21,357
Unbelievable. The solution was that simple. THANK YOU. I tried that every way but that one. Sometimes it helps to just step away for minute and get another set of (more experienced) eyes.
Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Top Bottom