Summing Booleans for a report

[sliptdisk]

Well, gang, here we go, again. I've inherited another poorly designed database, from which I am expected to publish reports. This database is based on a number of surveys, which, for the most part, are Yes/No questions. Unfortunately, the data from these surveys has been entered into a seperate database for each of the 5 surveys, all in one table, each. Using the report wizard only produced a "too many fields" message, so, I broke the data (or, at least the fields for the data), into smaller, more manageable tables. What I need to do, now, is either use the data from the original databases, or have the data reentered into the one I designed, and, publish reports for the surveys, indicating the percentage of respondents who answered Yes or No to each question. So, I need to sum the data for each of the fields, first, then have bound controls (text boxes, I'm thinking), set up to handle the summed and percentaged data. Incidentally, I tried to use the field list control, but, it's greyed out, for some reason. It doesn't seem like this should be all that difficult to solve, but, I'm doing this for a charity organization, and, considering that the person who first volunteered for this project put minimal effort into it, I'd like to do it right, so that they won't have to pay somebody else to come in and fix my mistakes. Thanks.

 

[Amiel]

I do appreciate the attitude on this one. The SQL folks would say that it should be done using SQL. The code oriented people would say, use code to extract the totals.

I say your answer might look something like the following,
For each table create a query like this:

"SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES]"

... this will provide a count of the yes and no answers, (just add, "Where [Question#] = 1", if there is are more than one question housed in the table(s)).

The difference between Count(AnsweredYES + AnsweredNO) and Respondents is the count from the 'no response' group unless you add;

...this, "... Where [YesNoField1] is not null and ...". To eliminate non-answers from the count.

I think this should do it. More? let me know.

 

[sliptdisk]

That looks like an excellent solution! Thanks. What I'm working with are five seperate databases, each based on a different survey. The guy who designed the db did it for free and just slapped together one giant table for each, with 150+ fields, most of which are boolean, with no more than 130 entries each (get the picture? this wasn't broken down into anything very manageable). I started a new db, and, at least broke the tables down into pieces, simply because the report wouldn't generate with so many fields. What I'm assuming is that that code you broke off for me should go into the Form_Open event of the report, no?

The last thing I need to do for this project is take a large section of each survey that is dedicated to rating preferences for various activities, of which there are many. Unfortunately, they were rated with 1 representing most favorable, and 5 being the least. I have to represent, in percentages, what the preferences are. I think that I can accomplish that by declaring variables to associate with the ratings, and, transpose that into a value that can be plugged into a text box. Similiarly, I need to group the zip codes of the respondents into percentages for the report, as well. Now that I think of it, all of this stuff would be going in form_load, right?

 

[Amiel]

No, not in the <>_Open event, I dont think so here.
Let me just take a stab at what you've included her.

First of all. The approach I'd use is to create the QueryDef for each of the five Surveys, which will become the data source for the report. This is the code that will be used to create the queries themselves, Place this in a Code Module and use, &quot;Call CreateDataSources&quot;, prior to Creating the report.


Public Sub CreateDataSources()
Dim db as database
Dim qd as QueryDef
set db = DBENGINE(0)(0)

On Error Resume Next
db.QueryDefs.Delete &quot;Query1&quot;
db.QueryDefs.Delete &quot;Query2&quot;
db.QueryDefs.Delete &quot;Query3&quot;
db.QueryDefs.Delete &quot;Query4&quot;
db.QueryDefs.Delete &quot;Query5&quot;
On Error Goto 0

set qd = db.CreateQueryDef(&quot;Query1&quot;, &quot;SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey1]&quot

set qd = db.CreateQueryDef(&quot;Query2&quot;, &quot;SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey2]&quot

set qd = db.CreateQueryDef(&quot;Query3&quot;, &quot;SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey3]&quot

set qd = db.CreateQueryDef(&quot;Query4&quot;, &quot;SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey4]&quot

set qd = db.CreateQueryDef(&quot;Query5&quot;, &quot;SELECT Count(*) as [Respondents],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey5]&quot

End Sub

.. remember, approach is the key. This code allows you to keep all the the development problems in a single function call. From here you can modify the Five (5) queries to suit your needs. Then update the report layout, if necessary, after, you have data the way that you want it.

If each survey needs to be broken down by zip code then add
a, &quot;... Group By [Zipcode] Order By [Zipcode]&quot;, clause to the SQL statement(s).

