By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

How to multigroup a report without any condition? Access 2007

P: 40
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:
Expand|Select|Wrap|Line Numbers
  1. -------------------------------
  2. Desc | Entity |  Name  | rate | qty
  3. ---------------------------------
  4. xxxx    1        Mark    100     1
  5. xxxx    2        Mark    100     2
  6. xxxx    1        Joe     100     3
  7. xxxx    2        Kive    100     4
  8. xxxx    1        Ben     100     5
  9. xxxx    1        Ben     100     6
  10. -----------------------------------
  11.  
I want to run a report that looks like this:

Expand|Select|Wrap|Line Numbers
  1.   |   Mark    Joe   Kive   Ben
  2. =================================
  3. 1     100     300    0     1100
  4. =================================
  5. 2     200     0      400    0
  6. =================================
  7.  
  8.  
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
Apr 4 '11 #1

✓ answered by Lysander

I have deleted the table I made, so can't test this, but this code should give you the total per row.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([rate]*[qty]) AS Expr1 
  2. SELECT TEST.name, Sum([rate]*[qty]) AS [Total]
  3. FROM TEST 
  4. GROUP BY TEST.name
  5. ORDER BY TEST.Name DESC  
  6. PIVOT TEST.entity; 
  7.  

Share this Question
Share on Google+
7 Replies


P: 40
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 .
Apr 4 '11 #2

Expert 100+
P: 344
I managed to do this using a crosstab query.

I created a test table with your fields and then wrote the following SQL
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([rate]*[qty]) AS Expr1
  2. SELECT TEST.entity
  3. FROM TEST
  4. GROUP BY TEST.entity
  5. ORDER BY TEST.Name DESC 
  6. PIVOT TEST.Name;
  7.  
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.
Apr 4 '11 #3

P: 40
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([rate]*[qty]) AS Expr1 
  2. SELECT TEST.name
  3. FROM TEST 
  4. GROUP BY TEST.name
  5. ORDER BY TEST.Name DESC  
  6. PIVOT TEST.entity; 
  7.  
  8. What i got as a result is :
  9. ==========================
  10. Names   | 1   |   2
  11. =========================
  12. MARK
  13. KIVE
  14. JOE
  15. BEN
  16.  
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
Expand|Select|Wrap|Line Numbers
  1.  =SUM([1]+[2]) 
  2. also =[1]+[2]
  3.  
,

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
Expand|Select|Wrap|Line Numbers
  1.  =SUM([1]) 
  2.  
,
the value showing on running the report is Error ,
any idea how to deal with it
????????
Apr 5 '11 #4

P: 40
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 :)
Apr 5 '11 #5

Expert 100+
P: 344
I have deleted the table I made, so can't test this, but this code should give you the total per row.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([rate]*[qty]) AS Expr1 
  2. SELECT TEST.name, Sum([rate]*[qty]) AS [Total]
  3. FROM TEST 
  4. GROUP BY TEST.name
  5. ORDER BY TEST.Name DESC  
  6. PIVOT TEST.entity; 
  7.  
Apr 5 '11 #6

P: 40
Thanks a lot, you really made my DAYYYY.

Appriciate your follow up ..
Apr 5 '11 #7

Expert 100+
P: 344
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:)
Apr 5 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.