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

E

Emelina Bumsquash

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?
 
K

Ken Sheridan

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
 
E

Emelina Bumsquash

Thanks Ken, this was a very helpful reply!

Ken Sheridan said:
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

Emelina Bumsquash said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top