Join empty fields (1 Viewer)

jaryszek · Jan 9, 2019

Hi,

i have query like:

Code:

SELECT Table1.Field1, Table1.Field2, Table2.ID2
FROM Table1 LEFT JOIN Table2 ON (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1);

Problem is that Field 1 it is empty so result of a join i (nulls for ID2 where i want to have values).

How can i join also empty fields in order to get ID2 for each record, not with blanks like in query attached for ID2

In attachment please find sample database.

Best,
Jacek

theDBguy · Jan 9, 2019

Hi. Would a RIGHT JOIN work in place of a LEFT JOIN?

isladogs · Jan 9, 2019

theDBguy said:
Hi. Would a RIGHT JOIN work in place of a LEFT JOIN?

No it would just create a different problem

Jacek
I'm not sure I see the point, especially as both tables are identical, but this will work

Code:

SELECT Table1.ID1, Table1.Field1, Table1.Field2, Table2.ID2
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2;

or this

Code:

SELECT Table1.ID1, Table1.Field1, Table1.Field2, Table2_1.ID2
FROM Table2 AS Table2_1 INNER JOIN (Table1 LEFT JOIN Table2 ON (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1)) ON Table2_1.ID2 = Table1.ID1;

Result:

jaryszek · Jan 10, 2019

theDBguy said:
Hi. Would a RIGHT JOIN work in place of a LEFT JOIN?

Hi,

no it can not be.

Code:

SELECT Table1.ID1, Table1.Field1, Table1.Field2, Table2.ID2
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2;

this is using IDs, i can not use them, i have disctintrows, not ids.

Code:

SELECT Table1.ID1, Table1.Field1, Table1.Field2, Table2_1.ID2
FROM Table2 AS Table2_1 INNER JOIN (Table1 LEFT JOIN Table2 ON (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1)) ON Table2_1.ID2 = Table1.ID1;

this is working, thank you.
How this is working?

Colin take your data from syncDataExample and create empty fields in both tables.
you will see the same issue while joining tables.

This i need to get exact corresponding ID from ExcelImportTable.

jaryszek · Jan 10, 2019

ok this is working by for ID...
what if i do not have IDs? (i have only distinctrows)

i want to join fields (it can be inner join) with empty fields.

this sql:

Code:

SELECT Table1.ID1, Table1.Field1, Table1.Field2, Table2_1.ID2
FROM Table2 AS Table2_1 INNER JOIN Table1 ON (Table2_1.Field2 = Table1.Field2) AND (Table2_1.Field1 = Table1.Field1);

will show only one record. And this is not true because all records are the same. But they have blanks and access do not see this.

I think that i should add one more critieria somehing like Table2_1.Field2 = Table1.Field2 or Table2_1.Field2 is Null or Table1.Field2 is null

isladogs · Jan 10, 2019

Sorry Jacek but you've lost me.

You gave an example with two identical tables each of which contains an ID field. Then you say you can't use the first solution given because it uses ID fields which you don't have!?!

You say the second solution works but I think you then say it's still no good as you have empty fields. Are these empty strings or null?

You cannot equate a null to anything, not even another null.
So trying to join two tables where the joint fields have null values will fail.

Also DISTINCTROW or DISTINCT apply to queries not tables

Have I lost you as well?

jaryszek · Jan 10, 2019

thank you Colin,

sorry for mess.

You cannot equate a null to anything, not even another null.

this is not good. Because i am receiving empty fields from Excel. These are text fields.
And this is hard to handle it. How to join and get appropriate data if i can not identify the same rows (rows are the same even there are nulls).

Maybe i should first put some string there, i mean update tblExcelTable and put "No value" instead of null there.
But this seems to be workaround and not efficient.

Best,
Jacek

isladogs · Jan 10, 2019

How about using Nz(FieldName,"") instead

jaryszek · Jan 10, 2019

you mean something like FieldName: Nz(FieldName,"") ?

Best,
Jacek

isladogs · Jan 10, 2019

Yes but you'll need to use a different name for the alias

plog · Jan 10, 2019

When I want to do fuzzy/Null matching I build connecting keys. First you build a query for each individual datasource and create a calculated field that concatenates all fields that will be used for matching:

Table1ConnectionQuery
Field1, Field2, Key
2, 1, "~2~1~"
Null, 2, "~~2~"
Null, 3, "~~3~"

Table2ConnectionQuery
Field1, Field2, Key
2, 1, "~2~1~"
Null, 2, "~~2~"
Null, 3, "~~3~"

The Key field is simply this

Key: "~" & Field1 & "~" & Field2 & "~"

The special character helps to delimit the individual field values so that 1, 102 and 110, 2 don't accidentally match.

Then, I build another query using those 2 queries joining them via their Key fields. That way, since I want to match on Field1 and Field2 and Field1 is sometimes Null the match is still made.

isladogs · Jan 10, 2019

That's an interesting approach which I haven't tried.

Another method is to do the following (taken from my website article)

However, it may be safest to manage NULL values by first setting them to valid but unused values in each field of the import & destination fields

For example, update null dates to 01/01/9999 and number fields to an unlikely value such as -1000000
You will need to do this for each field separately in the two tables. For example:

UPDATE tblData SET tblData.StartDate = #1/1/9999# WHERE (((tblData.StartDate) Is Null));
After completion run a further update query to revert the modified values in the destination table back to null.

Once again do this for each field separately. For example:

UPDATE tblData SET tblData.StartDate = Null WHERE (((tblData.StartDate)=#1/1/9999#));

jaryszek · Jan 11, 2019

thank you Guys!

Best,
Jacek

Join empty fields (1 Viewer)

jaryszek

Registered User.

Attachments

theDBguy

I’m here to help

isladogs

MVP / VIP

Attachments

jaryszek

Registered User.

jaryszek

Registered User.

isladogs

MVP / VIP

jaryszek

Registered User.

isladogs

MVP / VIP

jaryszek

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

isladogs

MVP / VIP

jaryszek

Registered User.

Similar threads

Users who are viewing this thread