Solved: Comparing two fields in same Table & updating the third field

[kbsudhir]

Hi All,

I want to know is it possible to compare two fields in the same table & update the same in the third field. :dunno

If yes, how to go about doing this.
If no, what alternate steps I can take.

Any guidance is appreciated.

Regards
Sudhir

[Norie]

Sudhir

Sure it's possible, but why would you want to do it in the first place?

You shouldn't be doing calculations in tables, use queries instead.

[kbsudhir]

Okay, but I need that results in a table. Is it possible to create a new table/update a already created table with the results of the query where

[kbsudhir]

All I need to compare Field 3 with Field 2 to find which one is greater.
Ex. If Field 3 value is higher than Field 2 value then I should get a "Increase" in the corresponding filed in the query, if field 3 is lesser than filed 2 then "Decrease", if both are same then "Equal".

Then update them to a table.

[CreganTur]

You can do this with a standard update query using an IIF (not a typo) statement. The IIF would look like:

IIF([Field 2] > [Field 3], "Decrease", "Increase")
This would go into the Update To section in Query Design view for the field you are wanting to update.

The syntax for IIF is IIF(Condition, True, False).

HTH:thumb

[kbsudhir]

Thanks Randy,

I want to know how to update this to another table lets say Table 2

[CreganTur]

Thanks Randy,

I want to know how to update this to another table lets say Table 2

Just create an update query that includes both tables- be sure you have a relationship setup so the primary key/foreign key relationships are intact. Then you just use an IIF like in the example above- you just need to declare tableName.fieldName correctly so there's no confusion for SQL.

[kbsudhir]

I just want update all the results of this query to that table (Table2).
There is no relationship between both of them at all. & no primary key is required.

[CreganTur]

I just want update all the results of this query to that table (Table2).
There is no relationship between both of them at all. & no primary key is required.

Then you do what I outlined previously, just ignoring the part about relationships.

[kbsudhir]

Thanks, Its Done

Really appriciate your value help Randy & Norie
:bow: :bow:

Regards
Sudhir