The premise of my database is an automated process for the Championship tables for a motorsport club I am in. There is a Driver table of race number/name and a results table for entering a finishing position. I have a table (Points) in which I require a lookup for a value [points] from a table [pointslookup], but the criteria I need/want to use come from 2/3 other tables. I have tried to reference the criteria tables but get an invalid syntax error message.
The tables (with fields) that relate to the dlookup are:
Fixtures - FixtureID, CarsBookedID
Results - FixtureID, RaceNumber, Race1Finish, Race2Finish, Race3Finish, Race4Finish
Points - FixtureID, RaceNumber, Race1Points, Race2Points, Race3Points, Race4Points
PointsLookup - CarsBookedID, Position, Points
All fields are "Number" except the [fixtures]![fixtureID], which is an auto number. CarsBookedID are not joined as [fixtures]![carsbookedID] is a calculated field. Fixtures, Results and Points are all linked through the fixtureID fields.
The dlookup is for the Points table and each "race_points" field. I need to lookup the "race_finish" which matches the "Position" field in PointsLookup, and also carsbookedID between the two appropriate tables.
I have tried the following two expressions within the calculated field for the points table fields:
=dlookup(“[points]”,”[pointslookup]”, “[position] =” & [race1position] & ” AND “[carsbookedID] =” & [carsbookedID] & ”) =dlookup(“[points]”,”[pointslookup]”, “[position] =” & [Results]! [Race1Finish] & ” AND “[PointsLookup]![CarsBookedID] =” & [Fixtures]! [CarsBookedID] & ” AND "[MeetingPoints]![FixtureID] =" & [Results]! [FixtureID] & ") Any help, even saying it won't work as I intend, would be gratefully received.
As i am trying to set calculated fields, should i instead, use a form and unbound textboxes then update the points table using an update query? or should i put the "race_points" fields into the results table to decrease the cross table reference for the criteria?
Thanks in advance.
