Access Report Generation

[Shalini Bhalla]

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) ........

[nico5038]

The structure isn't quite clear.
What's the table structure you use for generating this report ?
Nic;o)

[Shalini Bhalla]

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 ...?

[nico5038]

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)

[Shalini Bhalla]

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

[Shalini Bhalla]

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).

[nico5038]

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)

[Shalini Bhalla]

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 ...

[nico5038]

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)