Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Linking Null Values

    Is there a way to link 2 tables by fields that may contain null values?

    The 2 solutions I am currently using is to either update the tables with "NoValue" when it is empty or to use an iif statement with Nz(table1.field5,"NoValue")=Nz(table2.field5,"NoVa lue").

    Neither solution is working well for my current situation as I have 1 table which is linked so I can not update it and I need to check around 30 contact info fields for changes and that is a very bulky iif statement.

    Any help anyone has would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Linking table only on Null vlues does not seem a particularly good way to link tables!

    I may be wrong (again) but doesn't this 'join' give you a cartisian product ? (ie every Null value in table1 is matched every Null value in table2)

    If that is the case and that is what you want, then perhaps this will do it for you

    SELECT FiledList FROM table1,table2
    WHERE (table1.field5 Is null) and (table2.field5 Is Null)

    Just a thought.

    Is 'field5' the only linking criteria ?


    MTB

  3. #3
    Join Date
    Jan 2006
    Posts
    47

    Thank you for the response...

    I don't think the way I have worded this earlier was correctly detailing the problem.

    I am comparing a maintained list of reservations with a new complete list of reservations. I am currently linking on the reservation no and looking for new reservations and cancelled reservations.

    The ones I am having problems on is the modified reservations. There is a large number of fields in the table (last name, first name, address, etc.) and the only required field is the last name.

    If all the fields had values I could link last name to last name, first name to first name etc... and the modified would pop out like the cancelled and new.

    Since null values are possible I have to link the tables by the reservation no and do an

    iif(table1.last name = table2.last name and Nz(table1.first name,"NA") = Nz(table2.first name, "NA") and ....,"NotModified","Modified")

    Since there is a large number of fields the above iif statement is long and hard to maintain.

    This problem comes up a lot for me so if there is a better way I would really appreciate any direction.

    Thank you!

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think some more info on your table design is required.

    Do you have a primary key and foreign key, if not then pehaps a redesign is necessary.

    How are the 'modifications' registered?


    MTB

  5. #5
    Join Date
    Jan 2006
    Posts
    47
    The reservation no is the closest thing to a primary key but it isn't exactly. I am not sure what a foreign key is but I doubt there is one.

    The table I am receiving is essentially a data dump that I have no control over. I am essentially trying to figure out the new reservations, the cancelled reservations and the modified reservations for the previous weeks data dump. For this reason I don't have much control over the table design.

    The modifications could be something as simple as the last name was Smith and now it is Smiths or it could be a completely new name or address. The reservation no is more of a bucket used to hold names then something signifying a distinct reservation if that makes sense.

    I hope this answers your question and thank you again for your responses.

  6. #6
    Join Date
    Jul 2005
    Posts
    39
    If I read your post correctly, you are trying to compare tblOldVersion with tblNewVersion to find modified rows (tuples). Have you tried using the find duplicates query wizard? This query would give you what has not been modified. Now use this query to compare against the tblOldVersion should provide the difference or what has been modified.

    Alternatively, in two new queries/tables concatenate all 30 fields of each tables and then in a third query, join these two on this single field ought to point to those that are not equal.

    You also seem to indicate that reservation no acts as a temporary placeholder until reservation is confirmed. If so, if in tblOldVersion it has a temporary placeholder in reservation no, and then in tblNewVersion only reservation no field changes to whatever makes it a confirmed reservation, you will count this row as modified, yes?

  7. #7
    Join Date
    Jan 2006
    Posts
    47

    Thank you!

    Taurus,

    Thank you for your response.

    The duplicate query would go wrong because the null values. I haven't used it in a long time but from what I remember it finds dulplicates by linking the the fields.

    I like the concatenate Idea. There is a couple fields that have to have values so the new "key" would have values.

    The reservation no is a placeholder because in the table there can only be one them. It is a bit problematic though because every single other field could change but that number would not.

    Thank you again for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •