Query to include all records from one table, even if they dont match criteria

[jkobsjr]

I have an input table that I want to query against another table.

Column 1 of input table is an ID that matches with a column in the 2nd table. Column 2 is a number that should fall between two numbers (columns 2 and 3) in the 2nd table to further select a specific row in the 2nd table.

It works great if the number in column 2 of the input table falls within a set of the two numbers. If it doesn't, that ID does not show up in the query. I have tried the outer join to have all records of table 1 show up but since I am using the criteria,

"Between [tbl2]![col2] and [tbl2]![col3]"

it doesn't show anything if the input data is not between those two numbers.

Here's an example. Since 11 is outside the ranges for D in table 2, nothing shows up in the query results.

Table1
Col1 Col2
D . . . .11

Table2
Col1. . Col2 . .Col3
D. . . .1 . . . 6
D. . . .7 . . . 10
K . . . 737 . . 272
...

I somehow need to query table2 to find the minimum of column2 and maximum of column3 and then if table1 column2 is Not Between that range to show that there was no match. I am not sure how to do that so any help would be appreciated.

[Seth Schrock]

In the query design view, double click on the line that joins the two tables. Select the option that shows all the data from the table that you are wanting and only the data where the second table matches. I think that this will do what you are looking for.

[jkobsjr]

Seth,

I have tried that but it still will not show a Row for a particular ID in table 1 if the criteria "between" is not met.

[Seth Schrock]

Then you may have to do it as a subquery. Create a query on tbl2 that has your WHERE clause and then create a second query that combines the previous query with the other table.

[Rabbit]

For the outer join to work, you need to put that criteria in the join condition and not as a filter condition. You will have to do that through the SQL view because it is a complex join that is not supported by the GUI.

[NeoPa]

An alternative approach is to ensure that the criteria is such that what you want to show will never fail. When the record from the right side of a LEFT JOIN is absent then all of its fields will be Null.

IE.

([L].[X] Between [R].[Col1] And [R].[Col2]) OR ([L].[X] Is Null)

or alternatively :

(Nz([L].[X],[R].[Col1]) Between [R].[Col1] And [R].[Col2])

The latter being less wordy but also less efficient if that matters ;-)

NB.
In this example [L] -> Left table & [R] -> Right table.