>Hi, all.
>
>The goal is for the resulting table to have one record for each lane that exists in each section of highway. The sections in table2 (which has the lane data) are smaller than those in table1, so there may be several sections in table2 that fall at least partly within a single section in table1. Here is my SELECT, which produces correct results:
>
>SELECT DISTINCT table1.section_id, table2.lane_number FROM data\table1 ;
> INNER JOIN data\table2 ON table2.element = table1.element ;
> WHERE ((table2.beg_odometer>=table1.beg_odometer AND table2.beg_odometer<=table1.end_odometer) OR ;
> (table2.iri_end_odometer>=table1.beg_odometer AND table2.end_odometer<=table1.end_odometer)) AND ;
> table2.survey_year = cYear INTO TABLE data\my_test DATABASE data\temp
>
>I am just wondering if this can be refined to run faster, or if there is some other better approach. In my testing so far, I have about 350 records in table1 and 1.5 million records in table2, and it takes an average of 80 seconds. The full size of table1 will be about 32,000 records... so now we're talking hours. This may be acceptable if it's the fastest way, but I'm hoping that isn't the case. (I haven't worked with tables this large before, so I'm not sure what is a reasonable time to expect.)
>
>Thanks in advance for any advice!

Just a try, but put everything in your JOIN condition :
SELECT DISTINCT table1.section_id, table2.lane_number FROM data\table1 ;
> INNER JOIN data\table2 ON table2.element = table1.element ;
> AND ((table2.beg_odometer>=table1.beg_odometer AND table2.beg_odometer<=table1.end_odometer) OR ;
> (table2.iri_end_odometer>=table1.beg_odometer AND table2.end_odometer<=table1.end_odometer)) AND ;
> table2.survey_year = cYear INTO TABLE data\my_test DATABASE data\temp