Find the max value of 4 fields in a single record...

[Jasonm]

I don't think I captured the essence of what I am trying to acomplish in my
subject line! Lets try again..

I have a table with a date field as teh Primary Key In it are four fileds
(I1, I2, I3 and I4) I would like to be able (in a query) to list the max of
those 4 fields.

I.E.
I1 = 4
I2 = 5
I3 = 3
I4 =Null

MaxI = 5

I have tried nesting If statements, but they get too deep (at least the way
I was thinking about it...) Does anyone have any suggestions as to how I can
do this? I have a workaround where I can use an average, but I would really
like to have the maximum...

Thanks, Jm

 

[Duane Hookom]

One method is to normalize your table with a union query and use this query
as the basis for a totals query:
-- quniDateAndMax ---
SELECT DateField, I1 as I, 1 as Huh
FROM tblNotNormal
UNION ALL
SELECT DateField, I2, 2
FROM tblNotNormal
UNION ALL
SELECT DateField, I3, 3
FROM tblNotNormal
UNION ALL
SELECT DateField, I4, 4
FROM tblNotNormal;

Then use a totals query like:
SELECT DateField, Max(I) as TheMaxI
FROM quniDateAndMax
GROUP BY DateField;

 

[Marshall Barton]

I don't think I captured the essence of what I am trying to acomplish in my
subject line! Lets try again..

I have a table with a date field as teh Primary Key In it are four fileds
(I1, I2, I3 and I4) I would like to be able (in a query) to list the max of
those 4 fields.

I.E.
I1 = 4
I2 = 5
I3 = 3
I4 =Null

MaxI = 5

The reason you are having trouble is because that data is
more like a spreadsheet than a relational database table. A
relational database (e.g. Access) needs to be Normalized
(google it) so that the four fields are in a separate table
where a simple query can utilize the Max aggregate function.

With the table structure you have, you will have all kinds
of issues, your specific question is just the tip of the
iceburg your project is crashing into.

To do what you asked, you will have to create your own max
of a list function. Here's an example of such a function:
------------------------------------------------------
Public Function MaxOfList(ParamArray vValues() As Variant)
As Variant
Dim vX As Variant

MaxOfList = vValues(0)
For Each vX In vValues
If vX >= Nz(MaxOfList, vX) Then MaxOfList = vX
Next vX

End Function

 

[Jasonm]

Duane,

Thank-you, and WOW! The union query worked like a charm! along with
Marchall's comments below I think I am well on the way tto correcting the
situation.

I inherited this database several years ago, and have already correct a
number of area where the data is not "normalized". Now I see another
opportunity to correct and improve what I have.

Thanks again,
Jason

 

[Jasonm]

Marshall,

Thanks for the replay. You are absolutely correct. It is funny though
because wheen I inherited this db I corrected several areas where data was
not normalized, however (for what ever reason) I did not interpret this area
as one of them. Probably because I never had tried to get to the information
that I was after today!

I think I will spend the rest of the week and see if I can break this data
out into a seperate table and update all my queries and reports (YUCK!)

Thanks again for the solution and advice!
Jason

 

[Marshall Barton]

Marshall,

Thanks for the replay. You are absolutely correct. It is funny though
because wheen I inherited this db I corrected several areas where data was
not normalized, however (for what ever reason) I did not interpret this area
as one of them. Probably because I never had tried to get to the information
that I was after today!

I think I will spend the rest of the week and see if I can break this data
out into a seperate table and update all my queries and reports (YUCK!)

Sometimes the YUCK factor for the "current" problem seems
hugh enough to prevent "doing it right". Been there, done
that and every time I wish I had bitten the bullet and fixed
things earlier.

 

[Jasonm]

I agree, and have tackeled that today.

I exported the data to a seperate table, reconfigured my forms to allow for
easy entry, but have run itno a but of a sticky wicket on the report
portion...

I am trying to use a crosstab query to "flatten" the data back out to
display it in a similar fashion to before and I keep getting a Jet Database
error.

I have a simple select query with the following code:
SELECT tblPumpHours.MRDate, tblPumpHours.PumpNo, tblPumpHours.PumpHours
FROM tblPumpHours
WHERE (((tblPumpHours.MRDate) Between
([forms].[FiltrationPlant].[StartDate]) And
(DateAdd("m",1,([Forms].[FiltrationPlant].[StartDate]))-1)));
which gives me the current months values

I then try and run this through a crosstab query with the following code:
TRANSFORM Max(qryMonthlyPumpHours.PumpHours) AS MaxOfPumpHours
SELECT qryMonthlyPumpHours.MRDate, Max(qryMonthlyPumpHours.PumpHours) AS
[Total Of PumpHours]
FROM qryMonthlyPumpHours
GROUP BY qryMonthlyPumpHours.MRDate
PIVOT qryMonthlyPumpHours.PumpNo;
and I get a Jet Database error. Can a crosstab query not get its data from
an open form?
If i "hard code" the between criteria everthing works fine. I am reading the
group to try and find a solution, but didn't get far before I saw your last
post and decided to follow up. I will continue looking and perhaps post the
question to the group a bit later.

Any thoughts?

Jm

 

[Duane Hookom]

You must always declare the data types of your parameters in crosstab queries.
[forms].[FiltrationPlant].[StartDate] date/time
--
Duane Hookom
Microsoft Access MVP

I agree, and have tackeled that today.

I exported the data to a seperate table, reconfigured my forms to allow for
easy entry, but have run itno a but of a sticky wicket on the report
portion...

I am trying to use a crosstab query to "flatten" the data back out to
display it in a similar fashion to before and I keep getting a Jet Database
error.

I have a simple select query with the following code:
SELECT tblPumpHours.MRDate, tblPumpHours.PumpNo, tblPumpHours.PumpHours
FROM tblPumpHours
WHERE (((tblPumpHours.MRDate) Between
([forms].[FiltrationPlant].[StartDate]) And
(DateAdd("m",1,([Forms].[FiltrationPlant].[StartDate]))-1)));
which gives me the current months values

I then try and run this through a crosstab query with the following code:
TRANSFORM Max(qryMonthlyPumpHours.PumpHours) AS MaxOfPumpHours
SELECT qryMonthlyPumpHours.MRDate, Max(qryMonthlyPumpHours.PumpHours) AS
[Total Of PumpHours]
FROM qryMonthlyPumpHours
GROUP BY qryMonthlyPumpHours.MRDate
PIVOT qryMonthlyPumpHours.PumpNo;
and I get a Jet Database error. Can a crosstab query not get its data from
an open form?
If i "hard code" the between criteria everthing works fine. I am reading the
group to try and find a solution, but didn't get far before I saw your last
post and decided to follow up. I will continue looking and perhaps post the
question to the group a bit later.

Any thoughts?

Jm