choose maximum of several fields in each record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with times recorded for various events for each record. Some
are null. I am trying to create a query to select the latest time. An 'iif'
statement would be extremely long and complicated by the null values. Any
suggestions?
 
It depends on the type of field you used to store the data and the format you
used.

Post the information so it can be analyzed.
 
Claudio said:
I have a table with times recorded for various events for each record. Some
are null. I am trying to create a query to select the latest time. An 'iif'
statement would be extremely long and complicated by the null values.
Click to expand...


This is a very messy problem caused by your table's
spreadsheet like design. With your current design, the
complicated nested IIf or maybe a user defined function is
about the best you can do.

Generally a table should not have multiple fields that can
be compared like that. The terms that are used to describe
a correctly designed set of tables are Relational and
Normalized where you should adhere to the rules of the first
three Normal Forms.

Your situation sounds like you should have a separate Events
table with one **row** for each event. With this kind of
design, getting the answer you want is a simple Totals type
query using the Max function.
 
I have a similar problem. I have a table that consists of exhibitors' hotel
room requests over the span of 10 days of move-in, show, and move-out. The
data comes as a flat table from an online form with the number of each type
of room for each day, plus a total number of rooms field for each day. I
need to pull out of each record the total number of rooms they need on the
day they request the most rooms (e.g., if they request 10 rooms on day 4, 11
rooms on Day 5, and 8 rooms on Day 6, the query would pull 11 for peak rooms.
If they request 10 rooms on Day 4, 8 rooms on Day 5, and 4 rooms on Day 6,
the query would pull 10 rooms.) The exhibitors' requests don't all peak on
the same day or it would be easy.
 
I have a similar problem. I have a table that consists of exhibitors' hotel
room requests over the span of 10 days of move-in, show, and move-out. The
data comes as a flat table from an online form with the number of each type
of room for each day, plus a total number of rooms field for each day. I
need to pull out of each record the total number of rooms they need on the
day they request the most rooms (e.g., if they request 10 rooms on day 4, 11
rooms on Day 5, and 8 rooms on Day 6, the query would pull 11 for peak rooms.
If they request 10 rooms on Day 4, 8 rooms on Day 5, and 4 rooms on Day 6,
the query would pull 10 rooms.) The exhibitors' requests don't all peak on
the same day or it would be easy.
Click to expand...

If you have ten fields for the ten days... you'll find it a LOT easier if you
normalize the data, either by moving it into a normalized table or using a
"Normalizing Union Query" to string the data out so that it's one number per
record rather than one number per field.

You don't describe much about your table (primary key, fieldnames, etc.) but
you'ld need to go into the SQL window and edit something like

SELECT RequestID, (1) As TheDay, Day1 AS RoomsNeeded
FROM tablename WHERE Day1 IS NOT NULL
UNION ALL
SELECT RequestID, (2) AS TheDay, Day2 FROM tablename WHERE Day2 IS NOT NULL
UNION ALL
SELECT RequestID, (3) AS TheDay, Day3 FROM tablename WHERE Day3 IS NOT NULL
UNION ALL
<etc through the ten day fields>

This will give you the values in your spreadsheet, each in a record by itself,
with the daynumber in the field TheDay. You can then very easily use a Totals
or Top Values query to find the Max of RoomsNeeded and which day it comes.


John W. Vinson [MVP]
 
I've separated them out into separate records, but I still can't update
another record with the maximum value. It keeps telling me I need to use an
updatable query. Any way around this?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
 
I've separated them out into separate records, but I still can't update
another record with the maximum value. It keeps telling me I need to use an
updatable query. Any way around this?
Click to expand...

No Totals query, nor any query including a Totals query, is ever updateable.
Normally one would NOT store the maximum value of a series of records in any
table - that calculated field will become *wrong* the moment someone edits the
table to change some value to a larger one!

Could you explain the context? What data does this table represent, and just
what are you trying to accomplish?

John W. Vinson [MVP]
 
Back
Top