Lookup Table on Calculated Field

[duesouth]

I work in a school and am trying to create a database for modular exams.

The pupils take more than one practical - but only use the highest grade to go forward to their actual result.

I've successfully created a query which works out the maximum of these practicals.

However, from this value the raw mark has to turn into a UMS score - e.g. the raw mark was 40 - this converts to 100 (so the practical grades are weighted). My problem is that I can't multiply the raw mark by a fixed amount as it varies as you can see from the table exert below.

1 = 2
2 = 4
3 = 5
4 = 7
5 = 9
6 = 11

I've tried to link the highest raw score in the Query to the above lookup table - but I keep getting "type mismatch in expression" errors.

I'm sure there must be a way of doing this - basically the equivalent to a VLOOKUP in Excel.

Would appreciate any help.

 

[John Big Booty]

Perhaps the DLookup() function will do what you are after.

 

[duesouth]

Perhaps the DLookup() function will do what you are after.

Cheers - it sounds like the right idea, but I can't get it to work!

I have in the query QRY_SCYC_Calculation a calculated field called SCYC - which is the maximum of three coursework scores.

I have a Table - TBL_Raw_to_UMS which containts the Raw Scores (which will the the SCYC) and then the equivalent UMS.

What would my DLookup syntax look like?

Basically in Excel speak it would be =VLOOKUP(SCYC,Raw_to_UMS,2,FALSE) to return the correct number.

Funnily enough I thought the maximum of three scores would be the tricky bit - but it's the lookup that I can't get!

 

[duesouth]

I'm nearly there with:

DLookUp("UMS","CAU Raw to UMS","Raw = " & [SCYC])

The only problem is that it brings back each record more than one - the same data, but tons of times......

 

[duesouth]

OK - got it!! Went into the SQL view and added a DISTINCT

SELECT DISTINCT [SCYC Calculation].[Exam Number], [SCYC Calculation].SCYC, DLookUp("UMS","CAU Raw to UMS","Raw = " & [SCYC]) AS Exp
FROM [CAU Raw to UMS], [SCYC Calculation];

Thanks for your help.

 

[John Big Booty]

Good work, glad you got it sorted whilst I slept