select max value from multiple fields within a record

[jkyte]

Table contains a single record for each part number with 12 separate fields
for units sold in each of the last 12 months. I want to perform a select
query that yields the maximum units sold in 1 month for each part number. In
Excel this is easy as Max will compare both vertically (i.e. within a
field/column but accross records/rows) and horizontally (i.e. within a
record/row but accross fileds/columns). Max in Access appears to only allow
the vertical comparison feature. Is there an easy way around this limitation
that can be done in an Access query without a giant nested IF statement or
the equivalent of a DO UNTIL loop?

 

[[MVP] S.Clark]

Yeah, the funny thing about Access queries is that they expect the data to
be normalized. The nerve of them. Maybe someday, all of our data can be
haphazardly placed randomly about the screen, but for now, strict column
definitions are what we're stuck with. It's a pain sometimes, but it really
is for our own good. Maybe try restructuring your data to conform to these
outlandish rules, and the queries might behave a little better. No
guarantees, though.

Ok, I've pulled my tongue out of my cheek, and here's the serious answer.
What you have is called a crosstab. Access can create crosstabs, very
easily, from normalized data, but it cannot reverse engineer them. So,
short of writing your own VBA procedure, or a handful of queries, to handle
it, you're somewhat stuck.

Good luck,

 

[John Spencer (MVP)]

To expand on Mr. Clark's response.

You can temporarily normalize this data by using a UNION query and then doing
your Maximum value query against the Union Query.

SELECT PartName, Column1
FROM YourTable
UNION
SELECT PartName, Column2
FROM YourTable
UNION
....
UNION
SELECT PartName, Column12
FROM YourTable

Save that as QueryA

SELECT PartName, Max(Column1)
FROM QueryA