Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Return all records when joined field is null on parent dataset

    I have one table with our GL numbers making up our income statement. I also have a query that will return all activity for a time frame by GL number. I have linked these in a query by their "Prim" gl number and "Sub" gl number and I've set the table up as the "parent" (includes all records from it, only matching for other).

    The problem is that sometimes the "Sub" gl number is null which implies that all GLs with a matching "Prim" number are pulled in the income statement. In Access though it will only pull when both the Prim and the Sub are matching.

    For example, say I had 3 GLs for our Fees category in our Income statement specs table:

    Prim Sub
    745 450
    745 429
    750

    The first two pull correctly, but the third does not return anything because it is looking for a null value in the query I'm querying. Is there any way to set up a join so that if a value is null, it returns all?

    Joshua

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are two possible solutions (I prefer the first one):

    1. You can use a LEFT JOIN to link both tables (Prim LEFT JOIN Sub).
    2. You can add a "OR ISNULL" clause in the WHERE part of the query.
    Have a nice day!

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I already tried the left join. The problem is that when the sub is null it will only join with a null sub on the other table. What I want it to do is return all subs when the sub is null in the parent table.

  4. #4
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I ended up just doing a left join on the Prim number. I then put a formula IIf((IsNull([Sub GL on parent])) Or ([Sub GL on parent]=[Sub]),"Keep","") and set the criteria to "Keep" and it seemed to work. This way is less than ideal because it has to go through all records but until someone can show me another way, it will have to do.
    Last edited by scrtchmstj; 12-20-11 at 11:59.

Posting Permissions

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