Fill in values on a single page report from multi-row table 1

[klove73020]

I would like to fill in values onto a report based on the rows in a table. Each field on the report will have the value from a column in the table when the value of another column equals a predetermined value.

For example, the table has two columns:

question_id
question_count

The report has 157 fields and is always four pages long with predetermined locations for each report field. When the record with question_id equal to 'Q1a' is encountered, the value of question_count is placed into a particular field on the report.

I have no idea how to create this report in Access. If I was writing code to produce a text file, this would be easy.

What I'm looking for is a basic outline of how to go about creating this report.

As a side note, we had this report working when we had about 130 columns that were named 'Q1a', 'Q2a_e', 'Q11a', etc. There was 1 record in the report. While updating the columns in the table to add the new fields, we hit some sort of Access limitation on number of columns. With the new columns added, the table design refused to save.

Any help is appreciated.

 

[dhookom]

Your main issue might be one of normalization. The limit of 255 fields can be breached if you add and then delete fields without compacting. However, you might want to look at a properly normalized survey "At Your Survey" found at

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

Beyond that, I'm not sure what you want your report to look like.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klove73020]

Thank you for your quick response.

However, I don't believe I did a very good job of describing the desired report. The fact that this report is based on a survey is mostly irrelevant. The problem is more general in that I have multiple records with values that need to be placed in certain locations in a fixed size report.

Below, I've included a mockup of some sample data and what I'm looking for in the report. NOTE: I wanted a monospaced font similar to the 'pre' tag in HTML. Is there a better way in TGML than using the 'code' tag?

SampleData (1 record per row, 2 columns)
-----------------------------
question_id    question_count    
-----------------------------
Q1a                 2001
Q1b                   53
Q1c                    2
Q1d                   92
Q1e                 7911
Q2d_z                 42
Q2d_a                101
Q2d_b                 99
Q2e_z                942
Q2e_a                191
Q2e_b                999
Q2f_z                 86
Q2f_a                202
Q2f_b                  1
.... lots more rows here ....


Sample Report (Does NOT repeat)
-------------------------------

1. Sample question 1
   a. answer a                       <<Q1a>>
   b. answer b                       <<Q1b>>
   c. answer c                       <<Q1c>>
   d. answer d                       <<Q1d>>
   e. answer e                       <<Q1e>>

2. Sample question 2
                         Never    Sometimes    Always
   .... more answers here ....
   d. answer d         <<Q2d_z>>  <<Q2d_a>>   <<Q2d_b>>
   e. answer e         <<Q2e_z>>  <<Q2e_a>>   <<Q2e_b>>
   f. answer f         <<Q2f_z>>  <<Q2f_a>>   <<Q2f_b>>
   .... even more answers here ....

.... lots more questions here ....

The fields on the report are surrounded by '<< >>'. If the value of question_id matches text in the field, the value of question_count is placed in the field on the report. For example, when the record where question_id = 'Q2d_z' is encountered, the value '42' is placed in the field indicated by '<<Q2d_z>>'.

Basically, this report is a copy of the original survey with counts of each answer in place of the radio buttons, checkboxes, textboxes. I have the 157 counts in 157 records in a table in an Access database.

In previous surveys, the results were stored in 130 columns in a single record in a table in an Access database.

Is it possible to create this report in access? If it is possible, how do I create it?

 

[dhookom]

I'm still not exactly sure what you have for data and desired display. Do all the "z" correspond with Never and "a" with Sometimes?

Generically, you can create dynamic crosstab reports that would allow for differing headings for each question.

You can also use code to set the Left property of controls. This assumes you have something stored somewhere that set the Left property for each possible answer.

Another trick is to use the line of code:
Me.MoveLayout = False
to lay multiple report sections on top of each other.

The TT tgml should also create fixed space fonts in TT.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klove73020]

In my example, "Q2d_z" corresponds to all of the people that responded "Never" for "Sample question 2 - answer d". "Q2e_z" corresponds to all of the people that responded "Never" for "Sample question 2 - answer e". And so on. The actual survey has 7 columns and 8 "answers" (or 56 fields) on the report.

There are 13 questions with multiple parts totaling 154 fields (I did a recount .

 

[dhookom]

What confuses me is the difference between "answer" and "response". To me, an answer is a response. "Sample question 2" is the question and "answer d" should be the answer/response.

I guess I am having trouble understanding what the "7 columns and 8 answers" are :-(

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klove73020]

Just to let you know, based on the sentence "The limit of 255 fields can be breached if you add and then delete fields without compacting" I resolved the issue with saving the updated table design. I compacted/repaired the database and then added the new columns. The updated design was then able to be saved.

We are going back to the old way of doing things (since it worked).

On an academic note, I would still be interested in a way to base a fixed size and fixed field report on a multi-row table.

 

[klove73020]

Think of the "answer d", "answer e", etc. as "part d of question 2", "part d of question 2", etc. Each "part" requires a separate answer/response.

Here is the actual question #2 (w/ some slight reformatting to make it fit in the grid below):

[TT]
2. How many months have you been filing your business taxes using the QuickTax system? [select all that apply]

None 1-6 7-12 13-18 19-24 25-36 Over 36
a. Sales * * * * * * *
b. Withholding * * * * * * *
c. Tourism * * * * * * *
d. Use * * * * * * *
e. NR Royalty * * * * * * *
f. Waste Tire * * * * * * *
g. Mixed Bev. * * * * * * *
h. Est. Income * * * * * * *
[/TT]

Every "*" in that question is a radio button on a Web page with each row (e.g. the Sales line) grouped together. There are several types of questions (including check boxes, simple multiple choice, another one similar to the above, and two free form text boxes) on the survey. The taxpayer answers all of the survey questions and the responses are stored in a MySQL database table.

The results of taxpayers responses to the survey are then exported (via PHP) from the MySQL database to be imported into a local MS Access database (via a VB6.0 application). Finally, a series of queries are run and the response count for each field (e.g. filing Sales 1-6 months is a field) are placed in another table in the MS Access database (also via the VB6.0 application).

The aggregate counts of all taxpayer responses are what our management is interested in seeing. We have found that substituting the counts into the actual location of the response on the survey is a very good way to visualize the statistics. That is what the report is trying to do.

It was the second table that I was having trouble updating to handle the new questions (and their associated parts). Since that is resolved (with your help -- Thanks!), the results of the report is now working and we can finish creating the rest of the full report to our Deputy Administrator.

Does that make any more sense?

 

[dhookom]

There is a crosstab sample report solution that allows multiple dynamice column headings at

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]