Multiple Pass Reorts 1

[ekim]

I have a need to create a report that list the data from one field in all records, then all data from all records for the next field and so on. The table is a capture of responses to a comment/questionnaire with each record comprising all responses for a given respondent.

I have basically accomplished what I need using sub reports with individual queries for each sub report but this is clunky and inelegant especially if I want to expand it to a large number of responses. The inital one uses 4 responses. I am about to format one with 25 responses. Also I want to generalize the approach to create the output on the fly indepentdent of the number or type of promts or responses.

(I have a separate table for prompts in which I also identify the type of response required -- multiple choice or narative comment, etc.)

Originally I had the responses as details to each sheet but that broke down when I had to tie the responses back to the physical site and building where the comment sheet was filled out so I went for a "flatter" structure for now.

In my old dBase days I would have hand-coded a report with multiple passes through the data but I have yet to find a simple way to do that here. The posts for Ymesei and Mrs Chicken seemed to come close but the aha escapes me. Any help would be appreciated

 

[MichaelRed]

I think (cogito?) therefore (ergo?).

Up to a point, you can probably do this with a crosstab query. I am not sure of the limit on the number of "rowheadings' imposed by Ms. Access, but you can do at least ten. The 'Query Wizzard" will not do it completly, but you can get it to give you the overall framework, and modify it to add 'rowheder' items (make sure you set their corsstab to "Group By&quot and delete the totals column. You will get some 'junque' columns as well fro the column heading, but these may easily be ignored). After this, loook at the SQL. You will see that you have created a Transfom/Pivot query. It may be possible to modify it further and come even closer to your desired layout.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ekim]

Thanks. I had tried crosstab earlier but got some pretty unmanageable and unuseable results. It did not seem applicable perhaps due to my misunderstanding of how they work. I had had the impression that
a) they were only good for numerical data and I am working with text; and

b) if I have a table of x records with fields a, b, and, c , for example, I thought that a crosstab would be useful for "Give me all the records, x where a is true or b is greater than 25, etc. What I want is all the nz(textvalues) for a then all the nz(textvalues) for b ,etc.

I'm not really crosstabulating against anything per se (except perhaps non-null values) am I ? I'm just listing fields?

My table is basically SheetID, Response1, Response2, Response3, Response4, ...ResponseN.

Forgive my ignorance here. Any help is appreciated

 

[MichaelRed]

Go ahead and generate a crosstab query. Use your SheetId as the column header and three of hte response fields as row headers. Let the value be just the count of the SheetId. Run it and look at the results (all rowheader fields and the columnheader field should have their crosstab row in the query grid set to group by(. You should be able to 'see' the info you want in the Response1; Response2; Response3 ... rows. Modify the query to have as many of these as it will accept (you need to do this in DESIGN view of the query). It is NOT the 'perfect' way, but it should be o.k. for the limited number of coulmns to rows .


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ekim]

Thanks -- I'll give it a shot later this evening and let you know how it turned out.

I'd still like to create a more generic soulution, though, since I will very soon be dealing with surveys of 20+ responses and I'd like to liberate the user from needing me every time the survey changes. Since I have the prompts in one table and the responses in another and forms for maintenance and data entry for both, I may just have to buckle down and code a "wizard" and some globals to set it up.

The only thing I've come up with so far(conceptually) is to create a temporary table and loop through and append records by field then use that as the data source for the report. If that sounds reasonable and you have any thoughts on pitfalls to avoid or efficiencies to build in, I'd appreciate it.

Oh well!

Thanks again

 

[MichaelRed]

the loop/append approach may be the better soloution, however even this could be much easier if done as a (virtual) set of queries. Just have a generic 'query' where you replace the .SQL property with the necessary text. In general, all you need is a "list" of tables & Fields which you need to append from. These could just be more tables, so you could have a form set up for the user to add new tables. I would "assune" that you can control the field names - so your list of tables may be sufficient. Just the table name - then all fields Like "XX?" - which you can find programmatically.

In the long term you may want to re-think the design, so that this process is more automatic. One thought would be to have ALL of the tables have the SAME structure, something like:

SheetId | RespId | Resp

A-la 'modern' design, go deep as opposed to wide. Would simplify the current issue - but f course like any good bliviet, some stuff will pop out elsewhere - and need other soloutions.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ekim]

Yes, thanks again. Your comments are on target.

As your byline says, there is never enough time...........

My original design was, as you suggest, deep vs wide but something blew up that I couldn't find right away and under the pressure of time I opted for the flatter structure of my present version. I will go back to the deeper structure when I have the time since it will be easier to get what I want out of it. Of course, now it has become a challenge to make this one behave. I'll keep you posted.