getting a MAX field without using subquery

[Crowley16]

I would like to use a query in a report, which is something like:

select (select max(fld) from tbl) as maxFld, * from tbl

however I need to get rid of the subquery because this is used in the recordsource of a report with multiple level groupings.

Any ideas, on achieving the same thing without having to use that subquery?

Thanks

--------------------
Procrastinate Now!

 

[ZmrAbdulla]

How about DMax?
=DMax("YourField", "YourTableName")

Zameer Abdulla
Visit Me (New Look & style)

 

[Crowley16]

the thing is, the query is already running really slow, so any way of doing this without useing any of the D stuff?

I was hopeing there's something to be done with fancy joins, but I'm not great at joins...

--------------------
Procrastinate Now!

 

[ZmrAbdulla]

Join Magicians" like PHV or dhookom should jump into this thread!!

Zameer Abdulla
Visit Me (New Look & style)

 

[Crowley16]

that's what I'm hoping for

--------------------
Procrastinate Now!

 

[ZmrAbdulla]

Zameer Abdulla
Visit Me (New Look & style)

 

[Crowley16]

I've worked it out...

basically, don't use a join, and store the value in a query somewhere...

like:

select QryMaxValue.Value, tbl.Flds
from QryMaxValue, tbl

easier than I thought...

--------------------
Procrastinate Now!

 

[traingamer]

Technically, that IS a join.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Technically, that IS a join
I'd say that is a cartesian product.

 

[r937]

technically, a cartesian product is a cross join

hence, it IS a join



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

Good point rudy.