Create multiple page form or report based on one big query

[ice051505]

Hi guys! Thanks so much for being such helpful all the time

I got another question here: I have a query which contains 7 column: year, week, day, species, area, type, numberOfCount

I have a report to display the information from the query: week, type are the row head; area are the column head, numberOfCount is the value.

I need a multiple pages of report to display the data in different year and species, like Summery of numberOfCount in species X in 19XX-19xx

Right now it works perfect with single year selection and single species selection base on the query. But if I selected multiple year and species, it will always pops the msgbox ask me to enter the parameters of year and species. And it will only give me single page after I enter the parameter.

Can anyone help me with this?

Also can anyone tell me how to refer to the species value in the query to the form or report, such that I can put in the report header page one: Summery of numberOfCount in species x in 19xx-19xx; in the report header page two is Summery of numberOfCount in species Y in 19xx-19xx

Thanks a lot!!!

[June7]

The report is based on a CROSSTAB query?

If you want data by year and species then those need to be row heads as well. This will breakdown the count by year/week/species/type. Then use report Sorting and Grouping with aggregate calcs in group footers.

[ice051505]

Thanks June7 and Yes! it is crosstab query, Could you give me more detail on how to use report sorting and grouping with aggregate calcs in group footers? what do you mean here?

Again, Thanks!

The report is based on a CROSSTAB query?

If you want data by year and species then those need to be row heads as well. This will breakdown the count by year/week/species/type. Then use report Sorting and Grouping with aggregate calcs in group footers.

[June7]

You are using Access 2002. Grouping & Sorting tool has changed a lot.

Access Help has guidelines on building reports with Grouping and Sorting with summary calcs in textboxes in header/footer sections. Search Help on 'report grouping sorting'.

Or Google the topic.

[ice051505]

Thanks a lot, I will give a try

[June7]

Also, creating reports to run perpetually based on CROSSTAB query can be tricky because they can be dynamic in structure. Can't always count on the same fields to be generated. However, there are techniques to accomplish.