Is this type of multiple column report possible?

[SammiH]

Sorry in advance as I am totally self-taught and need very basic explanations if you can help!
Can I present the attached table in Excel in a database report? If so, how would I design the report/ query?
I have included the basic table structure that I have at the moment (it is a very large database that has worked fine over many years, but now I'm stuck!)
The "performance" field in the Performance table is a "number" field and cannot have y/n entries - I guess I can get around this by having 1 or 0 as the entries instead? What I am having trouble with is that I have multiple rows and columns, as the service wants to see each Site separately with the blocks for that Site listed below, by date, but with all performance indicator results under the date column. My other reports only have one column heading whereas this apparently needs several??!!

 

[vbaInet]

So are you saying you've done this already but with one column?

What you need is a multi column report (i.e. 3 columns) where the Date range is between Sep-10 and Nov-10.

http://office.microsoft.com/en-us/access-help/multiple-column-reports-HA001156534.aspx

http://www.databasedev.co.uk/multi-column_report.html

In the report (using SORTING AND GROUPING) you group by Site ID.

As for the number field, you can camouflage the values using the IIF() function or Choose(), the latter would be preferrable since you have only 3 values.

IIF():
http://www.techonthenet.com/access/functions/advanced/iif.php

Choose():
http://www.techonthenet.com/access/functions/advanced/choose.php

 

[SammiH]

Thanks for your very quick response - the report at the moment has a drop-down box to select which service we are looking at (eg Caretaking), the date field as column headings and the block and indicator fields are row headings, so the performance appears on separate lines. What they want is for the date and indicator fields to be column headings with each site grouped separately and blocks as row headings.

 

[vbaInet]

Let me see a screen shot of what you've already got.

 

[SammiH]

A sample is attached for the ASB service- each indicator is on a separate page (title in red). For caretaking, there are only three indicators, so they want them on the same page under each month, so month as column heading and indicator underneath each month, also as a column heading, but still with the sites down the side as row headings. Hope this makes sense.

 

[SammiH]

An update and a bump - I've managed to create what I want with a Form using Pivot Table wizard, which successfully opens in Excel where I can update the data using refresh. What I need to do now though is :
1. Refresh the form within Access rather than in Excel
2. Create a report in Access using this data - is there a way of opening the form/spreadsheet in an Access report, so it can all be created from within Access? Someone else with no knowledge of Access at all uses the database to create reports, then pdf's them. I tried saving the form with saveas report, but this just outputs the pivot table as an image with all the grids, dropdowns etc from the excel spreadsheet.
Thanks

 

[vbaInet]

You might be able to achieve what you want using a crosstab query instead. Have you had a look at that? However, comparing your current report to your desired report I don't see how the "y" and "n" are calculated. So maybe you want to shed more light on that.

Regarding your other questions:

1 - Me.Requery should be able to "refresh" the form
2 - The pivot table is simply a view. So even if you base a report on a form and use the wizard it will build the report based on the controls and most importantly the record source. A report doesn't have have a pivot table view so no it can't be done.

 

[SammiH]

Hi
Thanks for reply -sorry for not posting sooner as I don't work the end of the week. I have tried crosstab query, but not sure if it will work as I need more than one columnheader, and crosstabs only allow one - be grateful if you could explain how I can use a crosstab to achieve the report.
The y/n fields are user inputs rather than calculated; none of the fields are calculated - they are all the result of form inputs, so hopefully this makes it simpler, though it would be useful to add an average % score for each site.
I understand now about pivots not being useful for reports, so thanks for that. I've sorted the refresh thing with your help too.

 

[vbaInet]

So why have you got just Sep-10 to Nov-10? Does it mean you want to display the current month and two months prior to that?

The average of y/n for each site can be calculated in the report using AVG().

 

[SammiH]

Hi,
I will pull in data from April to current month for monthly reports and there is also a quarterly report that I will need to set up in a similar way, so that will use data from 1st Q, 2nd Q and so on. The caretaking report so far only has sample data in it for the 3 months. The bit I'm confused about is how I can show both the % score and the y/n data in one report for each month / block.
Thanks for your help so far.