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.
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.
Last edited: