Lookup

Local time
Tomorrow, 01:15
Joined
Mar 23, 2022
Messages
45
I have a final score as follows
BIbNoAthleteNameEventTimingScoringPoint
042DILCHANG N SANGMA100m0:00:09.46eg. 1400
046ANIMA ARENGH100m0:00:09.47
311TWINSTAR LYNGDOH100m0:00:09.48
084JOYTIFUL NARLONG100m0:00:09.49
046ANIMA ARENGH200m0:00:18.90
049CHANAKAM SANGMA200m0:00:18.91eg. 1398
069DROLIWELL SYIEM200m0:00:18.92
070DROWEL SOHKHWAI200m0:00:18.93
074STREAMLET TARO400m0:00:41.97
075WILKA RONGPIEH400m0:00:41.98
085DORALIN RONGHI400m0:00:41.99
047MEANCJI MECHURA R MARAK400m0:00:42.00eg. 1397

I want to lookup for scoring point from the following table.

Points100m200m300m400m500m110mH400mH4 x 100m4 x 200m4 x 400m
14009.4629.5441.9755.0812.2844.8635.841:15.422:47.49
1399-18.929.5541.9855.09-44.8835.851:15.442:47.55
1398-18.9129.5641.9955.1112.2944.8935.861:15.462:47.61
1397-29.574255.13-44.9135.881:15.492:47.67
13969.4718.9229.5842.0255.1412.3044.9335.891:15.512:47.73
1395-29.5942.0355.16-44.9535.91:15.542:47.79
1394-18.9329.642.0455.18-44.9735.911:15.562:47.85
1393-18.9429.6142.0655.212.3144.9835.921:15.592:47.91
13929.4829.6242.0755.22-4535.941:15.612:47.97
1391-18.9542.0855.2312.3245.0235.951:15.642:48.03
1390-29.6342.155.25-45.0435.961:15.662:48.09
13899.4918.9629.6442.1155.2712.3345.0635.971:15.692:48.15
1388-18.9729.6542.1255.29-45.0835.981:15.712:48.21
1387-29.6642.1455.312.3445.09361:15.732:48.27
1386-18.9829.6742.1555.32-45.1136.011:15.762:48.33
13859.5029.6842.1655.3412.3545.1336.021:15.782:48.39
1384-18.9929.6942.1855.36-45.1536.031:15.812:48.45
1383-1929.742.1955.3712.3645.1736.041:15.832:48.51

I dont know how to lookup. Please help me.
 
Your second table is structured wrong.
Each column should be a record. This is Access not Excel
 
Are you sure ,Bob?
On reflection....No. I'm not sure. In fact I believe I've made a silly mistake, so I've deleted my post here.

Thank for bringing this to my attention.
 
agree with Gasman, table design is not normalised. You would need to normalise it first using a union query

You might get away with a dlookup - perhaps something like this

ScoringPoint: Dlookup("Points","tblScoring","[" & Event & "] = " & timing )

