I have a large database with 3 fields (procedure1, procedure2, and procedure3) which may contain many responses from value lists. each of the 3 fields may have any of the value list responses.

I would like to have a summary page where the frequency of each response is given. I have worked out a way of doing this by running a script with a loop counting through all records and checking the 3 fields for their contents. This has to be done for each possible answer from the value list, then putting each on the page. This works but it will be a lot of work to do it for each and may be slow when the database has a lot of records.

Is there a simpler way of doing this? Would it be easier if things were stored in a relational way?

I would like to fix this now before actually using it. I'm sure it will be more work to come back and reorganise later.

There's proabbly a lot of ways to do this. One way to to create a summary field that counts the records.

Then, create a layout with a sub-summary field when sorted by... Procedure 1, another by Procedure 2, and so on..

- OR -

You coudl create a global field, called Procedure. Create another calc field, PrceduresCombo.c that is defined as:

Procedure1 & "P" & Procedure2 & "P" & ... so on

Then create a realtionship between the global field and the PrrocedureCombo calc.

Create another summary field that returns count of records.

In a layout format the Global field with a drop down of the available choices, then add a field from the realtionship you made earlier to show the summary field.

A user can select the "result" they want to see the summary of and it ought to show in the realted summary field.

I think this will work..?

Thanks for the reply but I'm afraid I don't understand. I think I probably didn't explain how I had written the DB very well in the first place. It might be easier to put it up on the forum than explaining it.

A summary field of Procedure1 just gives the count of the number of procedures, not the number of each different type of procedure. If I knew how to get a summary field to do that I could then make a summary page. I just don't know how.

When you mention a relationship in your reply I assume that you think a relational DB is the best way.

If you think I should I could post it.

Thanks again

OK - I just created and example and discovered that you don't need a summary field (I was wrong) , you need a calculation:

=Count(Self#procedureMatch.g#procedureCombo.c::zc.recordSerial)

That is... a self relationship between the Global field and the Combo field that counts something in the records, I chose the serial number.

Now, when you specify a procedure to summarize in the global field, the Count field will tell you how many procedures of that kind you have in any of the three fields.

See the attached...

Procedure Example.zip