Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95

    Query trouble with Left Join on' Is Null' condition

    Hi all,
    I have a table ‘TblDirect Database’ which contains as a PK, a serial number, ‘ACC’.
    I have a query called 'QryCrs1' which feeds off another table, in which, a similar serial number field exists-also called ACC. A number of identical serial numbers appear in both tables.

    I am trying to run a second query that shows me what ACC serial numbers exist in the table 'TblDirect Database', but don’t appear in the query 'QryCrs1'. I am using the following SQL;

    Code:
    SELECT [TblDirect Database].ACC
    FROM [TblDirect Database] LEFT JOIN QryCrs1 ON [TblDirect Database].[ACC] = QryCrs1.[ACC]
    WHERE (((QryCrs1.ACC) Is Null));
    Oddly, no data appears. Awkward, as I know that the data exists. E.g. if I run the query;

    Code:
    SELECT [TblDirect Database].ACC, QryCrs1.ACC
    FROM [TblDirect Database] LEFT JOIN QryCrs1 ON [TblDirect Database].ACC = QryCrs1.ACC;
    I get two columns, [TblDirect Database].ACC which is full and QryCrs1.ACC which is partially populated, thus I know there are ACC serial numbers that exist in TblDirect Database that match ACC serial numbers in QryCrs1, and that there are ACC serial numbers that exist in TblDirect Database that don't appear in QryCrs1. The query should work! Am I doing something wrong? In other examples, this has always worked for me-even with the 'Is Null' condition? If anyone can identify the error, I would be grateful.
    Regards,
    Mattbro

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Maybe they are Nulls, perhaps zero length strings or blanks.

    Show us the SQL of your query.

  3. #3
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Right-the SQL for QryCrs1 is;
    Code:
    SELECT [Field2] & [Exp1] & [Field4] & [Exp2] & [Expr1] AS ACC, Book1.Field2, Symbol.Symbol AS Exp1, Book1.Field4, Symbol.Symbol AS Exp2, Format([Field5],"0000000") AS Expr1, Book1.Field7, Book1.Field9
    FROM Book1, Symbol
    WHERE (((Book1.Field2)="GY"));
    ACC is 'constructed' from the source table 'Book1' and a second table 'symbol' in order to bring the 'Book1' ACC serial numbers into the same format as the ACC values in TblDirect Database. Any thoughts?
    Mattbro

  4. #4
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Strangely, if I export the data from QryCrs1 to Excel, import it to a new table QryCrs2 and then run the query,
    Code:
    SELECT [TblDirect Database].ACC
    FROM [TblDirect Database] LEFT JOIN QryCrs2 ON [TblDirect Database].[ACC] = QryCrs2.[ACC]
    WHERE (((QryCrs2.ACC) Is Null));
    ....it works....it must be something to do with the query structure. Any ideas anyone?
    Mattbro

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your sql for QryCrs1 from post #1 reformatted is
    Code:
    SELECT [Field2] & [Exp1] & [Field4] & [Exp2] & [Expr1] AS ACC
    , Book1.Field2
    , Symbol.Symbol AS Exp1
    , Book1.Field4
    , Symbol.Symbol AS Exp2
    , Format([Field5],"0000000") AS Expr1
    , Book1.Field7
    , Book1.Field9
    FROM Book1, Symbol
    WHERE (((Book1.Field2)="GY"));
    There is no field that relates Book1 to Symbol that I see. This would give "Cartesian product".
    Why do you have Symbol.symbol as Exp1 and Exp2? Just curious.

    What do you get when you run QryCrs1 by itself?

    What is [Table Direct] and how does it relate??

  6. #6
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    What I am doing is taking data off of one system via the source extract [Book1] and converting the ACC serial number so it matches the format used on another system, from the Source [TblDirect Database]. The difference requires the use of some symbols which are employed in the latter. (If i knew what I was doing, I would probably be able to incorperate it directly into the query rather than the long-winded method of creating a very small table with a symbol in it.....I am using a third table called "Symbol" which contains said symbols, and am referring to it in the query in order to generate the number. I am then ultimately looking at the query that tells me what is in the first system that isn't in the second (and visa versa). If I run QryCrs1, I get the list of serial numbers from the first system with the first row being the chopped and reconstituted data to match the format of the second system (as ACC).....

  7. #7
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Yes. I should do away with the symbol table and use an expression e.g. Exp2:"-". Thats a lesson learnt. Doesn't cure the problem though. Does access look at concatenated fields differently? Surely the 'Is Null' rules would work normally??

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about showing us a record as in Book1, and the same record as you need to reconfigure it for comparing with Table Direct.

  9. #9
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Right you are. As you can see, the ACC number on Book1 is chopped into relative parts as it is imported into the DB.TBLDirect Database Field1 Field2 Field3 Field4 Field5 Field6 ACC Field8 Field9XXX 1234567 1234 kk GY-13-0001234 01-Jan-14 Book1 Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field90 GY 20 14 1234 , 03-Jan-14 , Info 00GY20130001234

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Code:
    TBLDirect Database 
    Field1 Field2 Field3 Field4 Field5 Field6 ACC Field8 Field9
    XXX 1234567 1234 kk GY-13-0001234 01-Jan-14 
    Book1 
    Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9
    0 GY 20 14 1234 , 03-Jan-14 , Info 00GY20130001234
    I've tried to take your unformatted info and make sense of it.

    I don't know what goes with what.

    Can you use code tags and put some commas in to separate the field values?

    code tags keep your formatting. They are [c o d e] and [/ c o d e] without the spaces.

  11. #11
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Ah-I haven't used tags, as this isn't code. It's a snapshot of the two different record sources as requested.
    "a record as in Book1, and the same record as you need to reconfigure it for comparing with Table Direct."
    I appreciate it's not clear, but I don't know what it would reveal. I just copied and pasted from the linked tables......

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Well what I'm seeing is a list of fields Field1 Field2 etc and then a bunch of data with no clear indication of what data goes with what field.

    We need to find out what is the same in each record if you plan on some sort of join.

    The code tags keep all intenal tabs, spaces etc to try and align whatever you post.
    In your data I see 1 record that says 01-Jan-14, and the other record shows 03-Jan-14.

    Are you telling us that the same record in book1 gets palced into [Table Direct] and the date has changed?

    You know your data and we don't. If we are to help you, you're going to have to hel[p us understand your data.

  13. #13
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    I see what you mean, however the data is confidential, which is why I focused on the relative fields and left the others empty, hence the gaps. The only data I am manipulating as far as this database goes, is the serial number which manifests as;
    '00GY20130001234' in Book1 and
    '
    GY-13-00001234' in TblDirect Database.
    Book1 is created as an excel file. I import it into the database by changing it to a .csv file, with the serial number in the first column. I import it as fixed length which allows the serial number to be dissected into Field 1 (00), Field 2 (GY) Field 3 (20), Field 4(13), Field 5 (00001234), and the remaining fields which are largely ignored.

    QryCrs1 reassembles these fields into the same format as that used in TblDirect Database. The Data in TblDirect Database is also imported from a .csv file straight into the TblDirect Database. So then I am where I started this thread-a Left Join to see what exists in TblDirect Database that doesn't in QryCrs1, and visa versa. The data is comparable and if I miss out the Is Null statement, I can see both lists, and the gaps in QryCrs1 where the serial number exists in TblDirectDatabase but not in QryCrs1. As soon as I put 'Is Null' into QryCrs1.......nothing!

    (Please ignore the discrepancies in dates-that's just me doing a botch job of anonymising confidential data!)

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a mock up of the vba involved to do the transformation. Copy this and try it.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : mattbo
    ' Author    : Jack
    ' Date      : 25/01/2014
    ' Purpose   : Routine to reassemble/reformat a code.
    '
    'https://www.accessforums.net/queries/query-trouble-left-join-null-condition-41008.html#post209252
    'The only data I am manipulating as far as this database goes, is the serial number which manifests as;
    '00GY20130001234' in Book1 and
    'GY-13-00001234' in TblDirect Database.
    'Book1 is created as an excel file. I import it into the database by changing it to a .csv file,
    'with the serial number in the first column. I import it as fixed length which allows
    'the serial number to be dissected into
    '  Field 1 (00)
    ', Field 2 (GY)
    ', Field 3 (20)
    ', Field 4(13)
    ', Field 5 (00001234), and the remaining fields which are largely ignored.
    
    '---------------------------------------------------------------------------------------
    '
    Sub mattbro()
          Dim Bk1_Serial As String
    10    Bk1_Serial = "00GY20130001234"
          Dim sTarget As String
    
    20       On Error GoTo mattbo_Error
             
          ' Parse the book1 serial
          ' Positions 3 and 4, "-", positions 7 and 8, "-" and 9 thru 15 with extra 0 in left
    30    sTarget = Mid(Bk1_Serial, 3, 2) _
                  & "-" _
                  & Mid(Bk1_Serial, 7, 2) _
                  & "-" _
                  & Format(Mid(Bk1_Serial, 9, 7), "00000000")
    40    Debug.Print sTarget
    50    MsgBox Bk1_Serial & "  becomes " & vbCrLf & sTarget
              
    
    60       On Error GoTo 0
    70       Exit Sub
    
    mattbo_Error:
    
    80        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure mattbo of Module AWF_Related"
    End Sub
    I do have some questions for you.
    You say you import, but do you clear out the data before you import the next time?
    Is the Table Direct data cleared out before you do the next updates/appends?
    Have you considered just linking to the Excel file and not importing it into Access?

    Your data may be confidential to you, but it is quite unintelligible to most of us. If you could supply us some csv data, we can work on your JOIN issue.

    Good luck.

  15. #15
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Hello Orange,

    Technically, both data sources are ‘linked’ files.

    The Data that is imported for the TblDirect Database is automatically generated as a .csv file daily, placed in the same folder with the same name. I have it linked to the Db, so even though it is replaced, all the user has to do is press the button which I have coded to run an append query to TblDirect Database. TblDirect Database has to remain intact and mustn’t be cleared.

    The data for book1 works on the same linked file principle except the user has to convert it to a .csv and place it in the same folder for QryCrs1 to pick it up as a linked file. Again, an on_click event runs QryCrs1. There is currently no clear out of data from book1. Until last week, some poor soul was manually printing the data that is Book1 and comparing it visually to a printout of the daily .csv file! I pointed out that it could be done by altering the book1 data so the ACC numbers matched by writing new queries in the existing Db. Until I get this second query to work, I hadn’t thought about data clearout.

    My apologies regarding the unintelligible data. If I may, on Monday, I will see about anonymising the two .csv files and printing up a much clearer outline?

    I have worked with SQL a little, and VB...but this code that you have provided-sorry to sound naive, but where in the grand scheme, does it live? Could I place it in an on_click event to a button?
    Thanks for your patience and input,
    Mattbro

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-23-2014, 03:07 PM
  2. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  3. Replies: 8
    Last Post: 11-04-2011, 06:52 AM
  4. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  5. Outer left join leaves out null values
    By mschles4695 in forum Queries
    Replies: 1
    Last Post: 12-22-2010, 11:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums