Finding the max among several columns

[George]

Dear friends,

I have inheritanced a somehow flat database. Within a table I have about 50
numeric fields. In need to find the 3 maximum numbers among all these
fields. Is there any way?

e.g. in column10 I have a number 50
in column23 I have a number 56
in column44 I have a number 33
All the other columns are less than 33.

How can I show only those columns, showing up their field names?

Thanking you in advance,

GeorgeCY

 

[NetworkTrade]

are you looking for a mechanical solution to use repeatedly without ever
looking at it - or is this a one time process? if one time you can simply
sort column by column and manually see the values pretty quick....

 

[Jerry Whittle]

First off you need to combine all the columns into one long one with a field
name. That calls for UNION queries. You'll need to combine 50 of them. Note:
you might run into problems with the lenght of the SQL and further need to
break things up.

Then you need to find the top 3 of those.

Select Top 3 *
From (
SELECT ASIF.DATA_LENGTH As TheSize,
"DATA_LENGTH" AS FieldName
FROM ASIF
UNION
(SELECT ASIF.COLUMN_ID,
"COLUMN_ID" AS FieldName
FROM ASIF)
UNION
(SELECT ASIF.DENSITY,
"DENSITY" AS FieldName
FROM ASIF)
UNION
(SELECT ASIF.NUM_NULLS,
"NUM_NULLS" AS FieldName
FROM ASIF) )
ORDER BY 1 DESC ;

If you have to do this often, I recommend using something like above,
without the Top 3, to create a new table that is better normalized.

 

[George]

Thanks Jerry for your reply,

As I realise, I have to combine (concatenate?) all columns into one long one?
Please note that within each column a number is found, either with one digit
or even with two.

Any sugesstions?

Ο χÏήστης "Jerry Whittle" έγγÏαψε:

 

[John W. Vinson]

Dear friends,

I have inheritanced a somehow flat database. Within a table I have about 50
numeric fields. In need to find the 3 maximum numbers among all these
fields. Is there any way?

e.g. in column10 I have a number 50
in column23 I have a number 56
in column44 I have a number 33
All the other columns are less than 33.

How can I show only those columns, showing up their field names?

Thanking you in advance,

GeorgeCY

A normalizing Union query might be worthwhile (and will help you normalize the
data for future use):

SELECT ID, (1) AS ColNo, Column1 AS TheValue FROM yourtable
UNION ALL
SELECT ID, (2), Column2 FROM yourtable
UNION ALL
SELECT ID, (3), Column3 FROM yourtable
<etc for all 50 fields>

This will "unravel" the wide-flat spreadsheet into a tall-thin recordset; you
can then sort descending by TheValue to pick off the top three.

 

[George]

Thanks a lot John,

I tried it and now I can find where my maximum number is recorderd, but I
need to see three fields, having the highest values for each record, and if
possible instead of giving me the columnNo, e.g. 1,2,3 to give me the actual
field name.

Sorry, I know that this is very hard but if you can help I will highly
appreciate.

GeorgeCY



Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

Thanks a lot John,

I tried it and now I can find where my maximum number is recorderd, but I
need to see three fields, having the highest values for each record, and if
possible instead of giving me the columnNo, e.g. 1,2,3 to give me the actual
field name.

Sorry, I know that this is very hard but if you can help I will highly
appreciate.

I could help... if you would kindly post the actual structure of your table
and the result that you want. You can include whatever fields you like in the
UNION query, just so the 50 SELECT clauses all contain the same number of
fields of matching datatype. For example, instead of (1) you could put
"Fieldname"... but I was answering the question that you asked, not the one in
your head!

 

[George]

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of plot
no 1
SecondSpecies will be the name of the field having the second maximum value
of plot no1
ThirdSpecies will be the name of the field having the third maximum value of
plot no 1

Any ideas?

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of plot
no 1
SecondSpecies will be the name of the field having the second maximum value
of plot no1
ThirdSpecies will be the name of the field having the third maximum value of
plot no 1

Would it be acceptable to have it look

PlotNo 1
and in a subform or subreport
Pinus_nigra 31
other_species3 28
Pinus_brutia 24

If so, create a UNION query:

SELECT PlotNo, "Pinus_brutia" AS Species, [Pinus_brutia] AS qty FROM tablename
UNION ALL
SELECT PlotNo, "Pinus_nigra", [Pinus_nigra] FROM tablename
UNION ALL
SELECT PlotNo, "Other_species1",[Other_species1] FROM tablename
<etc through all 50 fields>

Save this query as uniCounts, and base the subform or subreport on a query

SELECT TOP 3 PlotNo, Species, Qty FROM uniCounts ORDER BY Qty;

Use PlotNo as the master/child link field of the subform/subreport...

I tried for a while to get the three-top as fields across but it's late and
I'm sleepy...

 

[George]

I agree John,

The ideal one should be two related tables (one to many), but unfortunately
there is not time to normalise them right now. If you find any solution for
the unormalised table please inform me.

Thanks again, and sweet dreams

Ο χÏήστης "John W. Vinson" έγγÏαψε:

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of plot
no 1
SecondSpecies will be the name of the field having the second maximum value
of plot no1
ThirdSpecies will be the name of the field having the third maximum value of
plot no 1

Would it be acceptable to have it look

PlotNo 1
and in a subform or subreport
Pinus_nigra 31
other_species3 28
Pinus_brutia 24

If so, create a UNION query:

SELECT PlotNo, "Pinus_brutia" AS Species, [Pinus_brutia] AS qty FROM tablename
UNION ALL
SELECT PlotNo, "Pinus_nigra", [Pinus_nigra] FROM tablename
UNION ALL
SELECT PlotNo, "Other_species1",[Other_species1] FROM tablename
<etc through all 50 fields>

Save this query as uniCounts, and base the subform or subreport on a query

SELECT TOP 3 PlotNo, Species, Qty FROM uniCounts ORDER BY Qty;

Use PlotNo as the master/child link field of the subform/subreport...

I tried for a while to get the three-top as fields across but it's late and
I'm sleepy...

 

[BruceM]

As I understand, John was suggesting a solution for the unnormalized
structure you have now. The union query normalizes the data somewhat, and
is used as the basis for a Top Three query. This Top Three query is used as
the Record Source for a subform.

I agree John,

The ideal one should be two related tables (one to many), but
unfortunately
there is not time to normalise them right now. If you find any solution
for
the unormalised table please inform me.

Thanks again, and sweet dreams

Ο χÏήστης "John W. Vinson" έγγÏαψε:

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of
plot
no 1
SecondSpecies will be the name of the field having the second maximum
value
of plot no1
ThirdSpecies will be the name of the field having the third maximum
value of
plot no 1

Would it be acceptable to have it look

PlotNo 1
and in a subform or subreport
Pinus_nigra 31
other_species3 28
Pinus_brutia 24

If so, create a UNION query:

SELECT PlotNo, "Pinus_brutia" AS Species, [Pinus_brutia] AS qty FROM
tablename
UNION ALL
SELECT PlotNo, "Pinus_nigra", [Pinus_nigra] FROM tablename
UNION ALL
SELECT PlotNo, "Other_species1",[Other_species1] FROM tablename
<etc through all 50 fields>

Save this query as uniCounts, and base the subform or subreport on a
query

SELECT TOP 3 PlotNo, Species, Qty FROM uniCounts ORDER BY Qty;

Use PlotNo as the master/child link field of the subform/subreport...

I tried for a while to get the three-top as fields across but it's late
and
I'm sleepy...

 

[Douglas J. Steele]

Unfortunately, I don't believe it's possible to have 50 subqueries in a
Union query: I think the limit's 32.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

As I understand, John was suggesting a solution for the unnormalized
structure you have now. The union query normalizes the data somewhat, and
is used as the basis for a Top Three query. This Top Three query is used
as the Record Source for a subform.

I agree John,

