Query compared to another table

[Petox]

Hello!

I'm having a lot of trouble trying to figure this out, because I'm so green I don't know the right question to ask.

Can I pull the records from a table, where values in field C are less than any values in field C of another table?

Any help is appreciated.

[June7]

Do these tables have a relationship? Show examples of raw data and desired output.

[Petox]

I do not believe the tables have a relationship, other than that the field names are the same.


Table 1

	Field1	Field2
1	Bob	25
2	Heidi	30
3	Frank	88
4	Frodo	49
5	Batman	41

Table 2

	Field1	Field2
1	Blah	55
2	Blah	43
3	Blah	80

Result

	Field1	Field2
1	Bob	25
2	Heidi	30
5	Batman	41

For me, this is a question of logic as a precursor to a much more complicated question. If this isn't possible then it rules out trying to solve the much larger issue.

The larger issue in a nutshell: I have a table of 4.1 million records which I need to filter down to less than 25 miles of any record in another table (about 800 records). I do this in Excel now with a spherical trigonometry formula, but am seeking ways to do this within a database. Exporting a quarter of a million records, and then using SUMPRODUCT to identify if each record is within x miles of the other 800 is extremely time consuming and pretty harsh on my processor.

If unlinked table comparisons isn't an option, then I guess I just need to deal with long process times and maybe buy a high-end processor. I'm not above locating commercial/freelance services to do this, it's just that I have no idea where to start.

[ssanfu]

PMFJI,

Try this:

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2)<(SELECT Top 1 Min(Table2.Field2) AS MinOfField2 FROM Table2;)))
ORDER BY Table1.Field2;