trouble with query or grouping report 1

[LikeThisName]

have a report that returns the sum of values
by year and country.

i need a report that returns the sum of values by year and the sum of values descending

this is what my report looks like
year numb of records total value
2002
Australia 85 $5000
Belgium 30 $20000

what i need is

year 2002
Belgium 30 $20000
Australia 85 $5000

the sum of values is based on country.
i need to group on country but not sort descending or ascending on country, just descending on sum of value per country.

i know i must be overlooking something simple, but i cant seem to figure it out.

thanks in advance



LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

Is the total value calculated in the query? If so, you can use this in your sorting and grouping. If it is not calculated in the query, tell us why not?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

when i try to calculate it in the query i get an error message

the query is based on three tables. the tables are a mess.

how would i calculate a total of values that are from the same year and country in my query

i can see how it is calculated in the report. its the sum of values that are based on a country grouping within a year grouping.

can you help me instruct my query.
i right click in the design query and select Totals
and it allows me to sort by group but i cant seem to get it to work and just return one line for
that counts records of a country in ayear and sorts descending by its value.

LikeThisName <- ? Sorry It's Taken =)

 

[LikeThisName]

when i try sum of value in my query it just returns the same number of value do i need to put something in the condition.
do i need to have a sub report with in my report perhaps?



LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

LikeThisName,
Do you have any field or table names. Creating a totals query and grouping by specific fields is generally quite easy. We have no idea what you have tried and if it didn't work we could only guess.

It would also help to see the SQL view of your totals query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

Field Names: Year, Country, Value

the report i have now

groups by Year then groups by Country

The report reads like above, where Country is counted (how many times returned in query with that year) and Value is by the Sum (based on a country in that year)

unfortunately though the sum is based on the the two groupings.

I need to generate the same lines as above just sort it by Year then country Descending (by highest Value)

i think i am making this harder than it is.

im thinking the problem is in the report. i need to utilize my detail section or footers like i did last time when you helped me calculate percentages.

given the fields year, country, value
how would you return this in SQL. they are from different tables and when i try to calculate in SQL i get error saying im not using some field that is linking the tables.

Thanks for your efforts.





LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

Create a query with a sql like:
SELECT [Year], Country, Count(Country) as NumOf, Sum(Value) as SumVal
FROM tableA
Group By [Year], Country;
Then, in the report,open the sorting and grouping dialog and group by Year (with header and footer) and then by Country.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

i don't want to sort by country, i want to sort by sumVal descending. im sorry i didnt make it clear.

i already have what you put, thats why included the sample of desired report return. I am going to have help come in tomorrow, i will post the solution when i get it, thanks for trying/responding. maybe you see why now i have been having a difficult time.









LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

Sorry I forgot the sort. Just sort first by the year and then by the SumVal.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

reply unfortunately my query won't let me do ' SELECT sum(value) as SumVal

says i can't have an aggregat function that doesn't include id_ntry, a field used to join tables in ym query.




LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

Where did id_ntry come from and why? It wasn't in your field list that you provided and it wasn't in your report and it wasn't in the sample SQL that I provided.

This is near impossible to help if you reveal only a piece at a time as it becomes an error message.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

DUANE, THANKS FOR YOUR HELP.

I needed to remove unnecessary fields in the query.
and change when I did a Totals Query by default it adds Group By, I need to change two of the conditional fields to where.

Thank you for sticking with me though I did not give you adequate information to solve it, you still helped me rethink my approach. Thank you so much.





LikeThisName <- ? Sorry It's Taken =)