Report that shows aggregated data

[cr-dev]

I have a very complicated report that I must design for our company "Satisfaction Survey" system and not sure the best way, programmatically and design type. For you report guru's this should be a reasonable challenge as for me, it is rather difficult. I'm looking for a way to get this done and quickly.

I have a database that contains 26 questions. 23 of them use 6 radio buttons (1 thru 5 being from excellent to poor and 6th button is NA) and 3 questions are Yes/No responses. Database is setup into 5 various tables linked together by a survey number. 4 of the areas (which point to 4 seperate tables): Scheduling, Education, Accommodations and Customer Service. (straight forward relational setup).

The report I have to design contains the following criteria.

* - Searching by a date range (start date...end date. I am familiar with that function)

* - for each "radio button" question marked 1-5, a percentage to reflect each option (Excellent, very good, good, below average, poor) Total number of surveys who answered excellent out of the date range total divide by 100.

* - for the percentage range that scored either a 4 or a 5, it will show who 4 fields of information (Name, Location, Study Date, and Technician).

That should get me started. If you have any ideas as to how I should start this, please advise. I was thinking it would be better to create a seperate database file and query the "master" database just to keep the DB processing lower but how much harder is that to do for this type of report?

Thanks all.

Chris

[comment]

I cannot follow your description very well. Do you mean you have 26 fields in the same record, each field being a response to a different question? And each record is ... what exactly? And all this is repeated across 4 tables?

[cr-dev]

I cannot follow your description very well. Do you mean you have 26 fields in the same record, each field being a response to a different question? [color:red]Correct. Each Button has a custom value of 1,2,3,4,5,NA. Yes/No's use a Yes,No Custom Value.

And each record is ... what exactly? And all this is repeated across 4 tables?

[color:red]Each record, to be specific, is a Satisfaction Survey that a patient would fill out rating us on how we perform in the 4 primary areas: Scheduling, Education, Accommodations, and Customer Service. On the Scheduling Section Question 1, "Customer Service was prompt to contact to schedule my appointment?" They would rate a 1,2,3,4,5 or NA (if required). The "Survey No" field is an auto populated field that is linked across the 4 Tables. I have attached the base structure of the file if you want to look at it with an example record. Very simple application and is quite effective for us. I can get the data for this report manually and put into Excel but It takes literally about 3 hours to do it so I know it can be done programatically, I just don't know the best way to do it. BTW, our database has around 11900 records and amazingly enough, I can query the data pretty fast for this type of info. More comparable in speed to a high end SQL box.

PSS.zip

[comment]

I'm afraid calling this "straight forward relational setup" might be a little too optimistic. It will be very difficult, if not impossible, to get the report you want using your current data model.

I suggest you study the following threads:

http://www.fmforums.com/forum/showtopic.php?tid/180113/

http://www.fmforums.com/forum/showtopic.php?tid/183639/

Once you restructure your data properly, producing the report will be easy.

[cr-dev]

Impossible? How can that be when I can query the system for each question and get the result? It is a manual process the way I do it of course, but it can be done. I could see your point if the system brought back incorrect results with each query but I haven't had that issue...yet. The other day I did a report on this type of info for EACH QUESTION (talk about tired fingers)and was able to get the data.

I was thinking that if I could script to find the result, and save the result in a global field, once all of the results totaled were collected, just have the report perform the calcs required to present the data appropriately. It would and could work that way without having to warehouse all of the data in one table. Man, Talk about query hell. It would be as slow as Christmas with getting the data as more and more records were added if all were in one table. It would have to query 153 different Fields for the info, on 11900 records!? It was amazing I found for one query on one question, it took only a few seconds. Doing each question, to query this data, I predict will take around 1 minute to complete to get the report in that fashion. Might be quicker tho, we'll see.

I appreciate your input Comment. Look forward to your future responses on this. :

My good friend -Queue- is a MASTER at the impossible. I will have to get my spell from the necronamican to summon him.

ARISE....-QUEUE- :content:

[comment]

Well, anything you can do manually, can be scripted. But it's an awfully clunky process, compared to a simple report with sub-summaries. Don't knock it until you have tried it.

[LaRetta]

The other day I did a report on this type of info for EACH QUESTION (talk about tired fingers)and was able to get the data.

Exactly. And you will continue to have problems getting the data. As Comment points out, reporting is the way to go (along with proper structure). I'd do a dance too if I thought it'd bring -Queue- back! Oh yes indeed! But I believe that even he would recommend the same thing as we are telling you in this instance. Regardless, let's try ...

-QUEUE-!! Show yourself and share your wisdom on this one!! Or just tell us about booleans and talk mathematics. We'd love it. We all miss you! :yep:

[cr-dev]

I wouldn't say "problems" but rather "a hard time". I have been working with a seperate DB file querying the master database and so far, it can retrieve the info required for this report. a little tough to setup, but not too bad. Infact, DB performance from the Master is very good with awesome response times. While I do agree with you and Comment about having the data stored under one table and it would make reporting on the data easier, I wanted better DB performance for larger record containment. 5 tables was the way to achieve this.

[mr_vodka]

Topic has been spilt.

http://fmforums.com/forum/showtopic.php?tid/194113