Compare data to limits (max and min)

[srcera]

Good Day,

I have data in one table and then my range limits for the data (the maximum and minimum values allowed) in another table. I want to compare the data against the limits to see if they pass or fail.

This would be easy to do in Excel, but I'd rather avoid the export/import if possible.

Anyone have experience doing something like this and could give me a possible plan of action?

Thanks!

 

[Joe4]

Can you post an example of what your data looks like?
Is the table with the limits a one record table?

 

[srcera]

The data would be in a table like this:

Sample Grade Length Width Height
1 ABC 1.2 4.3 3.5
2 DEF 2.4 6.7 9.2
3 ABC 4.7 3.8 9.1

The limits table could be in one of two formats

Format 1
Grade Max Length Min Length Max Width Min Width Max Height Min Height
ABC 5 1 6 3 8 2
DEF 11 5 6 2 10 1

OR

Format 2
Grade Limit Length Width Height
ABC Max 5 6 8
ABC Min 1 3 2
DEF Max 11 6 10
DEF Min 5 2 1

The tables would be linked by grade.

 

[Joe4]

OK, let's look at Format 1. For simplicity sake, I named the tables "Data" and "Limits".

In a query, link your two tables on your Grade field. Then, to check the Length field, create this calculated field:

Length_Test: IIf(([Data]![Length]<=[Limits]![Max Length]) And ([Data]![Length]>=[Limits]![Min Length]),"Pass","Fail")

Repeat the same logic with your Width and Height fields.

 

[HiTechCoach]

I generally handle this with a subquery.

I have an example in this other Post Here