Data Matching & Not Matching At The Same Time

[EzGoingKev]

I have two data sets - a master table and an application table.

Both tables have multiple fields that differ but have six fields that are supposed to match each exactly.

I wrote two queries:

• master vs application tables -> checks to see if there is anything in the master that is not in the application table
• application vs master tables -> checks to see if there is anything in the application table that is not in the master table

Both queries are joined using the EXACT same fields.

When I do the master vs the application table there is nothing shows up as non-matching.

When I do the application vs the master table I get one return. I have visually reviewed all the data in both tables and it matches 100%.

I cannot determine why it does not match. The thing that really confuses me is why it matches up in one query while the same exact data does not match up in another query.

Anyone know what is going on?

 

[theDBguy]

Hi. Unfortunately, sometimes, what our eyes see are not the same as what the computer sees. For example, what might look like a blank space to you may actually be a space character to the computer. Or, it may look like a space character to you; but to the computer, it's a tab character. So, you'll have to examine each column and make sure there are none of these possibilities happening in there to get an exact match.

 

[jdraw]

Suggest you post some sample data or a copy of the database (zip format). As theDBguy suggests -sometimes we see/don't see things as the computer does.

 

[isladogs]

It would also help to see your two queries

 

[EzGoingKev]

I edited my OP to add that both queries are joined on the EXACT same fields.

If it was something as simple as there was a space (which I already thought of and checked) then the data would not match up in both queries.

 

[theDBguy]

I edited my OP to add that both queries are joined on the EXACT same fields.

If it was something as simple as there was a space (which I already thought of and checked) then the data would not match up in both queries.

Where is the data coming from? The "space" was just an example. There are plenty other "non-printable" characters that could mess with whether data match or not. As others requested, a sample set of data would help us help you determine why you're getting a mismatch.

 

[EzGoingKev]

OK I figured it out.

When I went to pull samples of the data to put in a db to share here I found the data I needed was missing from one data set. This would explain why it was showing up in one query and not the other.

It turned out my boss had updated a field in the table to equal "" where the value was null. The query that built the table I was using used "". The data that I added had a value of null in that field so it was not being pulled in.

Thanks for the replies.

 

[theDBguy]

OK I figured it out.

When I went to pull samples of the data to put in a db to share here I found the data I needed was missing from one data set. This would explain why it was showing up in one query and not the other.

It turned out my boss had updated a field in the table to equal "" where the value was null. The query that built the table I was using used "". The data that I added had a value of null in that field so it was not being pulled in.

Thanks for the replies.

Hi. Glad to hear you got it sorted out. Good luck with your project.