Picking a value form a list of values in a report

[imellor]

I have a database which I run for my Scout group.
One of the reports I run is the nights away badge.
In the report, I produce a list of camps each Scout has attended and calculate the total number of nights.

I also have another field that displays the current nights away badge held, so for example:

Fred Bloggs
May Camp 3 nights
July Camp 4 nights
Total nights = 7
Current badge held 1 nights

What I would like to do is have a field that says they current badge they are entitled to. The badges go up in stages = 1, 5, 10, 20, 35, 50 etc

So for the above example the line would be:

Current badge entitlement 5 nights

My question is, how do I take the value 7 and select the relevant badge entitlement from the list, in this case 5?

In Excel I would do a VLookup, but I can't find an equivalent in Access?

Any suggestions

Ian

 

[Mattster]

A fellow scouter.

I would simply create a second table that defines the badge entitlements where one record would contain
BadgeName, NightsAway

Then simply in your query, have a <= result, so you have a result of 7 coming out already that is the data that you would put in against the nights away field.

This would provide you with two records 1 and 5. Now if you only want to display the second, make sure that the report is sorted first by Scout and then second by the new tables field nightsAway. Don't complete the detail section, just a footer for the Scout group and BAM there is your answer.

Hope that works