closest match to a field...

[marcus.]

Hi

I'm trying to find find the information on the movement of stock at the cloest match to a certain time ..

I have Stock Code and Date/Time
I want to look up the nearest transaction for that stock code around that Date/Time.

ie., if the Date/Time was 12:00 01 January 2004, i want to find the cloest record for movement of that stock.. ie., whether it be one day or two days after...

How can I connect these fields? In excel and the data was inorder of Time, I could use something like

=vlookup('stockcode&date_time','data:stockcode&date_time,transactionRef',2,true

Where true returns the closest match when the records are inorder.... (if you can understand my psudo excel something language up there!). So i'll try and detail my process

for ex..
'stockcode&date_time' = 90005_20040302_120

''data:stockcode&date_time' = a table as displayed below
stockcode&date_time transactionRe
90004_20040302_1023 Ref20
90004_20040302_1544 Ref20
90005_20040302_1023 Ref20
90005_20040302_1045 Ref20
90005_20040302_1333 Ref20

excel would return Ref206

Obviously I've greatly simplified thing (firstly only a few records), have joined fields together (stock code and time stamp) along with converting the time stamp into something which can be decified in numerical order, and sorted the records based on this field. But hopefully you understand what I am trying to achieve

Any ideas
Thanks
Marcu

 

[Allen Browne]

Try entering something like this into a fresh column of your query design
grid:

NearestMatch: ( SELECT TOP 1 [stockcode&date_time]
FROM [stockcode&date_time] AS Dupe
WHERE Dupe.StockID = 2
ORDER BY Abs(Dupe.[stockcode&datetime] - #1/1/2004#), Dupe.transactionRef )

Results will be read-only. The nearest match is the one where the absolute
difference between the 2 dates is the minimum.

In general, you use DLookup() in Access, but DLookup() is not capable of
handling nearest match. There is a replacement in this link that can do
that:
http://allenbrowne.com/ser-42.html
These functions will not make your query ready only, but they will be much
slower than the subquery suggested above.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to find find the information on the movement of stock at the

cloest match to a certain time ...

I have Stock Code and Date/Time.
I want to look up the nearest transaction for that stock code around that Date/Time..

ie., if the Date/Time was 12:00 01 January 2004, i want to find the cloest

record for movement of that stock.. ie., whether it be one day or two days
after...

How can I connect these fields? In excel and the data was inorder of Time, I could use something like:

=vlookup('stockcode&date_time','data:stockcode&date_time,transactionRef',2,t
rue)

Where true returns the closest match when the records are inorder.... (if

you can understand my psudo excel something language up there!). So i'll
try and detail my process:

for ex...
'stockcode&date_time' = 90005_20040302_1200

''data:stockcode&date_time' = a table as displayed below:
stockcode&date_time transactionRef
90004_20040302_1023 Ref203
90004_20040302_1544 Ref204
90005_20040302_1023 Ref205
90005_20040302_1045 Ref206
90005_20040302_1333 Ref207

excel would return Ref206.

Obviously I've greatly simplified thing (firstly only a few records), have

joined fields together (stock code and time stamp) along with converting the
time stamp into something which can be decified in numerical order, and
sorted the records based on this field. But hopefully you understand what I
am trying to achieve.

 

[marcus]

Thanks

I'm playing with it but i cant get it to quite work

I have two tables in access. One with the reference information I want to use

Table1:
Ref# Stock_ID Ref#Tim
001 9001 10/01/2004 10:23 A
002 9002 .....

One Table with transactions which i want to look up

Table2
Stock_ID TransactionTime Ref2
9001 10/012/004 08:00 AM A004
9001 10/01/2004 11:00 PM A004

NearestMatch should look up each record in Table1, the Stock_ID, find the closestmatch between Ref#Time and TransactionTime and return Ref2

ie., for Table1 Ref#001 Ref2# = A004

I'm sorry, i think my first message was perhaps a little too confusing and simplified it to much as I was trying to work out how to do it in my mind! ..
so creating a query which displays the contents of table1, joining with a one to many relationship between Table1.Stock_ID and Table2.Stock_ID and using your text in a fresh column within the query design grid i have???

(I can't work out how to modify your text to do what i want..

thanks again

Marcus

 

[marcus.]

Or perhaps if you could just explain your subquery, and then i can work it out.
but at the moment i can't get my brain around it

NearestMatch: ( SELECT TOP 1 [stockcode&date_time
FROM [stockcode&date_time] AS Dup
WHERE Dupe.StockID =
ORDER BY Abs(Dupe.[stockcode&datetime] - #1/1/2004#), Dupe.transactionRef

thanks Marcus

 

[Allen Browne]

Hi Marcus

A subquery is like a complete SELECT statement in one field of a query.

You say the table to lookup is Table2. Table2 does not need to be in the
main query, so the alias "AS Dupe" is not needed in this query.

Where you want to see the result, it needs to return just one field, and
just one row. The field to be returned is stockcode&date_time. The row you
want is where the value of Table1.[Ref#Time] is closest to
Table2.[stockcode&datetime]. That is the record where the absolute
difference between the two is smallest. We ask for that record by sorting by
the difference, and taking just the TOP 1 record.

If there happened to be two records with the exact same time difference, the
subquery would return 2 records (since Access can't tell the difference)
which is not legal for the subquery. To prevent that, we have to give Access
some way to pick between the two. Including the primary key of Table2 in the
ORDER BY clause guarantees that there will be a difference between them, so
Access will return just one record and we prevent that error.

Given the field names in your reply, the subquery becomes:

NearestMatch: ( SELECT TOP 1 Table2.[stockcode&date_time]
FROM Table2
WHERE Table2.Stock_ID = Table1.Stock_ID
ORDER BY Abs(Table2.[stockcode&datetime] - Table1.[Ref#Time]),
Table2.[Ref#2] )


In general, subqueries are *very* useful:
- Use them when a JOIN is not suitable.
- A subquery in the WHERE clause is very useful, and you can therefore use
them in the Filter of a form. This lets you create incredibly powerful
search forms.

Limitations:
- Results are read-only if you return a value from the subquery.
- Reports based on a subquery sometimes complains about not being able to
handle a "multi-level group by", so you have to stack one query on another
instead.

 

[marcus]

fantasitic!