Complex Summary Report

[DeanWilliams]

Hi,

I have to create a summary report based on fields from one main table. The table has loads of fields that I want to do counts on, each with 3 or 4 possible options.

So, what I want is a report with each fieldname as a row heading with the possible values as column headers and the counts over the recordset in the appropriate column. Some fields share the same set of possible values, but not all. So I can't have the same heading along the top for each field.

The only way I can think of so far is to create a crosstab for each field as the column heading, with the counts of the ID field as the value. This will take ages though, and I'm sure there must be an easier way.

Hope this makes sense.

Can anyone help??

Dean

 

[dhookom]

Can you give us an idea of your current table structure with table and field names and sample data? Then also provide a sample of the desired output.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TheStriker]

I have sort of the same situation going on where I want a massive summary report. If you could give us an idea of what you are trying to accomplish along with what data you are working with, maybe we can be of some assistance.

 

[DeanWilliams]

Hi. Thanks for your replies.

I am writing a report on one main table: It holds details about assets and the fields I want to summarize are all fields that rate the assets. So, things like:

Condition - Possible options: Grade A, Grade B, Grade C

Location - Possible options: Grade A, Grade B, Grade C

Market Significance - Possible options: Local, Regional, National

There are lots of these sorts of fields which categorise each asset by providing ratings.

These assets are split into 3 different types and there is a different QBF search form for each type (Industrial, Office, Development).

The report will be based on a set of these assets that the users have chosen from the QBF form. The report needs to have no raw data in it - just counts of the numbers of assets that come under each category. For example:
-------------------------------------------------------------
Grade A Grade B Grade C
Condtion 12 4 7
Location 9 7 7

Local Regional National
Mkt Signif. 15 7 1
-------------------------------------------------------------

Because there are so many fields and they have different values I can't see an easy way of doing it. I have started doing this now, but the way I mentioned above. I am dynamically creating Crosstab QueryDefs for each field and will copy the data to the report. This seems like a crude solution and depending on the number of assets returned by the search, creating the report may take ages.

Here is the code I am using to create the QueryDefs:

[COLOR=blue]
Dim tdf As TableDef
Dim qdf As QueryDef
Dim fldTdf As Field, fldQry As Field
Dim intFldLoop As Integer

For intFldLoop = 13 To tdf.Fields.Count - 1
    Set fldTdf = tdf.Fields(intFldLoop)
    Set qdf = dbs.CreateQueryDef("", 
                 "TRANSFORM Count(tblTemp.Ref) AS CountOfRef" _
                 & " SELECT tblTemp.PropType FROM tblTemp" _
                 & " WHERE tblTemp.PropType = '" & strType & "'" _
                 & " GROUP BY tblTemp.PropType" _
                 & " PIVOT [tblTemp." & fldTdf.Name & "]")
                                        
    PrintRecordset qdf.OpenRecordset
Next
[/color]

This creates a crosstab by plotting the type against each field, which would look like:
-----------------------------------------------
PropType Grade A Grade B Grade C
Industrial 16 144 175
-----------------------------------------------

I am then copying these fields to the report, except for PropType. Let me know if there is a more efficient way than what I am doing.

Thanks,

Dean

 

[dhookom]

Are your options as consistent and limited as your posting suggests? If so, I would normalize your many fields using a union query and then create a crosstab based on the union query.

Select FieldA, FieldB, Condition, "Condition" as Category
FROM tblA
UNION
Select FieldA, FieldB, Location, "Location"
FROM tblA
UNION
Select FieldA, FieldB, CurbAppeal, "CurbAppeal"
FROM tblA
UNION
etc.
This allows you to create a standard crosstab from the result of the union query.
You may need to create a couple of these due to several types of categories. Each could be placed in its own report or subreport.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[DeanWilliams]

Hi,

Thanks, that is something to think about.

There are about 45 fields and 3 subsets share the same options (with 12, 4 & 3 fields). This leaves 26 fields which each have thir own specific set of options, not shared with any other.

The trouble is the table is not normalised and all 3 types of properties are in one table (Industrial, Office, Development). Some fields are not relevant to all types. So I need to make it dynamic so I can have the right fields showing up depending on which search form, they are using.

Doh.

 

[dhookom]

The issues you are describing are the reasons I attempt to normalize, normalize, normalize. There are a couple demos at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Check out the Employee Evaluation and At Your Survey. These are loosely similar to your application but are much more normalized.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[DeanWilliams]

I know. I just took over the project and the report is an immediate requirement so I have no time to restructure the database.

Thanks a lot for the info. Will try it out.