Tally survey results

[jlazyr]

I have a survey of 42 questions. Possible responses are Always, Sometimes, Never. I am trying to make a talley sheet that would have a summary of how many respondents chose each of the responses for each question. Example, Always=42, Sometimes=15, Never=5 for each of the 42 questions. Would radio buttons or somethings else be better. What formulas should I use. [email protected]

[CobaltSky]

There are any number of ways to approach this.

One method that you might like to consider would be creating a separate file which has in it only three global text fields into which you enter the text 'always', 'sometimes' and 'never'. Then create a relationship which matches each of these fields in turn to all 42 of your questions in the main file (that's 126 relationships). Finally, you can create a three calculating fields for each question using formulae along the lines of: Count(Question7always::AnswerQ7) (where "Question7always" is the name of your first relationship for question 7 and "AnswerQ7" is the name of your answer field for question 7 in the main file).

This tally file will provide an up-to-the minute live read-out of the state of the data in the main file.

As regards, your other question, yes I'd recommend using radio buttons for entry and display of the responses on your main file, as they are easy to read at a glance, and support an 'either/or' data entry logic (ie selecting a new value over-rides the previous selection so only one answer is stored).

[lrcarey]

Hi,

I have a similar question. I already implemented the suggestion in the previous post and it worked out great. Now I want to create another report and I'm not sure if I need to add another facet to the formulas.

I am setting up a registration database where each person can register for each program once. Within that registration, they can register for multiple workshop choices. When they sign up in workshop time slot one for workshop session choice A, then the title of time slot one/choice A pops up. There are up to six sessions with up to five choices in each. Now I want to create a report that has a grid showing the title of each workshop session and how many people are signed up for that session choice. Help!!?!

I'm not sure I've explained this very well, but I'd be grateful for any help.

Thanks so much,

Leah

[CobaltSky]

If you wish, you could use a similar approach to the one described in my post of 9/4, to create a separate file that provides a dynamic read-out of the state of the session bookings, based on a series of relationships.

You've not said a great deal about the structure of your registration database, so I'm not clear on whether the workshop bookings are all within one record in the main file, or in separate records in a related file - nor whether the names of the workshops are popping up as a result of a lookup, a related value, a conditional value list or a calculation etc.

Nevertheless, the basic approach would be to greate global text fields with a series of choices entered into them, viz: Session A, Session B, Session C... etc.

Then you would be able to create a series of relationships which links each of these global fields in turn, to all of the time slots in your existing file(s) of registrant details/selections. Via that file, it should be possible to 'tunnel through' to pick up the associated workshop titles (ie referencing a stored calculation in the destination file which itself references a related value from elsewhere).

To go into more detail in a meaningful way, I'd need to know a whole lot more about the structure of the files you're working with - but hopefully you get the general idea, and can work with it to produce what you require.

[lrcarey]

Thanks so much for your quick reply! I thought I had it kicked, but then it turned out I was trying to build my relationship on a field that's not indexed, so now I'm stymied again. I'll try to give you a picture of what I've done:

The relevant files are a People file (which holds all the registrant's personal information), a Workshop file (which holds all the information regarding the workshop being run, and a Registration/Line Item file (which has the individual registrations in it.) Some workshops are one shots where everyone goes to the same place at the same time. Some workshops have multiple time slots with multiple workshops happening within each time slot. In the Workshop file, I have a page where the titles of each workshop can be entered (there are six workshop slots with up to five titles available for each slot.) They are labeled as Option A, B, C, etc. To register a person for a specific title in a specific slot, the data enterer chooses "Option A", which then pops in the title of the appropriate workshop by a calculation (I think there must be a slicker way to do this - I'd love to have the data entry person be able to choose from the list of titles for that session, but I haven't figured that one out either - would changing the set up of this portion make the next step easier?)

What I now want to be able to do is create a report for workshop X that counts the number of registrations for each TITLE within each slot within each workshop. After your previous post I thought I had it by relating the title of the session to the title that pops into the registration form, but it wouldn't let me do that because it wasn't indexed (a calculation pulling in from the workshop file).

Is that enough information? I will be very grateful if you can help me walk through this.

Thanks so much!

Leah

[CobaltSky]

To achieve what you waht, I suggest that you dispense with the calculation system which is producing your Workshop names, and use a look-up system based on a separate table of workshop titles. To do this:

1. Create a calculating field called Workshops_[key] in your Registration/Line Item file which concatenates together the information to identify a particular session - eg date, slot, session. The formula will be along the lines of: WorkshopDate & "|" & TimeSlot & "|" & SessionID.

2. Create a corresponding calculating field in your workshops file (eg date, slot, session) based on existing data for your workshops (I am assuming that in your workshop file you create a new record for each workshop title, which has all the perstinent details)

3. Create a new relationship called WorkshopDetails from Registration/Line Item to Workshops, based on a match between the two calculating fields described above.

4. Create a text field in your Registration/Line Item file called Workshop Title, and define it as a lookup to copy new values from the WorkshopTitle field in your Workshops file based on the WorkshopDetails relationship.

Now your workshop title field in the Registration/Line Item file will be able to be indexed and can be used as the target key for the dynamic report you want to build.

As regards your question concerning Value lists, to achieve this also, you'll need a couple more steps:

5. Create a calculating field called TimeSlots_[key] in your Registration/Line Item file which concatenates together the information to identify a particular time slot. The formula will be along the lines of: WorkshopDate & "|" & TimeSlot.

6. Create a corresponding calculating field in your workshops file (eg date and slot) based on existing data for your workshops.

7. Create another new relationship called TimeSlotData from Registration/Line Item to Workshops, based on a match between the TimeSlots_[key] fields in both files.

8. Create a value list in the Registration/Line Item file which picks up values in the SessionID field based on the TimeSlotsData relationship and enable the option to "Also display values from" - and select the WorkshopTitle field.

9. Define the SessionID field on your data entry layouts as a pop-up list based on the value list defined at step 8.

Then once a data and slot have been selected, the value list will display a list which automatically indicates the available sessions and their titles. when the correct one is selected, the session IF (eg Option A, Option B, Option C etc) will be placed in the SessionID field, and the workshop title (lia the lookup defined at step 4) will automatically appear in the title field. Simultaneously, the corresponding cell for that workshop in your dynamic report will be incremented by one.

Hope this gets you closer to the mark.

[lrcarey]

CobaltSky,

Thank you so much for your detailed reply to my query. I have spent a good deal of time working with it (I suspect I've already spent more time than this problem warrants, but unfortunately, as you will gather from the fact that I'm posting again, I haven't solved it yet.)

Here's how the files currently look:

*There is a Workshop file that has the time, date, title, etc. of the workshop. It also has six portals that bring in the titles of individual sessions within the workshop from the Sessions file.

*There is a Sessions file that has a record for each individual session - each record has the title of the session, the Workshop ID with which it is associated, the session number (1-6) with which it is associated. It is also automatically assigned a letter A-F - I did this so there would be some constant in the whole equation. I'm not sure it's necessary.

*The Registration file has a value list that pulls in the titles available for each session of each workshop.

Now I want to create a dynamic tally of the total registrations for each Session within each Workshop. I am working under the assumption that proper place to put this is the Workshop file, but whenever I try to set up the relationships between the Workshop file and the Registration file that I think will facilitate the count, they don't work because the concatenation in the Registration file is not indexed (it's pulling the info from the Workshop file to begin with!).

Is this enough information? I truly appreciate how much time you have already spent with me and hope I am not overextending my welcome.

Thank you again,

All my best,

Leah

[CobaltSky]

Hi Leah,

Try setting up fields for WorkshopID, SessionNo and Slot[A-F] in your registrations file and defining them as lookups to retrieve the appropriate data (based on the relationships you are currently using to access the data for your concatenation). Then the data will be brought into the file and stored in the new fields.

If you then point for concatenation formula at the lookup fields, the concatenation will be able to be stored and indexed and then you will be able to use it as a target key field to establish your summaries.