I am working on a comparison of 2 tables. for either one of the following situations: 1 to see what records match up and what records do not match up. In my tables it is legitimate to have null values in some of the fields. I am just not having any luck in being able to compare those records. They get left out of the result. I have tried various expressions and am at my wits end. Please advise.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
table comparison inclusive of null data 1
- Thread starter GueGueBla
- Start date
-
1
- #2
A NULL compared to anything else is always a null. A NULL in a calculation always results in a null. You cannot directly compare 2 fields for null.
You can check for null.
yourfield IS NULL
table1.yourfield IS NULL and table2.yourfield IS NULL
You can convert null to something else.
nz(yourfield,"was null"
nz(yourfield,"none" = nz(yourotherfield,"none"
You can check for null.
yourfield IS NULL
table1.yourfield IS NULL and table2.yourfield IS NULL
You can convert null to something else.
nz(yourfield,"was null"
nz(yourfield,"none"
= nz(yourotherfield,"none"- Thread starter
- #3
You don't need to update the table with values to check for null. This is an example of a couple of ways to build the where criteria.
This would select when the field in both tables was null.
Where table1.yourfield IS NULL and table2.yourfield IS NULL
OR
Where nz(yourfield,"none" = nz(yourotherfield,"none"
This would select when the field in both tables was null.
Where table1.yourfield IS NULL and table2.yourfield IS NULL
OR
Where nz(yourfield,"none"
= nz(yourotherfield,"none"- Thread starter
- #5
By the time I read this last portion of this thread, I had created queries that do fill the null records with an asterisk. However because of the 67 fields in my table this was time consuming and I am sure there is a faster way. Once I have time, unfortunately because I am building this query to make some production checks and will have to work with what I have right now. The longer way will do.
There is a slight twist to the tale that I have right now, when reading in text null fields, I put in for criteria Is Null and on the line below it "" to try and cover all possible scenarios. First of all, when I have the or is on the same line, the query updates every record regardless of what it finds, and secondly when I do have it split out like I do, sometimes it works when I have the is null first, and at other times it works better when I have the "" first. So my guess is that depending on what we have for the first record, either a null or "" I am still not covering everything. Is this a limitation to reading nulls in access if I do things the way I have it set right now, or would it be advisable for me to convert to the solution you have above and work with that?
There is a slight twist to the tale that I have right now, when reading in text null fields, I put in for criteria Is Null and on the line below it "" to try and cover all possible scenarios. First of all, when I have the or is on the same line, the query updates every record regardless of what it finds, and secondly when I do have it split out like I do, sometimes it works when I have the is null first, and at other times it works better when I have the "" first. So my guess is that depending on what we have for the first record, either a null or "" I am still not covering everything. Is this a limitation to reading nulls in access if I do things the way I have it set right now, or would it be advisable for me to convert to the solution you have above and work with that?
- Thread starter
- #7
UPDATE [tbl Products Total TBL by STATE], [tbl Updatable OUTGOING_MIGRATION] SET [tbl Products Total TBL by STATE].[New/ren_Rx_Cd] = "*", [tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd] = "*"
WHERE ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd]) Is Null) AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd])="") OR ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd])="" AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd]) Is Null));
This is the typical query I have that updates the fields that I am talking about. I have about 45 of these built out for every column. Would the fact that I have the two different tables over here have anything to do with the slight issues that I am having? I assumed that because they are two different tables and they have not been joined, the logic should not be looking at both tables to make it's decision, I was trying to kill two birds with one stone.
Anyhow, the eventual goal is to be able to make an accurate find unmatched query that will include records that have null values, those are valid, to see if the bigger table (MIGRATION) has all the records in the smaller table (STATE). To check my self, and to have a quick cross check, I am starting with a regular find matched select query.
WHERE ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd]) Is Null) AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd])=""
) OR ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd])="" AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd]) Is Null));This is the typical query I have that updates the fields that I am talking about. I have about 45 of these built out for every column. Would the fact that I have the two different tables over here have anything to do with the slight issues that I am having? I assumed that because they are two different tables and they have not been joined, the logic should not be looking at both tables to make it's decision, I was trying to kill two birds with one stone.
Anyhow, the eventual goal is to be able to make an accurate find unmatched query that will include records that have null values, those are valid, to see if the bigger table (MIGRATION) has all the records in the smaller table (STATE). To check my self, and to have a quick cross check, I am starting with a regular find matched select query.
- Thread starter
- #8
cmmfrds-in hind site, I have just re-read your response, I am not just making a comparison on two fields, I am comparing about 45 out of the 67 fields and I want to be able to do that, regardless of whether the field is null or not.So, I am trying to see if I can build an IIF statement into the criteria to say that if the record in that field is null, give it a value and then make the comparison. Having just written this out to you, I feel that it brings me back to the updating method. You did say I cannot compare nulls with nulls. I'll see if your method works out for this situation...will let you know if I get better luck this time.
These long table names with spaces are a pain to work with, I suggest shorten them and get rid of the spaces, or use an alias.
UPDATE [tbl Products Total TBL by STATE] as S, [tbl Updatable OUTGOING_MIGRATION] as M
SET S.[New/ren_Rx_Cd] = "*",
M.[New/ren_Rx_Cd] = "*"
WHERE (((S.[New/ren_Rx_Cd]) Is Null)
AND ((M.[New/ren_Rx_Cd])Is Null))
OR (((S.[New/ren_Rx_Cd])="" AND ((M.[New/ren_Rx_Cd])="");
This will catch where both are Null or both empty. If only 1 table may have null then will need to change the where to account for that.
UPDATE [tbl Products Total TBL by STATE] as S, [tbl Updatable OUTGOING_MIGRATION] as M
SET S.[New/ren_Rx_Cd] = "*",
M.[New/ren_Rx_Cd] = "*"
WHERE (((S.[New/ren_Rx_Cd]) Is Null)
AND ((M.[New/ren_Rx_Cd])Is Null))
OR (((S.[New/ren_Rx_Cd])=""
AND ((M.[New/ren_Rx_Cd])="");This will catch where both are Null or both empty. If only 1 table may have null then will need to change the where to account for that.
- Thread starter
- #10
Thanks for all your input, however the code that you have above is virtually the same as the code I pasted for you, the difference being that you create an alias for both tables, this gives you easier readability but I imagine that the logic and the performance should be the same.
Having said that, is it better to put the is null first, or put the "" first? Since the statement is an Or statement, it should not make a difference, but does it?
Having said that, is it better to put the is null first, or put the "" first? Since the statement is an Or statement, it should not make a difference, but does it?
- Thread starter
- #11
I think I know the answer to the last installment in my series of questions, I cannot criss-cross the criteria even if it's checking two tables that are not linked. For some reason and I don't totally understand the logic behind sql, the code works better if I have the two nulls on top and then the ""'s at the bottom or vice-versa, instead of criss-crossing.
These statements are completely different. I was trying to illustrate that you can check the rx-cd field in table1 for null and check the rx-cd field in table2 for null which in effect catches the case when both are null. If there are other variations you will need to add additional OR logic to catch them.
WHERE (((S.[New/ren_Rx_Cd]) Is Null)
AND ((M.[New/ren_Rx_Cd])Is Null))
OR (((S.[New/ren_Rx_Cd])="" AND ((M.[New/ren_Rx_Cd])="");
WHERE ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd]) Is Null) AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd])="") OR ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd])="" AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd]) Is Null));
The 1st statement checks the rx-cd field in both tables for null. You statement checks the rx-cd field in one table for null and the other table for being empty. So, these are doing different things.
WHERE (((S.[New/ren_Rx_Cd]) Is Null)
AND ((M.[New/ren_Rx_Cd])Is Null))
OR (((S.[New/ren_Rx_Cd])=""
AND ((M.[New/ren_Rx_Cd])="");WHERE ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd]) Is Null) AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd])=""
) OR ((([tbl Products Total TBL by STATE].[New/ren_Rx_Cd])="" AND (([tbl Updatable OUTGOING_MIGRATION].[New/ren_Rx_Cd]) Is Null));The 1st statement checks the rx-cd field in both tables for null. You statement checks the rx-cd field in one table for null and the other table for being empty. So, these are doing different things.
- Thread starter
- #13
I see what you mean. I have altered my sql statements to resemble yours and I am getting the results that I expect to see, there is one snag, it works for the majority of the fields, for some however, it doesn't, I have tried reversing the is null and the "" around to try see of the order was an issue, I have tried to make the criteria an OR between the two tables, that has a disastrous effect. Pretty much I don't know if there is anything I can do, or if this is one of the things access 97 is famous for. HELP!
When you say "it works for the majority of the fields"
Can you give an example where it does not work and what is the content of the database field in these cases.
Bear in mind that these values or lack of are different.
Null '- checked by IS NULL
empty '- represented by ""
space '- represented by " "
Can you give an example where it does not work and what is the content of the database field in these cases.
Bear in mind that these values or lack of are different.
Null '- checked by IS NULL
empty '- represented by ""
space '- represented by " "
- Thread starter
- #15
UPDATE [A], SET [A].PGM_YR = "*", .PGM_YR = "*"
WHERE ((([A].PGM_YR)="" Or ([A].PGM_YR) Is Null) AND ((.PGM_YR)="" Or (.PGM_YR) Is Null));
Ok, the OR statement here has changed slightly when compared to the example of the code from prior entries. I had once indicated that when my code was like this, the query updated every entry in the field. This time it did not, I am wondering if maybe my query had slightly varied from the one I have above. In any case, I am trying to understand why the code I have ablve works for this field and I can still use the old code for other fields. Like I had said, all fields are text. To me, it seems as if the code above is not necessarily looking for blanks or nulls in both tables and then making the decision to update, it seems as if it's working on a table by table basis. Is my interpritation in the right area, or are the reasons for this having worked different, is so please help me understand why.
WHERE ((([A].PGM_YR)="" Or ([A].PGM_YR) Is Null) AND ((.PGM_YR)="" Or (.PGM_YR) Is Null));
Ok, the OR statement here has changed slightly when compared to the example of the code from prior entries. I had once indicated that when my code was like this, the query updated every entry in the field. This time it did not, I am wondering if maybe my query had slightly varied from the one I have above. In any case, I am trying to understand why the code I have ablve works for this field and I can still use the old code for other fields. Like I had said, all fields are text. To me, it seems as if the code above is not necessarily looking for blanks or nulls in both tables and then making the decision to update, it seems as if it's working on a table by table basis. Is my interpritation in the right area, or are the reasons for this having worked different, is so please help me understand why.
- Status
Similar threads
- Locked
- Question
- Locked
- Question