Updating only TRUE's in ACCESS Query

[ilikered100]

I thought I posted this earlier this afternoon, but I can't find it anywhere so I'm reposting.

I have a table called MasterList where most of the fields are TRUE/FALSE (yes/no check boxes). I have two other tables with the same fields and field names (Yes I know now that this is very poor database design, but I'm stuck with it for now) that feed data into the MasterList table.

What I'm trying to do is aggregate all the TRUE's from different sources all into the MasterList table. When I run the query that merges data from one of the Merge tables into the MasterList table, it is overwriting the data in the MasterList table. What I want is if there is a TRUE in the MasterList table, but a FALSE(blank) in one of the merge tables, I want the TRUE to remain TRUE in the MasterList table. If there is a FALSE (blank) in the MasterList table and a TRUE in one of the Merge tables, then I want the MasterList table to be be updated to TRUE. So basically, I only want TRUE's to be updated, not FALSE's (blanks).

Here is a sample of the SQL I'm using when I merge data from one of the merge tables into the MasterList table

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID

SET MasterList.MathReadAloud = [tblmathmerge].[MathReadAloud],
MasterList.MathFSFrequentBreaks = [tblmathmerge].[MathFSFrequentBreaks],
MasterList.MathCalculator = [tblmathmerge].[MathCalculator],
MasterList.MathPlainEnglish = [tblmathmerge].[MathPlainEnglish];
​

Any help is much appreciated and again I realize there is a big problem with the design of the database. I'm a very intermediate Access user with a little SQL knowledge.

I hope this made sense.

Thank you,
CJ

 

[Micron]

Maybe because it's late here, but the only thing I can think of that certainly should work is IIF expressions. Outer join(s) might also work if say you specified all values from master table where the field(s) is/are true and only from the other side if True, but it would require sample data from both tables and some experimentation. As for IIF

MasterList.MathReadAloud = IIF([tblmathmerge].[MathReadAloud] = True, True, False)
I had Null instead of False; probably not appropriate.

Not sure why you use [] around some table/field names and not others. Only required if you use spaces or certain characters in names (which you should not) or Heaven forbid, start a name with a digit. Thought you might want to see how to shorten long names with aliases as well. If the following was done correctly, should work same as what you have.

UPDATE MasterList As ML INNER JOIN tblMathMerge As MM ON ML.ID = MM.ID

SET ML.MathReadAloud = MM.MathReadAloud,
ML.MathFSFrequentBreaks = MM.MathFSFrequentBreaks,
ML.MathCalculator = MM.MathCalculator,
ML.MathPlainEnglish = MM.MathPlainEnglish;

 

[xenou]

As well as using IIFs you can do you query one at a time for each item:

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID 
SET MasterList.MathReadAloud = [tblmathmerge].[MathReadAloud]
where MasterList <> -1

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID 
SET MasterList.MathFSFrequentBreaks = [tblmathmerge].[MathFSFrequentBreaks]
where MasterList <> -1

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID 
SET MasterList.MathCalculator = [tblmathmerge].[MathCalculator]
where MasterList <> -1

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID 
SET MasterList.MathPlainEnglish = [tblmathmerge].[MathPlainEnglish]
where MasterList <> -1

Please backup and test your data - you might even prefer to do your testing in a copy of the database first.

 

[ilikered100]

Thank you very much for the response. I rewrote my code using the suggested IIF. The code ran without an error, but the result is still the same where TRUE's in the Masterlist are being overwritten by FALSE's (blanks) in the MathMerge table.

Here is the code I rewrote with the IIF statement for the query:

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID

SET MasterList.MathReadAloud = IIF([tblmathmerge].[MathReadAloud] = True, True, False),
MasterList.MathFSFrequentBreaks = IIF([tblmathmerge].[MathFSFrequentBreaks]=TRUE,TRUE,FALSE),
MasterList.MathCalculator = IIF([tblmathmerge].[MathCalculator]=TRUE,TRUE,FALSE),
MasterList.MathPlainEnglish = IIF([tblmathmerge].[MathPlainEnglish]=TRUE,TRUE,FALSE);

As for your question about the brackets around the table names... that is what I inherited so I'm not sure why it was written that way.

Any additional help is very much appreciated.

CJ

 

[xenou]

You need the other table name in the IIFs:

UPDATE MasterList INNER JOIN tblMathMerge ON MasterList.ID = tblMathMerge.ID 

SET MasterList.MathReadAloud = IIF([MasterList].[MathReadAloud] = True, True, False),
MasterList.MathFSFrequentBreaks = IIF([MasterList].[MathFSFrequentBreaks]=True,True,False),
MasterList.MathCalculator = IIF([MasterList].[MathCalculator]=True,True,False), 
MasterList.MathPlainEnglish = IIF([MasterList].[MathPlainEnglish]=True,True,False);

I think this code will be unpredictable if you have Nulls in the MasterList table though.

FYI the rule for brackets is that they are required when the table name does NOT follow these rules (which are generally standard for SQL):

1. Table Names must start with a letter or an underscore (A-Z or _ )
2. Table Names must include only letters, underscores, and digits

Otherwise brackets are optional.

 

[Micron]

Thanks, xenou. Null would probably be an issue if there are any. Using Nz should take care of that, but using the full table names will probably make the sql look quite 'ugly'.
IMO, I would then use aliases as I pointed out. I can't tell what the default value should be if the field is Null, or even if it can be null.

 

[xenou]

My only real concern would be if the data in one or both tables were null:

SET MasterList.MathReadAloud = IIF([MasterList].[MathReadAloud] = True, True, False)

I think in this case the IIF would not pass the "true" test if MasterList.MathReadAloud is null, so it would get the value false, rather than the value that is stored in tblMathMerge.

Also if the value in tblMathMerge is null or True, we will be getting a value of False where the values in tblMathMerge is actually something else, and I don't like that either.

So, all bets off if there are any nulls, since there is nothing specified here about how to treat nulls.

 

[Micron]

agree, as noted

I can't tell what the default value should be if the field is Null, or even if it can be null.

 

[xenou]

I saw that. I wasn't sure why you said Nz() would probably fix it. So I gave more detail in case the OP was interested in diagnosing further.