Grouping Data

[chan069]

Hi All,

I am soon to have a lot of data (too much for excel) from some simulation work I am completing for university which I then need group based on the formula below. Any help with this would be really appreciated!

My database is composed of 7 tables, each with identical variables.

I then need to group all the data into 11 groups (R, BA_DO, BA, BAS, LG, DOS, DOA, DO, MO, HG, SHG) based on the formula below (written for excel) which references values in the table below (the values in the table need to be able to be changed)

=IF(AND([Au]>M3,[As]<M4,[K]<M6,[S]<M8),"SHG",
IF(AND([Au]>L3,[As]<L4,[K]<L6,[S]<L8),"HG",
IF(AND([Au]>K3,[As]<K4,[K]<K6,[S]<K8),"MO",
IF(AND([Au]>J3,[As]<J4,[K]<J6,[S]<J8),"DO",


IF(AND([Au]>I3,[As]<I4,[K]<I6,[S]<I8),"DOA",
IF(AND([Au]>H3,[As]<H4,[S]<H8),"DOS",
IF(AND([Au]>G3,[As]<G4,[K]<G6,[S]<G8),"LG",
IF(AND([Au]>F3,[As]<F4,[K]<F6,[S]<F8),"BAS",
IF(AND([Au]>E3,[S]<E4),"BA",
IF(AND([S]>D9),"BA_DO",IF(AND([Au]<C2),"R"
)))))))))),"NA")

Following the completion of the groupings I then need to display the resultant data in a summary table as per below.


Any help would be welcomed!

Cheers.

[chan069]

Or alternatively would anybody be able to assist in the rewriting of this If statement for access?

''=IF(AND([Au]>0.95,[As]<1.0,[K]<1.0,[S]<0.1),"SHG",
IF(AND([Au]>0.85,[As]<0.8,[K]<0.95,[S]<0.2),"HG",
IF(AND([Au]>0.75,[As]<0.7,[K]<0.90,[S]<0.3),"MO",
IF(AND([Au]>0.65,[As]<0.6,[K]<0.85,[S]<0.4),"DO",
IF(AND([Au]>0.55,[As]<0.5,[K]<0.80,[S]<0.5),"DOA",
IF(AND([Au]>0.45,[As]<0.4,[S]<0.60),"DOS",
IF(AND([Au]>0.35,[As]<0.3,[K]<0.70,[S]<0.7),"LG",
IF(AND([Au]>0.25,[As]<1.0,[K]<0.65,[S]<0.8),"BAS",
IF(AND([Au]>0.15,[S]<0.90),"BA",
IF(AND([S]>0.95),"BA_DO",
IF(AND([Au]<0.05),"R"
)))))))))),"NA")

[chan069]

I have managed to write a formula which groups all my data, however when every I wish to make any changes to definitions which control the groupings I have to complete this within the Query editor. Is there away where I can reference the numerical values (the <> values) in a table? And then alter these values by utilizing a form.

switch([Au]>0.95 AND[As]<1.0 AND[K]<1.0 AND[S]<0.1,"SHG",
[Au]>0.85 AND[As]<0.8 AND[K]<0.95 AND[S]<0.2,"HG",
[Au]>0.75 AND[As]<0.7 AND[K]<0.90 AND[S]<0.3,"MO",
[Au]>0.65 AND[As]<0.6 AND[K]<0.85 AND[S]<0.4,"DO",
[Au]>0.55 AND[As]<0.5 AND[K]<0.80 AND[S]<0.5,"DOA",
[Au]>0.45 AND[As]<0.4 AND[S]<0.60,"DOS",
[Au]>0.35 AND[As]<0.3 AND[K]<0.70 AND[S]<0.7,"LG",
[Au]>0.25 AND[As]<1.0 AND[K]<0.65 AND[S]<0.8,"BAS",
[Au]>0.15 AND[S]<0.90,"BA",
[S]>0.95,"BA_DO",
[Au]<0.05,"R")