Query Expression for Evaluating a Large Field of Data

[EcologyHelp]

I have received some very good feedback from this site before and want to thank all of you for that. I have another problem that I am stuck on and wanted to see if anyone out there has some thoughts on how to approach this question.


In the above pictures I have portions of a query that I am working through. The really important fields in the data sheet are the fields that contain OBL, FACW, FAC, FACU, and UPL, more specifically the left hand column in each pair. The other important field is the last one in the second picture that is titled Hydrophitic, the full title is Hydrophitic Veg (and yes I realize that it is misspelled, late night last night). What I am trying to accomplish is to have Access go through each line and evaluate if the # of OBL + FACW > FACU + UPL the Hydrophitic Veg = true and if # of OBL + FACW < FACU + UPL the Hydrophitic Veg = False. I am wondering if this is at all possible so in the future I or somebody else working with this data won't have to do it by hand.

Thanks again for your time.

[ssanfu]

I think it is doable, but I am confused on what you are adding/ comparing. When you say OBL, are you counting the number of occurances of OBL, or are there numbers in a different field you want to add?

In the second picture, I see the second record has the field Hydrophitic equal to TRUE (-1). How did you get the -1?
Maybe an example would help.

[CJ_London]

I see the second record has the field Hydrophitic equal to TRUE (-1). How did you get the -1?

fyi true/false can also be represented as -1/0 numerically (tho' in most other db systems it is 1/0)

[ssanfu]

fyi true/false can also be represented as -1/0 numerically (tho' in most other db systems it is 1/0)

Yep, knew that. That is why I had TRUE (-1).... maybe I should have used TRUE/(-1). I was asking how the TRUE/(-1) was calculated.

[EcologyHelp]

I had previously calculated by hand the true/false values, but that seems impractical for anyone updating the database after me. I am trying to write in an expression that will auto update the hydrophytic value for future users. Needless to say my Access skills are quite low and I am out of my league with this one.

[ssanfu]

I had previously calculated by hand the true/false values,

Again, HOW did you /do you calculate the values?

Pretend that you are teaching me to take over for you. Describe the calculation process.

[EcologyHelp]

So there are five statuses OBL, FACW, FAC, FACU, UPL. The two columns of represent two different regions so we are only interested in the one that has GP not WMVC. As you go across the line of data OBL = 1 and FACW = 1 FAC = 0 and FACU = -1 and UPL = -1. When you add then up if you have a positive number the hydrophitic field is marked as true, if you have a negative number it is marked as false (-1/0). An alternated way of looking at it is if the number of occurrences of OBL and FACW is greater then FACU and UPL the it is marked as true.

[ssanfu]

OK, better.

The two columns of represent two different regions so we are only interested in the one that has GP not WMVC

Now, what are the actual field names that you look at to check for the statuses? I see only 1 field name: "WSPZ1_GP_SP"


Next question: What is the SQL of the query as you have it now?