Replicating and Excel INDEX - MATCH with DLOOKUP

[Richey1977]

Hi. First-time poster. I’m hoping someone can help.



I have built a results database for my running club in Microsoft Excel. It works pretty well, and I’ve got it to perform all sorts of functions – club records, personal best etc. Fairly easy stuff. With some reference to forums like this, I then used the INDEX and MATCH functions to provide an ‘age-graded’ scores, by looking up data from two axis points on a separate table.

I’d like to convert my excel workbook to Access now – because there are thousands of records, and I’m finding Excel a bit unstable, and it’s less practical to use when there is so much data on the screen. I’ve replicated everything, but I can’t do the INDEX and MATCH lookup on Access.

Here’s the scenario:

Every runner’s race result is listed in records which identify the runner’s name, date, event, distance, and finishing time. A bunch of lookups then fetch the runner’s gender and age on the date of the event (in this example male of 38 run a 10k race). The INDEX and MATCH is then used to look at a table of world records for each gender, age and distance, and then return a score based on the percentage of the world record that was achieved by our runner (so the WR for a 38y-o male over 10k is 28:00. Our man did it in 38:18, so it returns a score of 73.11%

These age-graded percentages are vital to us because we can than assess ourselves against this single criteria, which equalises the athletic differences of age and gender.

Can someone give me a head start on this (excuse the pun)? I think I need to use the DLOOKUP function, but as far as I can see, the DLOOKUP function requires definite fields in the lookup array to work – whereas the ‘distance’ field to lookup will vary in each case, depending on the type of race that was completed… so I can’t get this to work.

[ranman256]

The excel layout vs normalized db wont be the same.
In acccess you'll have table:

tRace
----------
RaceID
RaceName
place
RaceDate
Distance

tRaceDtls
-----------------
RaceID
Gender
Time
Age

tRecords
--------------
RaceID
Age
Distance
Time

tRunner
---------
Name
BirthDate
Gender

tRunnerRaces
-----------------
RaceID
time

THEN you can build links to races/records etc.

[Richey1977]

Hi - Thank you - I've already done all that. What I need is lookup a table of world record standards, where the age is the X axis, and distance is the Y axis - obviously the age and distance will match those in the race results record.

[June7]

Did you try a query that joins tables on the Age fields? What about gender? Don't see that field in WorldRecords. That would mean a compound link in query on two fields.

If you want to use DLookup dynamically, the values in Distance field would have to equate to field names in WorldRecords.

DLookup("[" & [Distance] & "]", "WorldRecords", "Age=" & [Age at event] & " AND Gender='" & [Gender] & "'")

Be aware domain aggregates can be slow performers in queries and textboxes. Table/query join would be better.

[Richey1977]

Did you try a query that joins tables on the Age fields? What about gender? Don't see that field in WorldRecords. That would mean a compound link in query on two fields.

If you want to use DLookup dynamically, the values in Distance field would have to equate to field names in WorldRecords.

DLookup("[" & [Distance] & "]", "WorldRecords", "Age=" & [Age at event] & " AND Gender='" & [Gender] & "'")

Be aware domain aggregates can be slow performers in queries and textboxes. Table/query join would be better.

Thank you, June7. There are two tables of records to seperate the genders. My plan (carried over from Excel) was to wrap the whole lot if an IIF function to seperate them.

I'll give this a try - thank you.

[June7]

The records should be in one table with another field for gender.