Help with "Unmatched" query

[Robotron]

I have a type of query which finds unmatched records in a table. The problem is there is a month and year field in the other table which I have to put a criteria on for the latest month and year. If I take off the month and year criteria, I get the results I want and if I put them in, I do not get the proper results.

Any suggestions on finding unmatched if I have to put a 'filter' on the other table?

Robotron

 

[OnTheFly]

It would be helpful to see the SQL statement to determine what the problem is.

 

[RickSpr]

Without knowing how your query is written, nor what results you get, nor what results you want, it's pretty hard to answer this.

If you "filter" the other table, you're obviously going to increase the number of unmatched records. That's the whole point of filtering in this situation. It's doing what you asked it to. So how is what you want different from what you asked for?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Robotron]

Ok...

SELECT tblAccountMap.ParentRR, tblAccountMap.ParentDD, tblAccountMap.Account
FROM tblAccountMap LEFT JOIN tblDetail ON (tblAccountMap.Account = tblDetail.Account) AND (tblAccountMap.ChildDD = tblDetail.District) AND (tblAccountMap.ChildRR = tblDetail.Region)
WHERE (((tblDetail.Region) Is Null Or (tblDetail.Region) Is Not Null) AND ((tblDetail.District) Is Null Or (tblDetail.District) Is Not Null) AND ((tblDetail.Account) Is Null Or (tblDetail.Account) Is Not Null) AND ((tblDetail.Month)=7) AND ((tblDetail.Year)=2004));

 

[Robotron]

When I put the Month and Year criteria in is when I don't get the results I want. If I leave them out of the query, I get the proper results. How can I put the criteria on these two fields and still get correct results?

Robotron

 

[RickSpr]

You still haven't said what you want to get, versus what you're getting.

I can point out something from your query, nevertheless. Expressions of the form "(x) Is Null Or (x) Is Not Null" will always evaluate True, so your WHERE clause could be rewritten as:
WHERE ((True) AND (True) AND (True) AND (tblDetail.Month=7) AND (tblDetail.Year=2004));
or more succinctly:
WHERE (tblDetail.Month=7 AND tblDetail.Year=2004);
I doubt that that's what you intended.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

To expand on what you need to tell us: Do you want, for example, "rows in the first table for which there is no row in the second table, or there is a row in the second table but its Month <> 7 or its Year <> 2004"?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Robotron]

Okay. My apologies on not being more descriptive.

In the first table I have 25, 80, and 113130 for the Region, District and Account.

Since there is no record in the tblDetail for 25, 80, and 113130 for the month '7' and the year '2004', those values should be returned but my current query is not returning them.

Robotron

 

[RickSpr]

You can't do this with an outer join. You have to use a sub-select:

SELECT tblAccountMap.ParentRR, tblAccountMap.ParentDD, tblAccountMap.Account
FROM tblAccountMap 
WHERE NOT EXISTS
    (SELECT 1 
     FROM (tblAccountMap INNER JOIN tblDetail
        ON tblDetail.Account = tblAccountMap.Account
       AND tblDetail.District = tblAccountmap.ChildDD
       AND tblDetail.Region = tblAccountMap.ChildRR)
     WHERE tblDetail.Month = 7
       AND tblDetail.Year = 2004);

Note: This kind of subselect is called a "correlated subquery", because its criteria involve columns from the main query. Correlated subqueries tend to execute slowly, because they have to be executed separately from each row returned by the main query. This means that if tblAccountMap is large, the query may execute too slowly. Account maps are typically only about a hundred records or so, so this is probably not a problem for you.

However, if you find it is too slow, you can do it with a non-correlated subquery. It's just more complicated:

SELECT tblAccountMap.ParentRR, tblAccountMap.ParentDD, tblAccountMap.Account
FROM tblAccountMap 
WHERE tblAccountMap.Account & "|" & tblAccountMap.ChildDD & "|" & tblAccountMap.ChildRR NOT IN
    (SELECT  tblAccountMap.Account & "|" & tblAccountMap.ChildDD & "|" & tblAccountMap.ChildRR 
     FROM (tblAccountMap INNER JOIN tblDetail
        ON tblDetail.Account = tblAccountMap.Account
       AND tblDetail.District = tblAccountmap.ChildDD
       AND tblDetail.Region = tblAccountMap.ChildRR)
     WHERE tblDetail.Month = 7
       AND tblDetail.Year = 2004);

You can also do it with two Access query objects. The first one is like this last subquery except that it selects the individual fields Account, ChildDD, and ChildRR instead of concatenating them. The second query is an ordinary left join of tblAccountMap with the first query, and with the criteria WHERE tblDetail.Account IS NULL. I would have suggested this solution first, except that I assume the month and year are actually parameters to be plugged in, which would be difficult to do in code when using a nested query object.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein