help with query to determine range definition

[Funkbuqet]

I need some help figuring out how to make this query work. Essentially I have a table [Activity] with the feild "RecordedConcentration" with a numeric value. I have a second table [Assumptions] that defines the "Catagory" (for a given area) based on "Concentration".

What I want to do is for each record in the [Activity] table I want to define what Catagory it would be in based the "RecordedConcentration" value compaired against "Concentration". See the below example.

[Activity] table.
Area_____Date_______ RecordedConcentration
A________12/10/11______10
B _______12/10/11 ______2
C _______12/10/11 ______25
D _______12/10/11 ______5
B _______12/11/11 ______.75
C _______12/11/11 ______30

[Assumptions] table
Catagory _____Concentration ______State
7W __________20 ________________CA
5W __________10 ________________CA
2W __________5 _________________CA
1W __________1 _________________CA

Catagory applies to it's value and below. ie 2W would be >1 and <=5.

This is what I want to produce.
Area _____Date _________RecordedConcentration ______Catagory
A _______12/10/11 ________10 ______________________5W
B _______12/10/11 _________2 ______________________2W
C _______12/10/11 ________25 ______________________7W
D _______12/10/11 _________5 ______________________2W
B _______12/11/11 ________.75 ______________________1W
C _______12/11/11 ________30 ______________________7W

The two tables are related to each other a thrid table that knows which areas are in what state. The actual tables are a lot more complex too. There are 12 different catagories for each of the 4 states and over 35000 records in the activity table.

I tried using Dlookup, but there are too many variables and to get the desired result I would have to use it with 18 nested iif statements (which is too long to be supported).

Sorry if this isn't entirely clear. It was hard to describe what I want and I cannot provide an example database without dummying up all of the data as it is propriatary to my employer and I am not at liberty to share it. Thanks for any help you can give.

 

[plog]

Let me put this even simpler to see if I understand it correctly:

You want to take every RecordedConcentration value from the Activity table and compare it to every Concentration value in the Assumption table and return the Catagory of the record that is the closest to RecordedConcentration without going over.

If that's correct (and I think it is based on the sample data you provided) I would accomplish that with a DMax function nested in a Dlookup function like this:

DLookup("[Catagory]", "Assumptions", "[Concentration]=" & DMax("[Concentration]", "Assumptions"], "[Concentration]<=" & [RecordedConcentration]))


I didn't test that code, but the idea's right--find the largest Concentration in Assumption that is less than or equal to RecordedConcentration, then get the Catagory of that Concentration.

 

[Funkbuqet]

Plog, thanks a lot for your help. That almost gets me there. As I mentioned but did not include in my sample data, I have 4 states with different sets of values. The expression you gave me works well except it returns the closest value without going over from the table as a whole and does not take into account where the work took place. I tried to apply a second set of criteria but it is causing some sort of an error. I cant seem to get Dmax to apply the state ctiteria (even by itself). Below is the the expression I am trying to use and the error I get.

Dmax("Concentration", "Assumptions", "State=" & [OpState])

*Where [OpState] is one of the feilds in the query. I also tried Me.OpState to no avail.

When I try to run the query i get a #Error - "'The object doesn't contain the Automation object 'Florida". Florida is the value of the OpState feild. Why is it interpretting it as an Automation object?

When I use the expression you gave me (With [RecordedConcentration] also being a feild in the query) it works just fine.

Dmax("Concentration", "Assumptions", "Concentration<=" & [RecordedConcentration]

I have a feeling it has something to do with OpState being text, but haven't been able to find anything online to help.

 

[plog]

It does have to do with being text. You need surround it by quote marks. This should work:

Dmax("Concentration", "Assumptions", "State='" & [OpState] & "'")

 

[Funkbuqet]

That did the trick. Thanks for all your help answering my very convoluted question!