Count in Reports Detail section

[kokiri]

Hi all,

I have created queries (50+) to capture below information and these information is in one single table. Only thing different is column name. I used query option, because I have not figured it out different way.

SELECT Sum(Abs([RN1]="Yes")) AS Yes, Round(Sum(Abs([RN1]="Yes"))/(Sum(Abs([RN1]="Yes"))+Sum(Abs([RN1]="No")))*100,2) AS Yes_P, Sum(Abs([RN1]="No")) AS [No], Round(Sum(Abs([RN1]="No"))/(Sum(Abs([RN1]="Yes"))+Sum(Abs([RN1]="No")))*100,2) AS No_P, Sum(Abs([RN1]="N/A")) AS [N/A], Round(Sum(Abs([RN1]="N/A"))/(Sum(Abs([RN1]="Yes"))+Sum(Abs([RN1]="No"))+Sum(Abs([RN1]="N/A")))*100,2) AS [N/A_P]
FROM TempMedRecRvwData;

What I would like to do is put all 50+ queries to one single reports (if not 2 reports) in Detail section of Reports. I tried subreports but I don't like the way it appears in my reports.

I wonder what other options are available. I tried text box controls option but I wasn't able to figure it out in Detail section of reports.

What I like see in my reports is:

Question# RN1: Yes, Yes%, No, No%, N/A, N/A%
11, 31.43, 24, 69, 2, 5.4
Question# RN2: Yes, Yes%, No, No%, N/A, N/A%
5, 50, 5, 50, 1, 9.09
.....

Thank you for your help in advance.

Kokiri

 

[Remou]

I wonder what your table looks like?

 

[dhookom]

Having 50+ queries is the penalty for un-normalized tables. Is it too late to change your table structure?

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]

 

[kokiri]

Hi Remou & dhookom,

Currently, this table contains all answers and it looks like below and ID is primary key along with doc_date:

ID,doc_date,RN1,RN2,RN3,RN4...RN50+
11, date,Yes, No, N/A,Yes...
13, date,No, Yes, , Yes...

Thanks

 

[dhookom]

Again, is it too late to change your table structure? A better, more flexible structure is demonstrated in At Your Survey

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]

 

[kokiri]

Hi dhookoom,

No, It is not too late to change my table structure, and I'll be more than happy to change it if I need to. What do you recommend?

But I don't understand why I need to change it since this table collects only answers.

I briefly looked at your sample demo and it is very sophisticated and complicated. I also noticed that you are collecting data in rows rather than columns. There must be a reason... Is this for better performance or some other reason? I'm trying to understand your reason behind.

I really like your statistic and graphic statistic reports. That is what I'm truly looking for.

FYI, basic demographic data and some answers are imported from txt format and majority answers will be collected in Access.

Thanks.

 

[dhookom]

There is a good reason for the At Your Survey table structure. It takes all your repeating controls, fields, functions, and expressions and "un-repeats" them.

Consider the amount of work it would take to create the graphic and statistic reports with your current structure? Also, what happens with your current structure when you want to add a new question? How about creating a new survey?

It may be possible to take your un-normalized table and normalize it using a union query.

SELECT ID,doc_date,RN1 as RN, 1 as Question
FROM tblRawData
UNION ALL
SELECT ID,doc_date, RN2, 2
FROM tblRawData
UNION ALL
SELECT ID,doc_date, RN3, 3
FROM tblRawData
-- etc --
UNION ALL
SELECT ID,doc_date, RN50, 50
FROM tblRawData;

This might prove to be too complex for Access/Jet to handle.

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]

 

[kokiri]

Yes, I know. I didn't like the fact that I have to repeat the same steps to create same results for all different questions.

What do you recommend? I guess I need to restart my process. Do you have any steps or process so that I can accomplish what I'm looking for?

Thanks.

 

[dhookom]

If you have records in your table, start by creating the union query I suggested and use it to make and/or append your existing records to a normalized structure. You could also write code to do this.

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]

 

[kokiri]

Yes, I have records in my table. What is next step after you do union query all these answers?

Thanks.

 

[kokiri]

FYI,

I just completed union query for all the questions like the way you recommended and received an err with "Query is too complicated".

It didn't allow me to save it as one query so I made it to two query. I guess there is limitation of union query.


Thanks.

 

[dhookom]

I had guessed you would receive that error message. Use one union query to make a table. Then use the other union query to append the second bunch of records.

This should end up with all your wider records inserted into your normalized table. You can then review the At Your Survey to see how statistic reports can be created.

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]

 

[kokiri]

Hi Duane,

I'm reviewing your At Your Survey Statistic Reports and noticed that you used qselQstnTextRspnsxCount. How can I review this source?

Thanks.

 

[dhookom]

You can display the database window and then find the query in the queries collection.

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]

 

[kokiri]

Thanks.

I wasn't thinking. I forgot to expand the query window and only saw 2 queries....

Thanks.

 

[dhookom]

1) group footer sections are set in the report's sorting and grouping dialog.
2) You can't put =Sum([AnyFieldName]) in Page footers.
3) see 2)

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]