Identify records in one table that share values with at least one record in another table.

[JonathanOz]

I have two tables: One contains counts data for state highways. The other contains information on a segmentation scheme for each of the highways. Both tables contain a variable entitled Route Number. Every possible Route Number is in Table One. Only record numbers for routes where segments have been defined are in Table Two. The two files are each sorted by Route Number, and values of Route Number repeat themselves in each file.
I want to create a Boolean Variable (True/False) in a query that will indicate for each record in Table One whether its value for Route Number exists in any record in Table Two
An Example may prove helpful

Table One	Table Two
Route Number	Route Number
2	5
2	5
5	10
5	10
5	105
10	710
10	710
10
10
91
105
710

Result Query
Value of Route Number from Records inTable One	Boolean Variable	Reason for Value of Boolean
2	False	No Record in File Two has Route Number=2
2	False	No Record in File Two has Route Number=2
5	True	At least one record in File Two has Route Number=5
5	Tue	At least one record in File Two has Route Number=5
5	True	At least one record in File Two has Route Number=5
10	True	At least one record in File Two has Route Number=10
10	True	At least one record in File Two has Route Number=10
10	True	At least one record in File Two has Route Number=10
10	True	At least one record in File Two has Route Number=10
91	False	No Record in File Two has Route Number=91
105	True	At least one record in File Two has Route Number=105
710	True	At least one record in File Two has Route Number=710

How can I create the result query?

[June7]

Options:

1. involves at least 2 queries
SELECT [Route Number], Count(*) AS RecordCount FROM table2 GROUP BY [Route Number];

Now build another query that joins the above query to table1 - join type "Retrieve all records from table1 and only those from query that match"

2. involves domain aggregate function in 1 query
SELECT *, IIf(DCount("[Route Number]", "table2", "[Route Number]=" & [Route Number])=0, False, True) AS RecordCheck FROM table1;

Or if you want to show the count:

SELECT *, DCount("[Route Number]", "table2", "[Route Number]=" & [Route Number]) AS RecordCount FROM table1;