how to extract the nearest matched data from a table

[Headache]

Hi. I am a new user of Access 2003.
Here I have a problem. I have thought it for 2 days. So far I did not
sort it out.
The question is how to extract the nearst matched data from a table.
For example, I have 2 tables
Table A
Time(A) Price
09:10 10
09:15 15
09:18 18
.. .
.. .
.. .
.. .

Table B
Time(B)
09:10
09:13
09:16
..
..
..
..
..
I want to extract all the values in Price field when Time(A)<=Time(B)
and Time(B)-Time(A) is maximum.

I am a non-English speaker. So hopefully you can understand it.
Thanks

 

[Chris M]

Hi. I am a new user of Access 2003.
Here I have a problem. I have thought it for 2 days. So far I did not
sort it out.
The question is how to extract the nearst matched data from a table.
For example, I have 2 tables
Table A
Time(A) Price
09:10 10
09:15 15
09:18 18
. .
. .
. .
. .

Table B
Time(B)
09:10
09:13
09:16
.
.
.
.
.
I want to extract all the values in Price field when Time(A)<=Time(B)
and Time(B)-Time(A) is maximum.

I am a non-English speaker. So hopefully you can understand it.
Thanks

Hi, Sorry, probably just me being thick, but please could you clarify what
you are asking for.

Did you mean 'and TimeB - TimeA is MINIMUM.

ie you are looking for the price that has a time closest (but before) the
time in table B?

Cheers,

Chris.

 

[Chris M]

Hi, Sorry, probably just me being thick, but please could you clarify
what you are asking for.

Did you mean 'and TimeB - TimeA is MINIMUM.

ie you are looking for the price that has a time closest (but before) the
time in table B?

Cheers,

Chris.

On the basis that my assumption was true, does this help?

select * from TableB,TableA
where TableA.Time >= TableB.Time
and not exists
(
select 1
from TableA as TA2
where TA2.Time < TableA.Time
and TA2.Time >= TableB.Time
)

So we are saying display TableA and TableB where timeB <= TimeA. And there
are no records in TableA with a lower time that is still higher than the
TableB time.

Cheers,

Chris.

 

[Headache]

On the basis that my assumption was true, does this help?

select * from TableB,TableA
where TableA.Time >= TableB.Time
and not exists
(
select 1
from TableA as TA2
where TA2.Time < TableA.Time
and TA2.Time >= TableB.Time
)

So we are saying display TableA and TableB where timeB <= TimeA. And there
are no records in TableA with a lower time that is still higher than the
TableB time.

Cheers,

Chris.

Yes, it is minimum. Thanks a lot.