Match Formula in MS Access

[Fredek]

Hi guys,

I am working in Design view of a query. I have two tables below. I want to add a formula in query formula window to help calculate the new grade based on the 10% improvement. In Excel I could do it via Match formula, how can I do this in Access?

Table A:

Grade Upper Bound
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

Table B:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Score[/TD]
[TD]10% Improvement[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]11[/TD]
[TD]New projected grade based on 10% improvement from table A[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[Joe4]

I am not quite sure I undestand the relationship between the two tables. But if you simply want to create a calculated field that takes a value and add 10% to it, it would look somethng like this:

NewProjGrade: [Score] * 1.1

 

[Fredek]

Hi Joe, thank you for your reply!

I want to be able to use the new score (using the *1.1 or *1.2)to match it to the Table A to work out whether that score increase would result in grade increase.

For example if we start with a score of 40 and assume a 20% increase that would be 48 and would qualify for grade 5.

 

[Fredek]

Table A

Grade Lower Bound
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]0*[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I think you may be able to incorporate a solution like this:
https://access-programmers.co.uk/forums/showthread.php?t=287860