2 Replies Latest reply: May 15, 2014 9:57 AM by red221 _ RSS

    Database Joins

    red221 _

      Can anyone tell me why when I do a join between an access database, which was extracted using Monarch, with a customer number and zip code fields to my monarch table I receive a "null" in a few of the zip code fields but if I pull up the database there is a zip code corresponding to that customer number?  I am using version Pro v5.02. I apologize if I have left out any important info as this is my first posting.  smile.gif[/img]

        • Database Joins
          Mike Urbonas

          Red, I am assuming you are joining data from a report with your zip code data in an Access database.  (is that correct?)


          For the join to work, the data in the join column must be identical, including no "extra" spaces after the customer number which you can't see (but Monarch does  smile.gif[/img]  )


          There might be some trailing spaces after some of your customer numbers.  Try this: In your Monarch model, try this calculated field (we'll call it "Trimmed Customer Number"):


          RTRIM(enter name of your customer number data field name here)


          RTRIM is a function that will "trim", or remove, any trailing spaces for a field of data.


          Now redefine your Data Join by specifying the "Trimmed Customer Number" as the Join Column from your report data, to the customer number column in your Access file.  Let me know if this worked.

          • Database Joins
            red221 _

            Mike, thanks for you help, it did get me going in the right direction.  What I figured out was that the trap I was using on the template to pull the customer number & zip out had a problem.  Once I fixed that everything cleared up.  However, I did leave the RTRIM in place since I could see were a user inputting only a 5 digit zip code could put additional spaces in a field sized for a 9 digit zip code-    Red