How to multigroup a report without any condition? Access 2007

[Scorp Scorp]

Dear All,

I came up on such a report and couldnt figure out how to design it also to code any of it.

I have one table:

-------------------------------
Desc | Entity |  Name  | rate | qty
---------------------------------
xxxx    1        Mark    100     1
xxxx    2        Mark    100     2
xxxx    1        Joe     100     3
xxxx    2        Kive    100     4
xxxx    1        Ben     100     5
xxxx    1        Ben     100     6
-----------------------------------

I want to run a report that looks like this:

  |   Mark    Joe   Kive   Ben
=================================
1     100     300    0     1100
=================================
2     200     0      400    0
=================================

filtering by entity is easy , but filtering or sorting the names horizantaly, .... was not at all, like have no idea how to.

Any body can help ????

thanks a lot

[Scorp Scorp]

thinking of :
On load event of this report take the value of the textbox that displays the entity, then call a function to calculate the total for each name,and display the value in the corresponding text boxes ... failllll.

on load event only the first value will returned in the textbox that display the entity, ie: 1 i couldnot get the rest of the entities.



sorrry i clicked reply rather than save .

[Lysander]

I managed to do this using a crosstab query.

I created a test table with your fields and then wrote the following SQL

TRANSFORM Sum([rate]*[qty]) AS Expr1
SELECT TEST.entity
FROM TEST
GROUP BY TEST.entity
ORDER BY TEST.Name DESC 
PIVOT TEST.Name;

This gives an output of

entity Mark Kive Joe Ben
1 100 300 1100
2 200 400

You can base your report on the cross-tab query.

[Scorp Scorp]

thank for the reply Lysander,you almost made my day .
i just selected test.name so i got the following:
which made no difference, just changed the look of the result

TRANSFORM Sum([rate]*[qty]) AS Expr1 
SELECT TEST.name
FROM TEST 
GROUP BY TEST.name
ORDER BY TEST.Name DESC  
PIVOT TEST.entity; 

What i got as a result is :
==========================
Names   | 1   |   2
=========================
MARK
KIVE
JOE
BEN

That was perfect, but i couldnt get the sum of each coloumn as a total ie. under 1 i need a total number wich sums up all the ones and at the end of each row also the total ie: total for mark.

I tried puting a text at end of each record /row of value

 =SUM([1]+[2]) 
also =[1]+[2]

,

seems giving the total of all the the values under thos 2 coulomns not , all the values per row.

Same trial to get the total for per each colomn, of value

 =SUM([1])

,
the value showing on running the report is Error ,
any idea how to deal with it
????????

[Scorp Scorp]

Solved the total per each colomn , i shoulda place the textbox =SUM([1]) in the Report Footer Section not in the Page footer , my mistake .

But still need the total per row ... cant figure out how .... any ideas are welcomed :)

[Lysander]

I have deleted the table I made, so can't test this, but this code should give you the total per row.

TRANSFORM Sum([rate]*[qty]) AS Expr1 
SELECT TEST.name, Sum([rate]*[qty]) AS [Total]
FROM TEST 
GROUP BY TEST.name
ORDER BY TEST.Name DESC  
PIVOT TEST.entity;

[Scorp Scorp]

Thanks a lot, you really made my DAYYYY.

Appriciate your follow up ..

[Lysander]

You're welcome. I find crosstab queries confusing and the crosstab query wizard is one of the few wizards I actually use. If you have not come across it, it is the third option when you go to create a new query. "Crosstab query wizard"
Makes this sort of thing a lot easier:)