Linked Field and Null Values

[Ian Mac]

All,

Introduction (I case I'm doing this all wrong)

I have 1 huge table of data:

SysID
PersonData
"
"
"
"
AddressData
"
"
"
"
"
"
"
"
"
TelephoneData
"
"
"
"
Year (this is 1 or 2)

I'm taking each component Person, Address, Telephone info and making a compacted table of each (i.e. lots of Jane Does etc, appear in both years)
Each table with have a URN

I then need to update the big table with the URN so move onto another part of getting this data into a usable format.

Basically what follows is my question on how to get the URN's back into the table:

-----------------------------------------------------------------------

Is there a quicker way (or a way I'm not seeing) of doing the following.

I have a set of data 'tblDatabaseTable, say:

ID
Field1
Field2
Field3
Field4

In the database.

I have an imported file which will be tblImportTable, same deal without the ID:

Field1
Field2
Field3
Field4

I need to compare the data on each table:

SELECT * FROM tblDatabaseTable
INNER JOIN tblDatabaseTable
ON tblDatabaseTable.Field1 = tblImportTable.Field1
AND tblDatabaseTable.Field2 = tblImportTable.Field2
AND tblDatabaseTable.Field3 = tblImportTable.Field3;

Works great as long as all the fields have values in.

Is there a way to compare the tables and get results as:

Database
Ian MacConnell null

Import
Ian Macconnell null

Match!

without doing something like:

SELECT IIf(IsNull([Field1]),' ',[Field1]),
IIf(IsNull([Field2]),' ',[Field2]),
IIf(IsNull([Field3]),' ',[Field3])
FROM DatabaseTable

SELECT IIf(IsNull([Field1]),' ',[Field1]),
IIf(IsNull([Field2]),' ',[Field2]),
IIf(IsNull([Field3]),' ',[Field3])
FROM ImportTable

before comparing them???

Phew, hope that all makes sense, very long day and I'm........

Cheers,

 

[Jon K]

SELECT *
FROM tblDatabaseTable INNER JOIN tblImportTable
ON Nz(tblDatabaseTable.Field1,"Null") = Nz(tblImportTable.Field1,"Null")
AND Nz(tblDatabaseTable.Field2,"Null") = Nz(tblImportTable.Field2,"Null")
AND Nz(tblDatabaseTable.Field3,"Null") = Nz(tblImportTable.Field3,"Null");


Note: The join is not supported in query Design View, so you can't switch the query to Design View.

In fact, you can use any character(s) instead of Null.
.

 

[Ian Mac]

Thanks Jon,

My problem is the amount of fields in the real data 44!!!!

I can't test until tomorrow but would something like:

UPDATE tblApplicantAddressTelo
INNER JOIN tblApplicant
ON Nz(tblApplicantAddressTelo.Field1,"Null") = Nz(tblApplicant.Field1,"Null")
AND Nz(tblApplicantAddressTelo.Field2,"Null") = Nz(tblApplicant.Field2,"Null")
AND Nz(tblApplicantAddressTelo.Field3,"Null") = Nz(tblApplicant.Field3,"Null")
SET ApplicantID = tblApplicant.ApplicantID;

Work.

Cheers,

 

[Jon K]

Since both tables have an ApplicantID field, you need to tell Access which ApplicantID field to update.

UPDATE tblApplicantAddressTelo
INNER JOIN tblApplicant
ON Nz(tblApplicantAddressTelo.Field1,"Null") = Nz(tblApplicant.Field1,"Null")
AND Nz(tblApplicantAddressTelo.Field2,"Null") = Nz(tblApplicant.Field2,"Null")
AND Nz(tblApplicantAddressTelo.Field3,"Null") = Nz(tblApplicant.Field3,"Null")
SET tblApplicantAddressTelo.ApplicantID = tblApplicant.ApplicantID;


44! Not sure if Access has a limit on the number of links in the ON clause, though.
.

 

[Ian Mac]

Of course, I wrote it out freehand, that's my excuse.

Anyway, 44 yes but not all need to be done each time. It is more the bind of have to write Query1, 2, 3 on all of the fields.

Nevermind, it will be easier than my method.

I wonder if someone has created a mini application for compiling a string of this sort through a form type thingy.
I have to do this type of comparison um-teen times a day and it must be a common problem (unless of course lots of people don't realise the records aren't return in their query).

A side note/information on Nz() queries using this function, being pulled to Excel through MSQuery do not work as it can't complile the data.

Thanks very much Jon, you've been a big help.