Add more than one update to a field from an UPDATE Query

[jlgray0127]

I have the below query, which updates a fields value.

UPDATE ProgramNew INNER JOIN GWActivitySelection ON (ProgramNew.[NPI CPI Number] = GWActivitySelection.[NPI CPI Number]) AND (ProgramNew.[Program Name] = GWActivitySelection.Program) SET GWActivitySelection.Required = 0, GWActivitySelection.[Count] = 0
WHERE (((ProgramNew.GW1) Is Null) AND ((GWActivitySelection.GW)="GW1"));

I want to add to this, rather than build another query.

What it's saying, is if the there is no value, then update two different fields to 0.
This works perfect.

What I want to do is say, if the field is not blank, then update one field to -1 and the other to 1.

Not sure how, to add the statement:

SET GWActivitySelection.Required = -1, GWActivitySelection.[Count] = 1


WHERE (((ProgramNew.GW1) Is NOT Null) AND ((GWActivitySelection.GW)="GW1"));

It would be amazing, if I could also have the same changes in the same query where WHERE (((ProgramNew.GW2) Is NOT Null) AND ((GWActivitySelection.GW)="GW2")); etc.... I go all the way to GW8, so I have 8 separate queries now, and would have 16 separate queries, if this is not possible at all.

[orange]

see http://www.w3schools.com/sql/sql_update.asp

[June7]

If all filter criteria cannot be incorporated into one WHERE clause, then need separate queries. Will OR work for your situation?

WHERE (ProgramNew.GW1 Is Null AND GWActivitySelection.GW="GW1") OR (ProgramNew.GW2 Is Null AND GWActivitySelection.GW="GW2") OR ...


Why save calculated data? Saving calculated data (data that is dependent on other saved values) is usually, at best, unnecessary, or, at worst, dangerous. If values can be calculated for an UPDATE, they can be calculated whenever needed.

Also, it appears data structure is not normalized. Multiple similar name fields (GW1 through GW8) storing the same type data is an indicator of non-normalized structure.

If you want to provide database for analysis, follow instructions at bottom of my post.

[pbaldy]

If I understand correctly, you can take that field out of the WHERE clause, and change your SET's to something like:

SET GWActivitySelection.Required = IIf(IsNull(ProgramNew.GW1), 0, 1)

Noting that I didn't pay much attention to the values, just giving a methodology.

[jlgray0127]

If all filter criteria cannot be incorporated into one WHERE clause, then need separate queries. Will OR work for your situation?

WHERE (ProgramNew.GW1 Is Null AND GWActivitySelection.GW="GW1") OR (ProgramNew.GW2 Is Null AND GWActivitySelection.GW="GW2") OR ...


Why save calculated data? Saving calculated data (data that is dependent on other saved values) is usually, at best, unnecessary, or, at worst, dangerous. If values can be calculated for an UPDATE, they can be calculated whenever needed.

Also, it appears data structure is not normalized. Multiple similar name fields (GW1 through GW8) storing the same type data is an indicator of non-normalized structure.

If you want to provide database for analysis, follow instructions at bottom of my post.

I think this may work, and will try it.
GW1, is actually a date field in a main form. There is a subform, which lists data tied to the main form. If GW1 is blank, I want it to change all the records in the subform, related to GW1, to false. If it has a date, then the criteria to true.
Also, the GW1, is a deadline, if a date is added. If GW1 surpasses today's date, the fields relevant to the main form, in the subform, are then locked, so they can not be edited.

[jlgray0127]

Hello, again! I have made this into two SQL Statements (two queries), which I will paste below. What I want to happen, is when a field in the form loses focus, I want it to update the table. If there is a date in the form, I want it to:

UPDATE ProgramNew INNER JOIN GWActivitySelection ON (ProgramNew.[Program Name] = GWActivitySelection.Program) AND (ProgramNew.[NPI CPI Number] = GWActivitySelection.[NPI CPI Number]) SET GWActivitySelection.Required = -1, GWActivitySelection.[Count] = 1
WHERE (((ProgramNew.GW5) Is Not Null) AND ((GWActivitySelection.GW)="GW5"));

If there is a date, I don't want it to do anything. If by chance, the operator comes back to the cell and enters a date, I want it to revert the table's data back to the default values:

UPDATE ProgramNew INNER JOIN GWActivitySelection ON (ProgramNew.[Program Name] = GWActivitySelection.Program) AND (ProgramNew.[NPI CPI Number] = GWActivitySelection.[NPI CPI Number]) SET GWActivitySelection.Required = -1, GWActivitySelection.[Count] = 1
WHERE (((ProgramNew.GW5_5) Is Not Null) AND ((GWActivitySelection.GW)="GW5.5"));

