Comparing values in Access

[mikesal57]

Hi Guys....

Say I have 10 fields , 1 thru 10

I need field #1 to compare with 2 thru 10



If its the lowest than fields 2-10 I want it to show as a 1 in field 11

If its not the lowest ...I want a 0

if its Equal to a low in 2-10 .....I want a 1

Note:
a)all fields might be all blank....I want a 0

b) just field #1 has a value........I want a 0

c)Some fields might be blank within 2-10........still do compare as above and forget the blank ones

Hope this makes sense

thxs

[kd2017]

I wonder if your tables are properly normalized? Anyway, here is my untested attempt:

SELECT
IIF(
  (field_1 <= nz(field_2, field_1))
  AND (field_1 <= nz(field_3, field_1))
  AND (field_1 <= nz(field_4, field_1))
  AND (field_1 <= nz(field_5, field_1))
  AND (field_1 <= nz(field_6, field_1))
  AND (field_1 <= nz(field_7, field_1))
  AND (field_1 <= nz(field_8, field_1))
  AND (field_1 <= nz(field_9, field_1))
  AND (field_1 <= nz(field_10, field_1)),
  IIF(
    field_2 Is Not Null
    OR field_3 Is Not Null
    OR field_4 Is Not Null
    OR field_5 Is Not Null
    OR field_6 Is Not Null
    OR field_7 Is Not Null
    OR field_8 Is Not Null
    OR field_9 Is Not Null
    OR field_10 Is Not Null,
    1,
    0
  ),
  0
) AS field_11
FROM my_table;

[CJ_London]

what if field1 does not have a value, but there are values in 1 or more of the other fields?

[mikesal57]

Thxs KD...

I'd like to show how this should come out....

First off...there are NO blanks.......it will show -1
so all the NULL has to be changes..

next , your code above........does that go inside the BUILDER?

These pictures are what I need to happen....

in that 1st picture I need the 1 and 0 to show at the end

after it runs , I need a 1 to show in row ARP 2 5.........ARP 3 1......ARP 4 3......ARP 4 5.....


thank you

[mikesal57]

I had that......field 1 nothing ....its a 0

if field one is a -1 and other have values....SHOULDN't HAPPEn

[CJ_London]

I had that......field 1 nothing ....its a 0

I don't see that, nearest is 'a)all fields might be all blank....I want a 0', but no matter clear now. This is my offering - similar to KD's and not tested

result:abs(field1 is not null
and nz(field1)<=nz(field2,field1)
and nz(field1)<=nz(field3,field1)
and nz(field1)<=nz(field4,field1)
and nz(field1)<=nz(field5,field1)
and nz(field1)<=nz(field6,field1)
and nz(field1)<=nz(field7,field1)
and nz(field1)<=nz(field8,field1)
and nz(field1)<=nz(field9,field1)
and nz(field1)<=nz(field10,field1)
and (Nz([field2])
+Nz([field3])
+Nz([field4])
+Nz([field5])
+Nz([field6])
+Nz([field7])
+Nz([field8])
+Nz([field9])
+Nz([field10])<>0))

You seem to have a lot more columns than the 10 you referred to - so you may have an issue with number of characters if you need more columns (think it is 1024 chars max). Also, not sure why you are showing a group by query

[mikesal57]

I don't see that, nearest is 'a)all fields might be all blank....I want a 0', but no matter clear now. This is my offering - similar to KD's and not tested

result:abs(field1 is not null
and nz(field1)<=nz(field2,field1)
and nz(field1)<=nz(field3,field1)
and nz(field1)<=nz(field4,field1)
and nz(field1)<=nz(field5,field1)
and nz(field1)<=nz(field6,field1)
and nz(field1)<=nz(field7,field1)
and nz(field1)<=nz(field8,field1)
and nz(field1)<=nz(field9,field1)
and nz(field1)<=nz(field10,field1)
and (Nz([field2])
+Nz([field3])
+Nz([field4])
+Nz([field5])
+Nz([field6])
+Nz([field7])
+Nz([field8])
+Nz([field9])
+Nz([field10])<>0))

You seem to have a lot more columns than the 10 you referred to - so you may have an issue with number of characters if you need more columns (think it is 1024 chars max). Also, not sure why you are showing a group by query

yes , I know that there's more columns....

OK...looks like I will change the field names and put this in BUILDER.......I''ll let you know

thx


PS - You do know that I mistakenly said blanks or zero's......its actually a -1 for fields with blanks or zeros.....right?

