How to Summarise Multiple Fields in a report?

[khwaja]

I have a table with HR records of employees. Employees in varous department are judged based on their competency in various areas. I created an option group for each of 10 such competencies. So the table captures either 1 or 2 in each competency text field depending on whether eployee was judged to be needing further development or had strength. This works well on the form but when user asked me to produce a summarised report by departments as to find out how many or percent of employees were in each competency requiring development or had strength, I was completely knocked out. I tried crosstabs but that only works for one competency forone query. I then tried puttting all competency fields in a report group to manually calculate but this did not work either.

I will really appreciate if someone could help me with this. As a guide, following is the template I need to work on:

Competency 1 Comptency 2 Competency 3
Dev Strength Dev Strength Dev Comptency
Dept1
Dept2

The data should be represented either as number employees or percent of total for the Dept.

Cheers

AK

 

[KenReay]

Hi

You need to show the structure of your table(s), before anyone can comment sensibly on this Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[khwaja]

Thanks Ken. It is a very simple database with no further relationships apart from where I needed to have detailed academic record or career history as seperate pop up forms using 1toM relationship. All fields I indicated are part of the same table. I am not sure if you are hinting at creating another table but even if I do I might still have the same problem ie Departments coming from the main table and linked to competencies but summarisation of these competencies is the crux of problem. I will be happy to calrify further if necessary. Do you have any ideas how could this be achieved?

Regards Cheers

AK

 

[randysmid]

Hi AK,
You may want to try working backward from the final report you wish to create. For instance, your final query results will look like this:

Dept1 Comp1Dev Comp1Strength Comp2Dev etc.

So, with this in mind, think of using more than one query to get the results you desire. Some of my more complicated reports use 8 queries!!! HTH,
Randy Smith
California Teachers Association

 

[khwaja]

Thanks Randy. I somehow did not receive the email alert of your response. I appreciate the insight. I thought it was unprofessional to use an excessive number of queries to create a report along the lines I indicated. But I guess I will take your point. By the way, my fields are in True/False format and I am finding it impossible to count either tue or false values. Is there any limitation in Access 97 on counting logical values? How do you get around this problem? Cheers

AK