Results 1 to 6 of 6
  1. #1
    smithy540 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    3

    Query missing data when fields are null

    Hi,



    I'm trying to make a query which pulls up some fields. However, some of the fields have missing data, it seems that if any of the fields are blank then it doesn't show in the query.

    How can I make the query show all the data, even if it is blank? I've looked into Is Null, but it still isn't working.

    Thanks!

    Smith

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not much to go on. One table involved? Multiple tables that are inner joined? Outer joined? Are you using criteria against the fields that contain nulls?
    Isn't working doesn't help much either. Maybe you need criteria on one row of the design grid, and Is Null in the same field in one of the Or rows.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    smithy540 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    3
    I am trying to pull data into the query from 3 different tables. They are linked together with the Lookup Wizard, looking up an ID field from the previous table. The problem is, the data only shows in the query if all 3 of the fields have a value.

    For instance, we have a part and it has an ID number. Then we do some manufacturing operation (heat treatment) to the part and it gets a new ID number. The part ID to link them is Looked Up from the first table in a field with Lookup Wizard. Then we do another operation to it, and it gets another new ID, and the second ID (heat treatment) is looked up from the second table. However, sometimes parts aren't heat treated and this field is blank.

    Haven't put any criteria into the query, I just want to show all of it. If I put Is Null into the Or rows it shows nothing at all.

    Thanks!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds like you didn't keep the criteria part in the first row. If I put Is Null into the Or rows it shows nothing at all. Maybe not the case.
    I don't understand what a lookup wizard is, as odd as that may seem. I guess when you get more used to doing things without them, they become unfamiliar. Almost sounds like you might be using lookup fields, which is not advised.

    Without seeing the query design or understanding more about the data and the relationships, it's difficult to trouble shoot. I can't tell if the 2nd and 3rd tables have any related data in them as there are no examples in your posts. You cannot pull data from other tables unless there's something common between them. This may be as simple as fixing the joins, which you didn't answer as to whether they are inner or outer. In query design view, double click on the join between tbl1 and tbl2. You probably need either option 2 or 3, but not 1. Can't tell you without knowing more, but if you read the options carefully you may be able to decipher them. Based on your explanation of the process, I suspect it's #2.

  5. #5
    smithy540 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    3
    Thanks Micron! The query is working well now! I changed it to type 3 join, which seems it is right outer join.

    However, you mentioned that using lookup fields isn't advised. I am using lookup fields to make the relationship. What is the other way to do it? I use the lookup field because I want to select the part from Table 1 in a drop down box in Table 2.

    At the moment, I make a Part name in Table 1. Then in Table 2, I want to add different Heat Treatments to that Part, so I use a lookup field to bring Part from table 1 into table 2. Is this method incorrect?

    Thanks!

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad I was able to help!
    What is the other way to do it?
    It's considered less than ideal practice to work directly in tables. Users shouldn't see them. You might be the only user now, which would minimize the potential for damage, but one day you won't be? Best practice is to work in forms. I suggest creating the query for any form and ensuring it performs as expected (allows edits, deletes if that's what is needed) or at least returns the correct data before basing a form or report on it. I also believe in basing forms/reports on queries, not tables. A new requirement in a form or report is usually much easier to manage from a query than a table, and a query allows you to trim record lists rather than dragging every record from a large table into a form when you often don't need all of the records.

    See http://access.mvps.org/access/lookupfields.htm re lookup fields. As for multi-value fields,
    - they are not supported by other RDBM's (databases such as MySql, SqlServer, etc.)
    - what you see in these fields is actually a comma separated list that exists in a table that you can't see)
    - any queries you already have will have to be recreated if you include them
    - IIRC, some queries can't use them (those that use aggregate functions perhaps - memory fuzzy)
    - if you don't know how to reference a specific value from a multi value list, you can't use criteria on them in queries (not saying impossible, just less intuitive to most people)
    That should be enough to dissuade anyone in most cases.
    Some would say they violate a basic requirement of normalization (one value per field). Others say not so - the normalized data is in the hidden table.
    I say po-tay-toe, po-tot-toe. I've listed enough reasons to not use them, IMHO, without debating normalization.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Averaging fields with missing data
    By mfred1 in forum Queries
    Replies: 2
    Last Post: 03-19-2015, 08:32 AM
  2. Replies: 2
    Last Post: 03-05-2015, 01:43 PM
  3. Missing data entry fields in my subform
    By Nashskye13 in forum Forms
    Replies: 7
    Last Post: 07-30-2012, 03:22 PM
  4. Missing data when adding contents of two fields
    By Jamescdawson in forum Forms
    Replies: 3
    Last Post: 03-08-2012, 09:39 AM
  5. make query fields not display if null data
    By rivereridanus in forum Queries
    Replies: 1
    Last Post: 07-05-2011, 08:19 AM

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