Compare data between 2 rows

[jeffshex]

Not sure if this is for the query area or not...but here is my issue:
Simply put, I need to compare numbers from 2 different rows. If one number is less than a set value, then a flag should go up, same with the number from the 2nd row...if it is less than a specified value, then the flag is raised.

I have a lookup table containing the set values:

Area   Min   Max   Level
===========================
1      0     100   A
1      101   200   B
1      201   300   C
2      0     100   A
2      101   200   B
2      201   300   C

My table that assigns this to a person is:

ID   PersonID  Area  Score
==========================
1    1234      1      89
2    1234      2      255
3    5890      1      210
4    5890      2      120

So I'm trying to find out if person 1234 got less than 100 in area 1 or less than 200 in area 2 than the flag is raised.
This would be easier, maybe, if the data was on 1 row. Which i cannot because there will be a related child table to this one.

If it needs to be done other than a query, like VBA or something, I can post it there. I'm up for any help trying to figure this out.
Thanks!

 

[BNPMike]

If you join the second table to the first on Area, you will get records like

1234 1 89 0 100 A
1234 1 89 101 200 B
1234 1 89 201 300 C
1234 2 etc

You can then set your flag for each row to be the level value if the score is less than the max and greater than the minimum . else set it to null. Then you can select the non-null resulting rows:

1234 1 89 0 100 A A
1234 1 89 101 200 B [null]
1234 1 89 201 300 C [null]
1234 2 etc

You should be able to do that in one Select statement.

 

[jeffshex]

Maybe I'm misunderstanding, but how can I look at person # 1234 and see if Area 1 or Area 2 are below the score?

It would be these two rows:

Person   Area  Score
-----------------------
1234     1     89
1234     2     255

If someone falls within the A range in either of the two areas, then they fail. Using what I have, is there anyway to have an overall resolve?
Like a query that be assebled to list both areas and score on one row?

Person   Area1   Area1Score  Area2  Area2Score  Pass
--------------------------------------------------------
1234     1       89          2      255         FALSE

That way I can easily compare the two areas and process the logic.

 

[jeffshex]

I think I may have figured it out.
I added table that contains the people's scores on again and aliased it. I added duplicate fields for the area and score and put a parameter in for area 1 and area 2 in ther other box.
It looks like it is what I need, but not sure what the downsides to doing it this way are.
Any insight?