The ideal one should be two related tables (one to many), but
unfortunately
there is not time to normalise them right now. If you find any solution
for
the unormalised table please inform me.

Thanks again, and sweet dreams

? ??????? "John W. Vinson" ???????:

On Tue, 20 May 2008 21:45:01 -0700, George
<[email protected]>
wrote:

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of
plot
no 1
SecondSpecies will be the name of the field having the second maximum
value
of plot no1
ThirdSpecies will be the name of the field having the third maximum
value of
plot no 1


Would it be acceptable to have it look

PlotNo 1
and in a subform or subreport
Pinus_nigra 31
other_species3 28
Pinus_brutia 24

If so, create a UNION query:

SELECT PlotNo, "Pinus_brutia" AS Species, [Pinus_brutia] AS qty FROM
tablename
UNION ALL
SELECT PlotNo, "Pinus_nigra", [Pinus_nigra] FROM tablename
UNION ALL
SELECT PlotNo, "Other_species1",[Other_species1] FROM tablename
<etc through all 50 fields>

Save this query as uniCounts, and base the subform or subreport on a
query

SELECT TOP 3 PlotNo, Species, Qty FROM uniCounts ORDER BY Qty;

Use PlotNo as the master/child link field of the subform/subreport...

I tried for a while to get the three-top as fields across but it's late
and
I'm sleepy...

 

[BruceM]

I hope that information never comes in handy, but it is good to know.

Unfortunately, I don't believe it's possible to have 50 subqueries in a
Union query: I think the limit's 32.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

As I understand, John was suggesting a solution for the unnormalized
structure you have now. The union query normalizes the data somewhat,
and is used as the basis for a Top Three query. This Top Three query is
used as the Record Source for a subform.

I agree John,

The ideal one should be two related tables (one to many), but
unfortunately
there is not time to normalise them right now. If you find any solution
for
the unormalised table please inform me.

Thanks again, and sweet dreams

? ??????? "John W. Vinson" ???????:

On Tue, 20 May 2008 21:45:01 -0700, George
<[email protected]>
wrote:

Thanks again John,

Here is the structure of my Table (for a forest inventory):

PlotNo - Number - Integer - Primary Key
Pinus_brutia - Number - byte
Pinus_nigra - Number - byte
other_species1 - Number - byte
other_species2 - Number - byte
other_species3 - Number - byte
etc... (up to 50 species)

I want to have (perhaps in a query) the following

PlotNo FirstSpecies SecondSpecies ThirdSpecies
1 Pinus_nigra other_species3 Pinus_brutia

FirstSpecies will be the name of the field having the maximum value of
plot
no 1
SecondSpecies will be the name of the field having the second maximum
value
of plot no1
ThirdSpecies will be the name of the field having the third maximum
value of
plot no 1


Would it be acceptable to have it look

PlotNo 1
and in a subform or subreport
Pinus_nigra 31
other_species3 28
Pinus_brutia 24

If so, create a UNION query:

SELECT PlotNo, "Pinus_brutia" AS Species, [Pinus_brutia] AS qty FROM
tablename
UNION ALL
SELECT PlotNo, "Pinus_nigra", [Pinus_nigra] FROM tablename
UNION ALL
SELECT PlotNo, "Other_species1",[Other_species1] FROM tablename
<etc through all 50 fields>

Save this query as uniCounts, and base the subform or subreport on a
query

SELECT TOP 3 PlotNo, Species, Qty FROM uniCounts ORDER BY Qty;

Use PlotNo as the master/child link field of the subform/subreport...

I tried for a while to get the three-top as fields across but it's late
and
I'm sleepy...

 

[John W. Vinson]

Unfortunately, I don't believe it's possible to have 50 subqueries in a
Union query: I think the limit's 32.

If I get some time I'll try it. I've run into the Query Too Complex error on
large union queries, but I think that's from the overall size of the compiled
query, not the number of unions.

I'm sure the AFL/CIO would be very interested to learn that Microsoft is
putting limits on unions...!