Update in Simple Join Query

[drunkenneo]

I am strucked to a simple join query please help





Here I want to update the Flag of tblDataentry if any one of the orig or dest is -1 else 0. Please Help

[JoeM]

Are you saying that you want to update the "Flag" field in the tblDataEntry table if either the origin or dest fields in the same table is True (-1)?
If that is the case, then the Flag field sounds like it is actually a calculated field and should not be stored on the table level, but rather calculated on-the-fly on the query level.

[lfpm062010]

This is how I interpret your request. You want to update the tblDataentry.Flag = True if the tblDataentry.origin code found in tblCodes.Cod_Id have True on either tblCodes.airCodes or tblCodes.Flag are True (same with tblDataentry.dest)

tblDataentry.Flag, tblCodes.airCodes, and tblCodes.Flag are Yes/No data type.

If that is true, this is how I would do it.
[SQL]
UPDATE tblDataentry SET tblDataentry.Flag = True
WHERE (((Nz(DLookUp("1","tblCodes","Cod_Id = '" & [tblDataentry].[Origin] & "' AND (airCodes = True OR Flag = True)"),0)+Nz(DLookUp("1","tblCodes","Cod_Id = '" & [tblDataentry].[dest] & "' AND (airCodes = True OR Flag = True)"),0))>0));

[JoeM]

I am not saying it cannot be done, but it is not adviced. Doing so usually violates the rules of normalization and is considered a bad programming practice, as it can undermine the integrity of your data and database.
For example, what happens if at some point down the road, those Origin or Dest fields get changed? The Flag field will NOT update automatically, so your data will not be in agreement.

The General Rule of Thumb is this: anything that can be calculated off of other fields should NOT be stored in a table. It should be calculated in a query, where it is dynamic.