Arrays ... multi

[ceebaby via AccessMonster.com]

I am a mid level access user and have been asked to provide reports for a
database designed by someone else in my office.I am not a programmer but
have a keen interest in access and use it regulary

I am trying to create a report based on a query, showing respondents
answers to a crime and antisocial behaviour questionnaire in a specific
area. The report must show the breakdown of numbers of respondents by
gender and how they have marked specific types of crimes by priority.

I have tried using an array to hold the different types of crimes (fields)
and have used a Do loop to loop through the records,

and dcount to count the numbers of respondents who have given the said
crimes a specific priority. Eg the crime types are as follows: street
crimes, domestic violence, burglary etc.

There are 11 fields of these crimes and the respondent was asked to insert
their priority with 1 being the highest to 11 being teh lowest.

basically I need some help on both declaring the array, I have tried the
following and am stuck

eg dim CrimeType ()as variant

crimetype= array("[street crime]", "[domestic violence]" etc etc.
crime type = priority 1, crime type = priority 2 to be able to use these in
my DCount.

I have used dcount to count how many female respondents set street crime as
priority 1, priority 2 etc.

from my research i believe a multidemnsional arrays and loops might be the
best way to provide the results of this report. Currently my attempt was to
provide 121 expressioned text boxes using dcount.11 crime fields and 11
prioritys
Surely there must be simpler way to achieve these results, if this is the
way to go is there also a way to output the results to a report showing the
crimes against the priority. Sorry for the long post any help will be most
greatfully received.

 

[Duane Hookom]

"11 fields of these crimes " sounds un-normalized. You should normalize the
records (possibly using a union query) and then create a crosstab query to
get your results. You might want to look at the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There is
a simple crosstab "report" included.

 

[cee esimaje via AccessMonster.com]

thanks for your response Duane
The database is normalized the query is based on a 1 to many relationship
between personal details table where recordID is the primary key and the
crime statistics table. I have tried to attempt what you have suggested but
hve still come unstuck because the preport must show the 11 crime type
fields set against the 11 priorities. A crosstab query will only allow 3
row headers, I cannot work out how to show the other 8 crimes in the
crosstab.

I need the report laid out in a crosstab style as shown below

By Gender - Female
total respondents

priority 1 priority2 priority3 etc priority11
street crimes 56 98
burglary 45 78
domestic violence 160 22
etc
etc

Can I have a crosstab with more than 3 columns? This was why I thought
outputting an multidimensional array might have been a better method
Thanks once again for your help

 

[Duane Hookom]

A crosstab can generate up to 255 columns. Yours look like you should have
Gender and Crime as "Row Headings", Priority as "Column Heading" and Count
of something as the "Value".

You are no where near the query limits.