sort order by SUM on a report 1

[jwa6]

hi all
I have a rpt based on a qry

fields are
county
exp type
cost
period - there are 3 periods I am dealing with
that means 3 cost amounts per grouping ( county)

this is the sum at each group level ( county)
=Sum([Cost])

I need to order by highest to lowest group sum amount.=Sum([Cost])

The query had alot of tables ..so Im lost on how to group the query to get the ag function Sum to work. I would like to order by the summed cost in a county as the report rolls out....but being grouped by county, doesnt seem that I can resort the grouping sort order.

thoughts in the ether?
thanks
jim

 

[dhookom]

Can you create a totals query that groups by County and sums Cost? Then add this query to your report's record source query and join the County fields. Add the SumOfCost to the report's fields so that you can use it to sort.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwa6]

yes

I have thought of a totals q by it self. Then build a detail q off of that one hooking up on county.
Can you have 2 querys as the recors source to a report>?
And, thanks for your reply dhookom.
jim

 

[dhookom]

You can combine two queries into a third query. Then use the third/final query as the report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwa6]

I think that is what I was leaning toward. -- a trail of querys. I assumed that Access had more features than it does.
In Oracle ( which I usually work in) I could write an in line view and then select off the county and add the detail records all in one sql.

Nothing under the hood and fast!

Im going to be do more access ( which I think is a glorified user tool and not conducive to furthering ones career?).
Is there anywhere I can get sql for access information? I have a 'bible' on Access which doesnt discuss sql at all.
Thanks for your post!

jim

 

[dhookom]

A good Access programmer can make a fairly good career of developing applications.

There are some good books listed at

http://www.viescas.com/Info/books.htm.

You could create the county totals all in one query with a subquery. I'm not sure a report would allow you to use this result from the subquery as the control source for sorting and grouping.

Don't expect the Access implementation of SQL to be as powerful or functional as Oracle, SQL Server, or other database server technology. However, don't expect to build rich forms, reports, and other user interface objects Oracle or MS SQL Server.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwa6]

You could create the county totals all in one query with a subquery. I'm not sure a report would allow you to use this result from the subquery as the control source for sorting and grouping.

Ill try and let you know

thanks for the list of books -- and I appreciate it
jim