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:
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?
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.
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.