how to extract the nearest matched data from a table

  • Thread starter Thread starter Headache
  • Start date Start date
H

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
 
Headache said:
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
Click to expand...

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 said:
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.
Click to expand...

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.

:-)
 
Chris said:
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.

:-)
Click to expand...

Yes, it is minimum. Thanks a lot.
 
Back
Top