Assign % based on EARNED (1 Viewer)

I have a table with that has a value and %

AMT PCNT
0 0.00%
36000 0.50%
46000 0.80%
52000 1.00%
58000 1.30%
64000 1.50%
72000 2.00%


And another table with crew and an "VALUE" earned.

CREW EARNED
Bill 32000
Todd 57508
Mark 67204
Donna 76118

If a value is >= an "AMT" I want to display the "PCNT"

I have 50 rows with "AMT" falling somewhere within these values.

How do I set up my query to show Bill at 0% and Todd at 1.0%

Thanks for any guidance

Use the designer as if you are building a regular query. Bring down all the fields from the Crew table, the PCNT field from that table. Make it an aggregate query (click the Sigma/Summation symbol in the ribbon). Change the "Group By" to "Max" under the PCNT field. Connect the tables on the AMT and EARNED fields, change the JOIN to show all from the Crew table.

So far so good, probably got that far yourself, now here's the magic: Go into SQL view and in the LEFT JOIN add a ">" so that the JOIN is now:


When you do that though, you can never go back. You must always edit this query using SQL, if you use the Designer and override the warnings it removes that special JOIN and you will have to redo it. So beware editing it.

I love the simplicity of it. Thanks so much, I'll give it a go on Monday