Selecting highest value across several fields

[megabrown]

Hello,

I have a table in Access 2003 that is a larger version of this:

Sample Beetle Fly Moth Spiders 
1 4.20 2.30 0.01 1.25 
 
2 2.30 2.00 0.00 3.00 
3 3.10 1.60 1.03 2.73

'Sample' is the primary key. I need to determine the percent of the most abundant creature per sample (ie. (# of most abundant creature in sample/total # of creatures in the sample)*100). I already have a query calculating the total creatures (called 'sampletotal') per sample and in the same query I would like to display the percent abundant creature per sample. How would I get access to go through each entry (ie. each sample) and select the field with the highest numerical value so that I could use it for the calculation?

So for sample 1 percent_abundant should = (4.2/7.76)*100 and for sample 2 it should = (3.0/7.3)*100 and so on for each sample

[weekend00]

base on your needs, you should modify the table structure like this:

Sample Creature percent
1 beatle 4.20
1 fly 2.3
1 moth 0.01
1 spider 1.25
1 crab 3.67
2 beatle 2.30
...

or use numbers instead of names of creatures.

[megabrown]

Its a fairly large database with about 294 samples and 56 different organism species. Is there anyway I can do the calculation without restructuring the original table?

[weekend00]

Yes. you can do the calculation without restructuring the original table by compare field by field which means 55 comparisons to get the maximum number.

[pbaldy]

Does this help?

Minimum or Maximum Value Across Fields of Record

[megabrown]

Thanks dudes!!

[pbaldy]

Happy to help, and welcome to the site!

[megabrown]

Thanks again.

I have implemented the module for determining max value across the fields however due to the number of fields I was unable to do it all in a single go because it says its 'too complex'. I had to break it up thus determing the max ('MAX1') for the first 15 fields, then the max ('MAX2') for the next 15, and so on. Up until this point everything works. When I try to determine the max (Total_Max) out of MAX1, MAX2, MAX3, MAX4, it gives me inaccurate values. How could I correct this?

[pbaldy]

Where does the error occur? As far as I know, the array and function should be able to handle 365 items (though I've never tested with that many). Can you post the db, or a representative sample?

[megabrown]

This is the SQL for the portion calculating the max with the part in red being where it gives the wrong value (It doesnt select the highest value out of those values):

Maximum([UNID_OLIGOCHAETA],[NAIDIDAE],[LUMBRICULIDAE],[TUBIFICIDAE],[UNID_HIRUDINEA],[GLOSSIPHONIDAE],[ERPOBDELLIDAE],[BRANCHIOBDELLIDAE],[HYDRIDAE],[TURBELLARIA],[NEMATODA],[NEMATOMORPHA],[UNID_BIVALVIA],[SPHAERIIDAE],[UNIONIDAE],[UNID_GASTROPODA],[ANCYLIIDAE],[VALVATIDAE],[PLANORBIDAE],[LYMNAEIDAE],[HYDROBIIDAE],[PHYSIDAE],[ACARI]) AS MAX1,

Maximum([UNID_DECAPODA],[CAMBARIDAE],[UNID_AMPHIPODA],[PONTOPOREIIDAE],[GAMMARIDAE],[HYALELLIDAE],[CRANGONYCTIDAE],[OSTRACODA],[UNID_COLLEMBOLA],[ISOTOMIDAE],[ENTOMOBRYIDAE],[SMITHURIDAE],[HYPOGASTRUIDAE],[PODURIDAE],[UNID_EPHEMEROPTERA],[CAENIDAE],[BAETIDAE],[HEPTAGENIIDAE],[EPHEMERELLIDAE],[SIPHLONEURIDAE],[EPHEMERIDAE],[LEPTOPHLEBIIDAE],[UNID_TRICHOPTERA],[LIMNEPHILIDAE],[PHRYGANEIDAE],[LEPTOCERIDAE],[POLYCENTROPODIDAE]) AS MAX2,

Maximum([LEPIDOSTOMATIDAE],[HELICOPSYCHIDAE],[MOLANNIDAE],[HYDROPTILIDAE],[SERICOSTOMATIDAE],[BRACHYCENTRIDAE],[HYDROPSYCHIDAE],[DIPSEUDOPSIDAE],[PSYCHOMYIIDAE],[ODONTOCERIDAE],[UNID_ANISOPTERA],[GOMPHIDAE],[CORDULIIDAE_LIBELLULIDAE],[AESHNIDAE],[UNID_ZYGOPTERA],[LESTIDAE],[COENAGRIONIDAE],[UNID_COLEOPTERA],[GYRINIDAE],[DYTISCIDAE],[ELMIDAE],[HYDROPHILIDAE],[HALIPLIDAE],[PSEPHENIDAE],[CHRYSOMELIDAE],[SCIRTIDAE],[HYDRAENIDAE]) AS MAX3,

Maximum([GEORYSSIDAE],[STAPHYLINIDAE],[CURCULIONIDAE_ERIRHININAE],[UNID_LEPIDOPTERA],[COSMOPTERIGIDAE],[PYRALIDAE],[UNID_HETEROPTERA],[GERRIDAE],[NOTONECTIDAE],[CORIXIDAE],[VELIIDAE],[NEPIDAE],[MESOVELIIDAE],[BELOSTOMATIDAE],[PLEIDAE],[UNID_MEGALOPTERA],[SIALIDAE],[CORYDALIDAE],[UNID_NEUROPTERA],[SISYRIDAE],[UNID_DIPTERA],[PUPAE_CYCLORRHAPHOUS_BRACHY],[CHIRONOMIDAE],[CHAOBORIDAE],[TABANIDAE],[CERATOPOGONIDAE],[TIPULIDAE]) AS MAX4,

Maximum([DOLICHOPODIDAE],[EMPIDIDAE],[SCIOMYZIDAE],[STRATIOMYIDAE],[PSYCHODIDAE],[DIXIDAE],[MUSCIDAE],[PORIFERA],[DOLOMEDES],[UNID_PLECOPTERA]) AS MAX5,

Maximum ([MAX1],[MAX2],[MAX3],[MAX4],[MAX5]) AS [Dominant Taxa]

This is the module I am using:

Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
' Return the maximum value found.
Maximum = currentVal
End Function

[pbaldy]

I meant when you tried to send all 30 to the function at once.

[megabrown]

Oh, when I tried doing this it said it says "Expression too complex in query expression":

Maximum([UNID_OLIGOCHAETA],[NAIDIDAE],[LUMBRICULIDAE],[TUBIFICIDAE],[UNID_HIRUDINEA],[GLOSSIPHONIDAE],[ERPOBDELLIDAE],[BRANCHIOBDELLIDAE],[HYDRIDAE],[TURBELLARIA],[NEMATODA],[NEMATOMORPHA],[UNID_BIVALVIA],[SPHAERIIDAE],[UNIONIDAE],[UNID_GASTROPODA],[ANCYLIIDAE],[VALVATIDAE],[PLANORBIDAE],[LYMNAEIDAE],[HYDROBIIDAE],[PHYSIDAE],[ACARI],[UNID_DECAPODA],[CAMBARIDAE],[UNID_AMPHIPODA],[PONTOPOREIIDAE],[GAMMARIDAE],[HYALELLIDAE],[CRANGONYCTIDAE],[OSTRACODA],[UNID_COLLEMBOLA],[ISOTOMIDAE],[ENTOMOBRYIDAE],[SMITHURIDAE],[HYPOGASTRUIDAE],[PODURIDAE],[UNID_EPHEMEROPTERA],[CAENIDAE],[BAETIDAE],[HEPTAGENIIDAE],[EPHEMERELLIDAE],[SIPHLONEURIDAE],[EPHEMERIDAE],[LEPTOPHLEBIIDAE],[UNID_TRICHOPTERA],[LIMNEPHILIDAE],[PHRYGANEIDAE],[LEPTOCERIDAE],[POLYCENTROPODIDAE],[LEPIDOSTOMATIDAE],[HELICOPSYCHIDAE],[MOLANNIDAE],[HYDROPTILIDAE],[SERICOSTOMATIDAE],[BRACHYCENTRIDAE],[HYDROPSYCHIDAE],[DIPSEUDOPSIDAE],[PSYCHOMYIIDAE],[ODONTOCERIDAE],[UNID_ANISOPTERA],[GOMPHIDAE],[CORDULIIDAE_LIBELLULIDAE],[AESHNIDAE],[UNID_ZYGOPTERA],[LESTIDAE],[COENAGRIONIDAE],[UNID_COLEOPTERA],[GYRINIDAE],[DYTISCIDAE],[ELMIDAE],[HYDROPHILIDAE],[HALIPLIDAE],[PSEPHENIDAE],[CHRYSOMELIDAE],[SCIRTIDAE],[HYDRAENIDAE],[GEORYSSIDAE],[STAPHYLINIDAE],[CURCULIONIDAE_ERIRHININAE],[UNID_LEPIDOPTERA],[COSMOPTERIGIDAE],[PYRALIDAE],[UNID_HETEROPTERA],[GERRIDAE],[NOTONECTIDAE],[CORIXIDAE],[VELIIDAE],[NEPIDAE],[MESOVELIIDAE],[BELOSTOMATIDAE],[PLEIDAE],[UNID_MEGALOPTERA],[SIALIDAE],[CORYDALIDAE],[UNID_NEUROPTERA],[SISYRIDAE],[UNID_DIPTERA],[PUPAE_CYCLORRHAPHOUS_BRACHY],[CHIRONOMIDAE],[CHAOBORIDAE],[TABANIDAE],[CERATOPOGONIDAE],[TIPULIDAE],[DOLICHOPODIDAE],[EMPIDIDAE],[SCIOMYZIDAE],[STRATIOMYIDAE],[PSYCHODIDAE],[DIXIDAE],[MUSCIDAE],[PORIFERA],[DOLOMEDES],[UNID_PLECOPTERA]) AS [Dominant Taxa]

They are all number fields, double, fixed, and have 5 decimal places each

[pbaldy]

Can you post the db? I've never tried the function with that many fields, and I don't have anything that has that many (as has been mentioned, it's not a normalized design).

[megabrown]

Yeah, it isnt normalized but Ive been hesitant to change it. The database exceeds the upload size limit even after compression :S

[pbaldy]

Can you make a copy of it, get rid of all but 10-20 records in this table, compact/repair, zip and see if that will make it?