but looks like you will have data typing issues as timing in the first table looks almost like a date/time field and text in the other table. May or may not need date delimiters (#) or text delimeters (') and may or may not need to regularise the value since 0:00:09.46 is not a datetime format
 
This is what the query will look like if you normalize the scoring table:

Select Top 1 t1.BibNo, t1.AthleteName, t1.Event, t1.Timing, t2.ScoringPoint
From tblResults as t1 Left Join tblScoring as t2 On t1.Event = t2.Event
Where t1.Timing <= t2.Timing;
 
agree with Gasman, table design is not normalised. You would need to normalise it first using a union query

You might get away with a dlookup - perhaps something like this

ScoringPoint: Dlookup("Points","tblScoring","[" & Event & "] = " & timing )

but looks like you will have data typing issues as timing in the first table looks almost like a date/time field and text in the other table. May or may not need date delimiters (#) or text delimeters (') and may or may not need to regularise the value since 0:00:09.46 is not a datetime format
I am using Date/Time Extended Data Type in office 2021 and i am finding it difficult to compare or convert the time to text/strings.

Can I use normal date time data type for sports timing format as "0:00:09.46"
 
Last edited:
'normal' date time only goes to second, not to fractions of a second.
 
You should probably keep the time as hundredths of a second in an integer and scale it yourself. Then you can divide by 60 to get seconds and the remainder will be hundredths.

But that is neither here nor there. Normalize the table so the lookup will be simplified.
 
It is THEORETICALLY possible for a date/time variable to hold milliseconds, but the Access standard date/time formatting routines will not recognize the fractions of a second and the Now() function will not give you fractional times. Since a DATE variable is a typecast of a DOUBLE, you have 53 bits of mantissa to play with. You need approximately 16 bits for the date (up to a time in the future in I think the 32nd century) and you need 17 bits for the time of day. That leaves 20 bits unaccounted for. If you track time to milliseconds, you have to use another 10 bits. Due to rounding effects, the last 10 bits of the mantissa cannot be used reliably.

I have made this "extended time" work many years ago when dealing with computation of specific network timing issues based on log files (from another source) that contained the times to that precision. I can't show you any code because it was a database I did not own and so could not keep a copy.

You cannot use the standard Access routines at ANY point when converting between formatted text and the date/time variable. Basically, I used some parsing options to split apart the date in dd-mmm-yyyy format OR mm/dd/yyyy format. I could tell which one I had by searching for the dash and slash. After that, I picked out the time in hh:nn:ss.fff format to hours, minutes, seconds, and fractions based on the colons and dots. Then I used simple multiplication and division to build the DOUBLE. Of course, going the other way, the date part is just an integer and the fraction part can be repeatedly divided by the various factors for hours, minutes, seconds, and milliseconds.

As long as you don't send the DATE variables through anything that would try to format the variable, they will retain the fractions and you can even do math on them. But remember, you can NEVER get native Access to handle that fraction of a second.
 
It is THEORETICALLY possible for a date/time variable to hold milliseconds, but the Access standard date/time formatting routines will not recognize the fractions of a second and the Now() function will not give you fractional times. Since a DATE variable is a typecast of a DOUBLE, you have 53 bits of mantissa to play with. You need approximately 16 bits for the date (up to a time in the future in I think the 32nd century) and you need 17 bits for the time of day. That leaves 20 bits unaccounted for. If you track time to milliseconds, you have to use another 10 bits. Due to rounding effects, the last 10 bits of the mantissa cannot be used reliably.

I have made this "extended time" work many years ago when dealing with computation of specific network timing issues based on log files (from another source) that contained the times to that precision. I can't show you any code because it was a database I did not own and so could not keep a copy.

You cannot use the standard Access routines at ANY point when converting between formatted text and the date/time variable. Basically, I used some parsing options to split apart the date in dd-mmm-yyyy format OR mm/dd/yyyy format. I could tell which one I had by searching for the dash and slash. After that, I picked out the time in hh:nn:ss.fff format to hours, minutes, seconds, and fractions based on the colons and dots. Then I used simple multiplication and division to build the DOUBLE. Of course, going the other way, the date part is just an integer and the fraction part can be repeatedly divided by the various factors for hours, minutes, seconds, and milliseconds.

As long as you don't send the DATE variables through anything that would try to format the variable, they will retain the fractions and you can even do math on them. But remember, you can NEVER get native Access to handle that fraction of a second.
Thank you very much.

So now I will still have to use the Date/Time Extended Data Type available in Access 2021
 
I am using Date/Time Extended Data Type in office 2021 and i am finding it difficult to compare or convert the time to text/strings.
Don't use it if you are having hard time dealing with it and you have No idea what is it for.
google first it's purpose, maybe you have wrong idea of it's purpose.
 
Don't use it if you are having hard time dealing with it and you have No idea what is it for.
google first it's purpose, maybe you have wrong idea of it's purpose.
Thanks.
I will try to find out more about it.
 
The date/time data type is a POINT IN TIME. Elapsed time is something different. I prefer to use an Integer or Long Integer and scale as necessary for elapsed time.
 

Users who are viewing this thread

Back
Top Bottom