J
Jonathan S via AccessMonster.com
I have a table that contains account and payment recording info. As an
account can have multiple payments, I am trying to split my accounts out to a
separate table. The account info is allowed to have null values in its
columns, and does. (there are three rows that determine the account "number")
I can achieve this with the an INSERT...SELECT DISTINCT statement. Before I
drop the account columns on the original table, I issue an UPDATE on it,
JOINing it with the new account table, and set the "accountID".
Problem is, while "SELECT DISTINCT" can match null with null to produce
distinct accounts, UPDATE with a JOIN does not see null as equal to null, and
thus I have accountIDs that are null, when there should be a valid accountID
there.
The only way I can get around this is to assign a space to every where I see
a null value in my account info, but this has produced unpredictable results.
This is all on a live system. If I were starting from scratch, it would be no
problem. So, is there a way to split this table and not have null values in
the accountID? Can the split and linking of the two tables be done in one
statement?
Jonathan Scott
account can have multiple payments, I am trying to split my accounts out to a
separate table. The account info is allowed to have null values in its
columns, and does. (there are three rows that determine the account "number")
I can achieve this with the an INSERT...SELECT DISTINCT statement. Before I
drop the account columns on the original table, I issue an UPDATE on it,
JOINing it with the new account table, and set the "accountID".
Problem is, while "SELECT DISTINCT" can match null with null to produce
distinct accounts, UPDATE with a JOIN does not see null as equal to null, and
thus I have accountIDs that are null, when there should be a valid accountID
there.
The only way I can get around this is to assign a space to every where I see
a null value in my account info, but this has produced unpredictable results.
This is all on a live system. If I were starting from scratch, it would be no
problem. So, is there a way to split this table and not have null values in
the accountID? Can the split and linking of the two tables be done in one
statement?
Jonathan Scott