I just finished a survey that was a combination of Yes/No and multiple choice questions. The data was not normalized, so I could not take advantage of crosstab queries and such.

You'll need 3 queries, a Select query to determine the results of each individual's responses, and 2 Totals queries to sum it all up.

If your questions are all True/False, you'll need to create at least 3
formula's for each question:
1 to test if answer is True,
1 to test if False, and
1 to test if Null(no answer.)
The IIF function covers this nicely:
IIF([Q1]=True,1,0) will test Q1 for True and return a 1 if it is so.
(if your data is stored as text, enclose the word True with "".)

You should name your fields so that you can identify them easily in the subsequent Totals queries. The method I chose was Q#T, Q#F and Q#Null. This is done by writing the above formula thus: Q1T:IIF([Q1]=True,1,0) in the Field section of your QBE grid.

Next field would be IIF([Q1]=False,1,0) and finally IIF(IsNull([Q1]),1,0) returns a 1 if the field is Null. do this for each question, save and close the query.

The first Totals query is based on the Select query you created. Select each field (Q1T,Q1F,Q1Null...etc.) and then choose Sum for the Total row in the QBE grid. You can also explicitly name each field here, otherwise Access assigns wonderful names like "SumOfQ1T". At this point, you'll have the sum of total responses for each question. Close and save the query.

In the second Totals query you then need to create fields to return whatever your desired end results are. For example, if you want to know what percent answered True to Q1, the formula would be -
Q1Total:[SumOfQ1T]/([SumOfQ1T]+[SumOfQ1F]+[SumOfQ1Null].) Repeat this for each possible response, substituting the appropriate fieldname at the beginning of the equation.

You could then create a report based on the second Totals query to present the results of the survey. Tip: since the formulas are basically repetitive in nature, you can use copy and paste techniques to reduce the typing required.

If you know SQL or stumble across the Find Duplicates Query Wizard, the solution may be obvious. This Wizard actually uses different methods for different needs. The discussion here covers only one method, and works with version 1 (which doesn't have the Wizard.)

1. Create a new query, and add the table you are working on.
2. Click the Totals button on the toolbar (upper case Sigma.) A "Total" row will be added to the grid.
3. Drag your field into the grid, and in the Total row, select Group By
4. Drag the same field into the grid a second time. For this second instance, select Count for the Total row, and enter ">1" in the Criteria row.
5. Run the query, to view the contents of offending records, and the number of duplicates you need to locate.

You may be interested to see what Access generates behind the scenes. If the table is called "Customers" and the field is "CustomerID", the SQL string will be something like:

• SELECT DISTINCTROW CustomerID, Count(CustomerID) AS CountOfCustomerID FROM Customers GROUP BY CustomerID HAVING (((Count(CustomerID))>1));

You wish to mail two different messages - one to your WA friends and another to those in other states. You create two queries: one where the Criteria line under State reads WA , and a second query where the Criteria line reads Not "WA" . You could think this would take care of all cases. It doesn't!

The problem is the way Access and other true relational databases handle Nulls. If the entry under State has been left completely blank, the record will not show up in either of the above queries. You must specifically ask Access to check for Nulls, by using a Criteria such as Is Null Or Not "WA" . Whenever you enter criteria for a query, think about the possibilities of Nulls.

In some cases, you will want to prevent Nulls occurring. In Access 2 or 7, this is easy: open the table in Design View, click on the field, and in the properties at the bottom of the screen, set Required to "Yes". In Access 1, this was not possible, but you could prevent it with a macro assigned to the BeforeUpdate property of a form (not the control!.) For example, to ensure no record can be added if the Surname is blank:

1. Create a new macro. Under Condition enter: [Surname] Is Null . (If you see no Condition column, check the View menu.)
   
2. Under Action choose CancelEvent . This will do the job, but it would be polite to display a message explaining what's wrong.
   
3. On the second line under Condition type three dots: Access interprets this as a ditto. Beside this under Action, choose MsgBox , and fill in a suitable message in the bottom of the window.
   
4. Save the macro with a name such as NoBlankSurname
   
5. Open the form in design mode. If the Properties window is not visible, click the toolbar icon with a finger pointing to a form. The title of the Properties window should read "Form"; if it doesn't, click the little white box immediately below the form's control box (left end of title bar.)
   
6. Locate BeforeUpdate in the list of properties, and select the NoBlankSurname macro.

Nulls in Joins

The same "Gotcha!" occurs when you have two tables joined on a field that can contain a Null. For example, a Customer table might be joined to an Invoice table on a CustomerID field. To cover "Cash Sales", you allow invoices to be entered with a blank CustomerID

Now you create a query with both the Customer and Invoice tables, as the basis for a report showing all sales. If you are observant, you notice that your Cash Sales are missing, so the report has incorrect totals!

The solution is very simple. In the Query Design grid, double-click the line that joins the two queries, and Access will pop up a dialog box asking what type of JOIN you want. Select the appropriate OUTER JOIN, and all the "Cash Sales" will reappear in your query and in the report.

Access has just exposed one of it's limitations, it's 16 bit and therefore stores data into 64K segments, you've just filled up one of those segments. Consider making your query a Make-table query or an append query to a temp table then base the report on the table, this will split the processing up a bit and as the query and report are no longer one operation, Access will no longer try to store both into the same 64K segment.

1. For a sample we will create a table named t_address. This table will have Six fields: FirstName, LastName, Address, City, State and Zip.

2. Now put some sample data into the table. Make sure you put in at least 10 names and duplicate at least 2 firstname, lastname etc. --- like Joe brown then Joe Green then Tom Green and so on with City, State and Zip also.

3. Now create a parameter query based on five of these fields. The Five fields we will use as Parameter based will be [First Name] [Last Name] [City] [State] [Zip] the address criteria field will be left blank. Name the query q_r_address.

4. Now create a report based on this query. Name the report r_address -- you could make this a mailing labels report.

5. When you run this report you will be prompted to enter First Name, Last Name City, State and Zip. Unless you enter an appropriate value for each parameter you will get a blank report.

6. Now go back into your query and change the criteria fields as follows: Change [First Name] to IIf(IsNull([Query]![q_r_address]![FirstName]),[t_address].[FirstName],
   [Query]![q_r_address]![FirstName].) Do the same thing for the other applicable fields.

The simplicity of this approach stems from the fact that access will look at what you enter into the parameter query and if you don't enter anything it will go to the t_address table and return all values that are entered. here is where you need to be careful. If there isn't anything entered in a field then that row will be left out. So you should go through and make sure that nulls are accepted and that "" is the default value for all fields you think may be left blank from time to time. Or just require each field to be filled.

This little tip can generate literally thousands of reports based on multiple selection or non-selection of parameters based on the parameter query. Remember earlier I said to include duplicates. Now run the report and enter values for some of the parameter requests and leave some blank. Notice how those fields that are left blank return all matches to those you fill in.

You can now get real fancy and develop a form (to drive the report) which prompts the enduser for values that are based on listboxes pulling values from the underlying table -- but that is another tip.