Help with min/max qbe query

[ChristineMD]

Extracting data in a report by size.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Using the query grid, not writing SQL statements
<o></o>
Don't need to list every size, just the smallest size and the largest size - I'm using Min/Max in two separate columns to show the smallest size and the largest. <o></o>
The database has a numeric sort code ("size"), but of course the users don't want to see this, they want the label("sizecode"). Because the label for the sort code is text, it produces the ever so intuitive Microsoft sorting where 10 comes before 2. <o></o>
So using the sort code and min/max is successful. 1<SUP>st</SUP> column has the smallest size code, and the 2<SUP>nd</SUP> column has the largest size code.
<o></o>
but, What I want to do is sort by the numeric sort code, but display the label.

<o></o>
I tried using the statements below in the query grid in two separate columns,

SmSort: IIf(Min([size]),[Sizecode])<o:p></o:p>
LgSort: IIf(Max([size]),[Sizecode])

If I used "group" by with the above, got "cannot have aggregate function in group by clause

If I used "min/max" got "cannot hve function in aggregate expression"

Cannot not show totals, because other fields in the query depend on it.<o></o>
<o></o>
I also tried <o></o>

 SmSort:  [sizecode] (with min([size]) in the criteria field)

... but that resulted in zero results. <o></o>
<o></o>If anyone thinks the underlying SQL would be helpful, let me know, I'm just not sure it would!


Any ideas? Thanks in advance!

 

[Norie]

If you want both minimum and maximum in one query you might need subqueries.

I think we need to know more about what table(s) you have.

What is it you are trying to get the max/min size for anyway?

ie the largest/smallest what?

 

[ChristineMD]

I just figured out a way... seems kind of a cheat, but it works for me.

I used

1st column:   Sm: [size] & " " & [sizecode]  (with Min in the total row)
2nd column:   Lg: [size] & " " & [sizecode]  (with Max in the total row)

It seems to be "minning" and "maxing" by only the [size] field, so it's working. It just shows the size code AND the label in the results.


Thanks for taking the time to respond Norie!

 

[Norie]

Glad you've got it working.

You might need to watch out though.

If any of the size values go above 10 you might get some strange results.

I'm sure there's another way to do it 'properly' but if what you've got works that's the important thing.

 

[ChristineMD]

Even better, this works. Seems funny/hokey to me. But works is works.



Sm: Mid([size] & " " & [sizecode],7,15)
Lg: Mid([size] & " " & [sizecode],7,15)

 

[Norie]

Are the labels actually numbers stored as text or are some of them alphanumeric?

If they are just numbers just stick Val round the field and they should sort properly.

Val([sizecode])

Other conversion functions are available.

 

[ChristineMD]

The sort code IS numeric, but the label is alphanumeric - and doesn't match the sort code.


Also doing mids around the statement does NOT work. It eliminates some results, but not including the mids definitely is working for me.

Whee.

If you're in the States, have a happy 4th!