James Reay
New member
- Local time
- Today, 00:57
- Joined
- Apr 1, 2003
- Messages
- 9
Something that has been bothering me for a while...
I have a query that has the following expression as one of its fields.
Grade: DLookUp("[TABLE Grade Schema Details]![Grade]","TABLE Grade Schema Details","[TABLE Grade Schema Details]![Grade Schema] = '" & [Grade Schema] & "' And [TABLE Grade Schema Details]![Percentage to achieve] <= " & (100/[Max Mark])*[Mark])
Basically what it does is to search through a table named "TABLE Grade Schema Details" with 3 fields (Grade Schema, Grade, Percentage to achieve) in order to fetch the particular grade earned by a student’s achievement in an exam module. Everything works fine (table sorted in descending order of Percentage to achieve field) as long as all the details associated with a particular grade schema are added at the same time. The problem occurs when a new boundary is inserted i.e. between what is required to score an F and what is required to score a U (0). The only way to solve things using the above DLookup is to delete all rows within the TABLE Grade Schema Details that relate to a particular grade schema and add then all again in descending order. However........
If I create a simple select query that just returns the same information stored in 'TABLE Grade Schema Details' and sorts it into descending order using the Percentage to Achieve field and then uses that as the basis of my DLookup (See below), everything works as required.
Grade: DLookUp("[QUERY Sorted Grade Schema Details]![Grade]","QUERY Sorted Grade Schema Details","[QUERY Sorted Grade Schema Details]![Grade Schema] = '" & [Grade Schema] & "' And [QUERY Sorted Grade Schema Details]![Percentage to achieve] <= " & (100/[Max Mark])*[Mark])
Does anyone know why or how to avoid having to use the extra query (without changing the table structure)?
I have a query that has the following expression as one of its fields.
Grade: DLookUp("[TABLE Grade Schema Details]![Grade]","TABLE Grade Schema Details","[TABLE Grade Schema Details]![Grade Schema] = '" & [Grade Schema] & "' And [TABLE Grade Schema Details]![Percentage to achieve] <= " & (100/[Max Mark])*[Mark])
Basically what it does is to search through a table named "TABLE Grade Schema Details" with 3 fields (Grade Schema, Grade, Percentage to achieve) in order to fetch the particular grade earned by a student’s achievement in an exam module. Everything works fine (table sorted in descending order of Percentage to achieve field) as long as all the details associated with a particular grade schema are added at the same time. The problem occurs when a new boundary is inserted i.e. between what is required to score an F and what is required to score a U (0). The only way to solve things using the above DLookup is to delete all rows within the TABLE Grade Schema Details that relate to a particular grade schema and add then all again in descending order. However........
If I create a simple select query that just returns the same information stored in 'TABLE Grade Schema Details' and sorts it into descending order using the Percentage to Achieve field and then uses that as the basis of my DLookup (See below), everything works as required.
Grade: DLookUp("[QUERY Sorted Grade Schema Details]![Grade]","QUERY Sorted Grade Schema Details","[QUERY Sorted Grade Schema Details]![Grade Schema] = '" & [Grade Schema] & "' And [QUERY Sorted Grade Schema Details]![Percentage to achieve] <= " & (100/[Max Mark])*[Mark])
Does anyone know why or how to avoid having to use the extra query (without changing the table structure)?
