Subreport Required?

[DreamGenius]

I'm out of my depth here. I don't use Access Reports as a rule because I prefer Excel, but this is what the client wants.

I've built frmReport which uses controls for user input to modify SQL behind reports and gives users the option of running an Access Report, with less data in it, or an Excel Export, which is just a dump of the whole query.

They want to group multiple dates produced by the query to be grouped together so that the duplicated data, such as who, what and where only appears once on one line, with the when broken down. That sounds like a subreport to me.

I've attached a thinned down version of the database, containing only this functionality and enough data that, using the default reporting options, you can see the problem.

Any help appreciated.

 

[lark_tilly]

Can you re-post the mdb in version 2000?

 

[DreamGenius]

Can you re-post the mdb in version 2000?

In a word, no. I've tried and it's taking far too long to convert and I've already had warning messages. This was built in 2002 from the outset so I see no benefit in down-grading it, other than I won't be able to have your input, as I'm guessing you don't have a new enough version of Access to be able to open it?

 

[Simon_MT]

I can't open your database in 2007 but it seems to me that this is a case of bottom up!

You start from the table WHEN, this becomes the Detail and then put groups on the WHO WHAT WHERE so that the WHEN shows every occurance and WHO WHAT WHERE only appear once.

Simon

 

[DreamGenius]

You should imagine a sheepish expression on my face as I post this. Simon's reply above has lead to a solution; I was looking for something somewhat more complex and therefore overlooking the blindingly obvious!

My stumbling block was not really understanding Grouping in Reports. By Grouping by one of the duplicated data items, and moving the other duplicated data items into the same Group, I've solved the problem.

Thanks to Simon for the seed of the solution and everyone else who thought about it at all.