as in..., &quot;SELECT Count([ZipCode]) as [Respondants],Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey1] ORDER BY [ZipCode] Group By [ZipCode]&quot;

.. or
&quot;SELECT Count([ZipCode]) as [Respondants],First([ZipCode]) as [ZipCode], Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey1] ORDER BY [ZipCode] Group By [ZipCode]&quot; [/b]

I think this is correct. Use the Function above, replace the necessary table names and field names in teh queries and execute the function call, then afterword execute the queries, and check the results. You'll end up playing with the queries themselves to get the data right. But this approach should make it easier to get the job done.

Let me know.

 

[Amiel]

... that's, &quot;Group By, Order By&quot;, and not, &quot;Order By ,Group By&quot;

... also add, &quot; WHERE [ZipCode] is not null&quot;, to your query.

 

[sliptdisk]

Great. I'll give it a shot, first thing in the morning, and, I'll get back to you on my progress. You've been a big help.

Dan

 

[sliptdisk]

Well, apparently, I have alot to learn about Access. I opened up a code module from the db window, and, did a cut and paste of the zip code code you gave me. Insofar as the queries are concerned, I was opening up new ones in design view, and creating one for each table, as per your instructions. I tried to insert the code, above, into the criteria box, but, it was telling me I had a syntax error. Do I need to include the quotation marks, or am I doing it in the wrong plce, altogether?

Furthermore, do I need to create criteria for each field? As I mentioned, there are 150 questions, with Boolean fields for most of them. I've broken the big tables into smaller ones, in hopes of making them more manageable, but, there is no data in them, yet. Would it be easier just to stick with the one table, or should I attempt to import the data into these 10 or so smaller ones?

 

[sliptdisk]

Do I need to create a variable in the code module to divide the total # of yes responses by the total # of entries?

 

[Amiel]

I've been away for a few days and could not answer your e-mail in a timely way.

The code above will be used to create the queries for the report, not the query window. You said, &quot;...I was opening up new ones in design view, and creating one for each table, as per your instructions...&quot;. Absolutely not, the point to the above code is that since each table will be being treated more or less the same, there is no need to manually edit each of the five queries seperately. So;

The code that I offered will be used to create the final queries that that will be used as the data sources for the report. The objective is to get the query statement correct in the code, then execute the code to create the five (5) queries. As you add or remove fields, you will only need to edit the lines of code in the (single) function rather than hastling with each of five different queries in the query design window. In fact you can eliminate all but query1 from the code, and once that query is corrected, cut and paste that same code to create each of the other four (4) queries.

So ....

What was the specific error returned when using the following? being careful to check the spelling and to place field names in brackets.

&quot;SELECT Count([ZipCode]) as [Respondents], Sum(Abs([YesNoField] + 1)) as [AnsweredNO], Sum(Abs([YesNoField1])) as [AnsweredYES] From [tableSurvey1] ORDER BY [ZipCode] Group By [ZipCode]&quot;

 

[sliptdisk]

Well, I must be very confused as to where the select statement is supposed to go, and, this thing is getting out of hand. I've taken each of the 5 databases (i.e., each of the 5 tables, as each database consists of one, giant table), and created queries with the wizard, just to break the tables down into more manageable pieces. Did you mean that the code I placed in the module should be called from another module? I guess that's what I'm confused about - exactly what you mean by &quot;use&quot; the call statement? Use it where?

 

[Amiel]

The code that I offered is used to create the query definition (which will show up in the list of queries in the Access window after the code is run), this is suggested as an approach to avoid having to edit each of the five seperate queries manually. I am assuming here the field names are the same, and the table names are different. So that once 'Query Number 1' is complete, you can use the same statement used in Query Number 1 in Query Number 2 by simply changing the name of the table for 'Query Number 1'. If this is not the case then the suggestion goes to file 13.

Again, using a single function call to create the queries was just an approach to minimize the time spent and reduce the complexity of the job.

Secondly, and unrelated to the code in the suggested approach to solving this problem, is the SQL statement itself.

I also am assuming :~/ that you have created a single database containing all of the tables, since each table contains, &quot;... no more than 130 entries each&quot;, I figure the first thing to do is to 'drag and drop' these into a new database.

The origional question had to do with summing Booleans. I'm sure that that example statements above will work. Test them using just a couple of fields from one your tables.
Once done add the rest of the fields in the table.

To edit the SQL statement you'll have to go to 'SQL view'. If you are not comfortable working like this and you still need help send me an E-mail ...
[sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]