What I did, was added this to one macro, so both queries run, but it's not updating anything now.
Is there a way to do the below in one statement on lost focus? I'm not sure how to approach this. I just need the two records to update if the form has a date or not.

UPDATE ProgramNew INNER JOIN GWActivitySelection ON (ProgramNew.[Program Name] = GWActivitySelection.Program) AND (ProgramNew.[NPI CPI Number] = GWActivitySelection.[NPI CPI Number]) SET GWActivitySelection.Required = -1, GWActivitySelection.[Count] = 1
WHERE (((ProgramNew.GW5) Is Not Null) AND ((GWActivitySelection.GW)="GW5"))
OR
UPDATE ProgramNew INNER JOIN GWActivitySelection ON (ProgramNew.[Program Name] = GWActivitySelection.Program) AND (ProgramNew.[NPI CPI Number] = GWActivitySelection.[NPI CPI Number]) SET GWActivitySelection.Required = -1, GWActivitySelection.[Count] = 1
WHERE (((ProgramNew.GW5_5) Is Not Null) AND ((GWActivitySelection.GW)="GW5.5"));


I don't know how to even begin to word this in SQL, if it would even work.

[June7]

You have the SQL. What you want is VBA.

Did not respond to questions. Why are you saving calculated data?

[jlgray0127]

I'm not saving a calculated field. With the SQL, I'm changing it to true or false. -1 or 0, and adding a digit to another field to sum...
Required is a true or false. If there is no date, its not required and in turn, not counted. If there is a date, it's required and [Count] switches to 1, so I pick it up in my query for my report.
Seemed simple to me... but it all seems simple when you don't know what you're doing! LOL

[June7]

Changing the value of a field (regardless of the type, even Yes/No) based on other data is saving calculated data. As stated, if values can be calculated for an UPDATE, they can be calculated whenever needed.

However, running an action SQL in VBA would be:

CurrentDb.Execute "UPDATE ... "

The real trick is figuring out what event to use.

[jlgray0127]

That makes sense! Does it make sense why I am capturing it then?
This thing is killing me. I have to have it rolled out Monday, and am really behind on getting it put together.
I'm lost on the VBA. I don't believe it's worded the same, and am unsure how to type it out. As for where to put it, I think lost focus, works best, as my concern was the user skipping the field, then coming back and adding a date, or deleting it.
You know, if there was a chat box for this forum, other than the experts not getting anything else done, that would be amazing!!! LOL

Thank you so much, June7! You have helped me so many times over the years, you're name is permanently burned into my brain! :P

[orange]

If you have fields that must be populated before saving or updating a record, you can write code to ensure all those fields are completed.
If the user has skipped around completing the data, you can still do the field completion and validation checks.
The real issue, and I'm sure part of your tension at the moment, is do you have a clear understanding of what you are trying to do. Seems to me that most or part of your uncertainty is clear statement of WHAT has to be done.

Good luck.

[June7]

It still looks like to me you want to populate a field in another table based on value of field on form. That is saving dependent data and usually a bad idea. Also, that is a different issue from forcing fields to be populated on the form.

[orange]

jlgray0127,
It looks like we're still trying figure out WHAT your issue/opportunity is.
It also appears, since your last post was before 10AM today, that you have not been looking to this thread for any answers or advice.

Good luck with your project, and if you want support/advice from this forum - you really have to participate.

[jlgray0127]

Hello!!! Sorry for the delay! I had a meeting to go to and was tied up the remainder of the day!
I see what you are saying about saving the data, so yes, where I said I was not saving, I clearly am saving it. I was looking at is as changing the value, but true, if the field is empty or populated, I am trying to create a result in a table for this.
These are projects we are tracking. If the date field has a value, I need it to show the data as required and countable. If the date field is empty, I want it to change the value to false, (not required) and Counted to 0, so it does not sum, but is still showing as a step in the project. Does this make any sense?

I am still looking for a solution! I have all of these queries running, but for whatever reason, it's unstable. The database is not updating as I am wanting it to.

Is there a way to join the statements as I asked?

[June7]

I still recommend a SELECT query that joins the tables. Then calculate a field that returns the desired value based on the date field content.

As I said, any query to UPDATE the value can just be a SELECT query to calculate it when needed.

Unless the WHERE statements can be combined (maybe with OR operator), no I don't think can be a single action. One query references GW5_5 and GW5.5 and the other GW5.

Do you want to provide db for analysis? Follow instructions at bottom of my post.