[CJ_London]

was not aware - KD's code is probably easier to change. You will need to replace nz's with iif(fieldx<1......

Would be a lot easier if your table was normalised - you can still use a crosstab to present in the excel format.

[CJ_London]

modified to use -1 or 0 for 'blank'

Result:Abs(field1>0
And field1<=iif(field2>0,field2,field1)
And field1<=iif(field3>0,field3,field1)
And field1<=iif(field4>0,field4,field1)
And field1<=iif(field5>0,field5,field1)
And field1<=iif(field6>0,field6,field1)
And field1<=iif(field7>0,field7,field1)
And field1<=iif(field8>0,field8,field1)
And field1<=iif(field9>0,field9,field1)
And field1<=iif(field10>0,field10,field1)
And (field2>0
or field3>0
or field4>0
or field5>0
or field6>0
or field7>0
or field8>0
or field9>0
or field10>0))

you only actually have 3 checks to make so you could split this into 2 or 3 separate calculations

chk1 - field1>0
chk2 - field1 <= to all the other fields where there is a value>0
chk3 - at least one of the other fields has a value>0

C1:Abs(field1>0)

C2:Abs(field1<=iif(field2>0,field2,field1)
And field1<=iif(field3>0,field3,field1)
And field1<=iif(field4>0,field4,field1)
And field1<=iif(field5>0,field5,field1)
And field1<=iif(field6>0,field6,field1)
And field1<=iif(field7>0,field7,field1)
And field1<=iif(field8>0,field8,field1)
And field1<=iif(field9>0,field9,field1)
And field1<=iif(field10>0,field10,field1))

C3:Abs(field2>0
or field3>0
or field4>0
or field5>0
or field6>0
or field7>0
or field8>0
or field9>0
or field10>0)

then combine

Result:C1*C2*C3

you can split it further

C2a:Abs(field1<=iif(field2>0,field2,field1)
And field1<=iif(field3>0,field3,field1)
And field1<=iif(field4>0,field4,field1)
And field1<=iif(field5>0,field5,field1))

C2b:Abs(field1<=iif(field6>0,field6,field1)
And field1<=iif(field7>0,field7,field1)
And field1<=iif(field8>0,field8,field1)
And field1<=iif(field9>0,field9,field1)
And field1<=iif(field10>0,field10,field1))

Result:C1*C2a*c2b*c3

might find this useful if you are running out of characters for a single calculation. Also this split will only work on a select query, not a group by query

[mikesal57]

was not aware - KD's code is probably easier to change. You will need to replace nz's with iif(fieldx<1......

Would be a lot easier if your table was normalised - you can still use a crosstab to present in the excel format.

I tweaked your code......but theres a issue

result: Abs([nPP1SHF] Is Not Null And Nz([nPP1SHF])<=Nz([nPP2SHF],[nPP1SHF]) And [nznPP1SHF])<=Nz([nPP3SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP4SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP5SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP6SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP7SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP8SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP9SHF],[nPP1SHF]) And Nz([nPP1SHF])<=Nz([nPP0SHF],[nPP1SHF]) And (Nz([nPP2SHF])+Nz([nPP3SHF])+Nz([nPP4SHF])+Nz([nPP5SHF])+Nz([nPP6SHF])+Nz([nPP7SHF])+Nz([nPP8SHF])+Nz([nPP9SHF])+Nz([nPP0SHF])<>0)

A) Picture 1 has a POP UP , I believe theres a error in the code somewhere

B) 1 is not showing up as the RESULT

C) Items in YELLOW should be a 1 because its lower than whats in fields 2-10

D) ARP 4 6 is NOT what I'm expecting ....field 3 is lower that field 1 ....so the result should be a zero

thx again for putting up with me

[kd2017]

Here's the source of issue A) [nznPP1SHF] should be nz([nPP1SHF])

[NoellaG]

Maybe I'm a bit stupid, but isn't this easier in Excel? Imo this isn't a database setup, and I'm a big fan for using the right tool for the right job :-)

[CJ_London]

Shame it wasn't mentioned from the beginning but since none of your values are null, there is no point in using the nz function, it won't produce the result you require. I provided a version which accounts for values of 0 and -1 in post #9, base your adaptation on that.

[mikesal57]

Here's the source of issue A) [nznPP1SHF] should be nz([nPP1SHF])

That looks like it worked KD......I'll look at full results .....I think we're good

thx so much.....

Mike

[mikesal57]

I guess could have but you guys are smarter.....

Mike