Max Function from Union Query - why simple function not working??!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Emelina Bumsquash - 25 Apr 2008 13:00 GMT

based on helpful advice i found on here already, i created a union query so
that i could find maximum and minimum values (i hadn't heard of this type of
query before but the database was designed in such a way that a union query
was necessary)

the union query works well - it returns one column with a large list of
values.

all i want to do is then find the maximum and minimum values in that list! i
thought it would be straightforward enough i.e. just create a new query based
on the union query and use the access 'max' function. however, for some
reason, the 'max' function is returning the number '7' even though 7 appears
nowehere in the union query list! this is driving me crazy! can anyone help?

Reply to this Message

Ken Sheridan - 25 Apr 2008 13:24 GMT

What are the data types of the columns in the tables used by the union query?
If text then a value of 7 would sort after any value beginning with a value
less than 7, no matter how long the 'number' in question. If the values are
numeric, but text data type, try returning the values as numbers with the Val
function:

SELECT MIN(VAL([YourColumn])) AS Lowest,
MAX(VAL([YourColumn])) AS Highest
FROM [YourUnionQuery];

If the MAX function is returning a 7 I'd be surprised if there isn't a row
with that value lurking somewhere in the union query's result set. What
happens if you open the union query in design view and sort it on the column
with the Z-A button? The highest value should be first on the list.

Ken Sheridan
Stafford, England

> based on helpful advice i found on here already, i created a union query so
> that i could find maximum and minimum values (i hadn't heard of this type of
[quoted text clipped - 9 lines]
> reason, the 'max' function is returning the number '7' even though 7 appears
> nowehere in the union query list! this is driving me crazy! can anyone help?

Reply to this Message

Emelina Bumsquash - 28 Apr 2008 11:11 GMT

Thanks Ken, this was a very helpful reply!

> What are the data types of the columns in the tables used by the union query?
> If text then a value of 7 would sort after any value beginning with a value
[quoted text clipped - 27 lines]
> > reason, the 'max' function is returning the number '7' even though 7 appears
> > nowehere in the union query list! this is driving me crazy! can anyone help?

Reply to this Message