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

Access Report Generation

100+
P: 190
I need to generate a report using crosstab query....
i want report as follows.....
main option1
sum of (opt1)
sum of (opt2)
sum of(opt3)
sub option 1
opt 1
opt 2
opt 3
Sub option 2
opt 1
opt 2
opt 3
main option2
sum of (opt1)
sum of (opt2)
sum of(opt3)
sub option 1
opt 1
opt 2
opt 3
Sub option 2
opt 1
opt 2
opt 3
can any body help me in getting way to get sum of(opt1) ........
Dec 29 '07 #1
Share this Question
Share on Google+
8 Replies


nico5038
Expert 2.5K+
P: 3,072
The structure isn't quite clear.
What's the table structure you use for generating this report ?
Nic;o)
Dec 30 '07 #2

100+
P: 190
i have 3 tables

1) main_ind_mast (contains Main Industries)
main_ind_code main_ind_name
0 Chemicals
1 Transport

2) ind_mast (contains subindustries for main industries)
ind_code ind_name Main_ind_code
01 xyz 0
02 hij 0
03 abc 1
]3) data (contains detail info of individuals)
state name ind_code gender emp_code
AP mr.ram 01 m 1234
AP raKhee 02 F 1233
HP mr.rami 01 m 1235
HP mr.raheem 02 m 1236
HP mr.rami 01 f 1237
HP mr.raheem 02 m 1238

i m trying to put things formatted but things are getting unformated in this window why so ...?
Dec 30 '07 #3

nico5038
Expert 2.5K+
P: 3,072
OK, your table structure looks like:

1) main_ind_mast (contains Main Industries)
main_ind_code (*)
main_ind_name

2) ind_mast (contains subindustries for main industries)
ind_code (*)
ind_name
Main_ind_code (FK)

3) data (contains detail info of individuals)
emp_code (*)
state
name
gender
ind_code (FK)

The (*) fields should be the unique identifier and (FK) stands for ForeignKey and point to another table.
First create a query that Joins these tables by Main_ind_code ("main_ind_mast" and "ind_mast" ) and ind_code ( "ind_mast" and "data" )
Run this query and make sure you have as many rows as there are in the "data" table. If not, then there's something wrong with the uniqueness of your key fields (those marked by (*) )

Now use this query to create a new query with the type "GroupBy" (use e.g. the "E" looking button) and place the ind_code and place the gender field twice.
Change the first Gender field into:
GenderM:IIF(Gender='m',1,0)
and the second into:
GenderF:IIF(Gender='F',1,0)
Finally change the "GroupBy" under these two fields into "Sum"
Run this query to see the count per gender per ind_code.

Nic;o)
Dec 30 '07 #4

100+
P: 190
ok, thanks a lot ..
but my problem is that i want to generate report , how to get these total in the begning of Ist grouping
Dec 30 '07 #5

100+
P: 190
one more thing that i forgot to tell you is that i want it state wise & Main_ind wise report i.e

if we make crosstab query main_ind_name , ind_name & gender will be rowheading , state will be column heading and sum of emp_no will be value

now at each change of Ist level grouping(i.e main_ind_name) i want the sum of each IIIrd level grouping(i.e gender wise Male & female).
Dec 30 '07 #6

nico5038
Expert 2.5K+
P: 3,072
I was starting to generate a query to produce the results.
The totals (as indicated before) can be places in a report section's header or footer when needed by adding an unbound field with a:
=Sum(fieldname)
So first create the query(queries) to get the "basic" data and then use the Grouping levels (button looks like [= ) to add the header and/or footer for the totals.

Your description indicated that data was duplicated, thus start with making correct queries... :-)

Nic;o)
Dec 30 '07 #7

100+
P: 190
no i m not getting it ......


the main problem is that i want query to be in crrostab form to generate report
pls allow me to send u the sample of my report and data structure thrue mail ...
Dec 31 '07 #8

nico5038
Expert 2.5K+
P: 3,072
You can post an attachment here. Just create a new message and after Submitting use the edit link (bottom right) to add an attachment.

Nic;o)
Dec 31 '07 #9

Post your reply

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