Help with Grouping and Calculating in report

[xeen20]

Hi ,
i am new to access and i am struck with a prob in building a report.
i have a table with name : Books
and fields
Title ------Type-------Page-----Publisher---Scanner----RFBD
Book1----Simple------199-------- -1 -------- --------- --
Book2-----Hard-------282-------- -------- -1 --------- --
Book3 --Very Hard -- 343-------- -------- ------------ -1 ---
Book4-----Hard ----- 534 ------ -1 -------- 0 --------- -1 ----
Book5----Simple ---- 232--------- --------- -1 ------ -1 ---

here the feilds Publisher, Scanner, RFBD are Option Buttons

i attached my test data base....

Now i need to create a report showing like


-------------Type--------Pages

Publisher
-----------Simple -------- 199
-----------Hard ----------- 534
-----------Very Hard ------- 0
----------------Total --- 733
Scanner
-----------Simple -------- 232
-----------Hard ----------- 282
-----------Very Hard ------- 0
----------------------Total --514
RFBD
-----------Simple -------- 232
-----------Hard ---------- 534
-----------Very Hard ----- 343
-------------------Total -1109

Total Pages = 199+282+343+534+232 = 1590

my db file is attached

 

[ajetrumpet]

Hello again Xeen,

Have you looked at THIS??

Your going to have to create more than one query and group them separately on one field, then UNION them together.

 

[ajetrumpet]

Actually, now that I think about it, I didn't include any info. about UNION in that FAQ. Maybe I should have...oh well.

Anyway, see what this does (as a basis for a report)...
(You can sum the groups in the report with group leveling and summaries in the wizard)

 

[xeen20]

Very Very Thanks Aje, I know u will be there to help me , Hey i am not able to get the Total as show in the example, Could you plz help me in that.
Like
total Pages is only the Sum Of All Pages in Table. its not the grand total of the Report generated.

 

[Rabbie]

You need to change the type of the field Pages to Number and not text. Then the query wizard will help you design the query. Try building a crosstab query which should give you what you want

 

[ajetrumpet]

Hey i am not able to get the Total as show in the example, Could you plz help me in that.

Ok, I think you're asking for help with obtaining the numbers (total pages for each "section")?? If that's what you're asking about, are you having trouble reading the SQL in the example queries?? Your syntax should be the same.

Like

total Pages

is only the Sum Of All Pages in Table. its not the grand total of the Report generated.

I'm not understanding this..."Total Pages" WHERE?? It's not the grand total of the report?? It sounds like you're getting a grand total on the report, but not in the queries. Could you elaborate a bit more on this?? Need help...

Attached is the same file I gave you, but with a report in it. I am not sure if that's what you're asking about (or if it's the queries themselves), so it is there for reference...

 

[xeen20]

Ok, Thanks Rabbie and Aje

Here it is Aje

Lets think i have only 4 books and each have 100 pages.

i am distributing to 3 people Publisher, Scanner and RFBD


one book is distributed to different combinations and in the repot we are getting total no of pages we are distributed.( This is Fine)
But i would like get a field that say i have only 4*100 = 400 pages ( this is the Sum of pages in table)

example
Table::::::::::::::::
Title ------Type-------Page-----Publisher---Scanner----RFBD
Book1----Simple------100-------- -1 -------- ----------- --
Book2-----Hard-------100-------- ---------- -1 ---------- --
Book3 --Very Hard ---100------- -------- -------------- -1 ---
Book4-----Hard -------100 ------ -1 --------- 0 -------- -1 ----


Report::::::::::::::::::::::::::::
-------------Type--------Pages

Publisher
-----------Simple -------- 100
-----------Hard ---------- 100
-----------Very Hard ------ 0
----------------Total ----- 200
Scanner
-----------Simple ---------- 0
-----------Hard ----------- 100
-----------Very Hard ------- 0
----------------------Total 100
RFBD
-----------Simple -------- 0
-----------Hard ---------- 100
-----------Very Hard ----- 100
-------------------Total - 200

Grand Total is 500 ( i could get till here)


Total Pages = 100+100+100+100= 400 ( i am talking about how can i get this line)( help me with this line)( i.e sum of pages in table)
I know u can do this so Thanks in Advance.

 

[ajetrumpet]

I really don't want to think that hard about joining a sum function into all of this....

Just write a query summing the page column, add a text box to the report with a control source of the query.

Anyway, try and get the knowledge you need from this...

 

[xeen20]

Thanks Aje. It worked now.