Organizing many types of data

[meirfridman]

Hallo all,

I am busy building a database for a personal security group, to record and organize the emergency calls coming in to the dispatcher.

Calls are separated (internally) into events and situations, where events are the incoming calls and situations are the (details of the) possible situations of the event.

Each situation presents a unique set of questions the dispatcher asks the caller.
(ie."What does he look like?" for a human attacker, and "What Make and model?" if it is a suspicious car)

I have a table of situations, a table of questions and a table that links situations and their respective questions. (with a separate table of events themselves linked to the table of situations to identify which questions should be asked in each event.)

The problem is how to organize the answers. With many questions featuring in more than one situation, I obviously need a separate table for the answers. However, the answers are in all different data types. for instance every caller will give his location (data type "text") the time (data type "time") and usually a description of the threat of emergency (data type "memo"). There then continue a combination of check-box questions (data type "yes/no"), pull down menu multiple choices (gender, approximate age, height, hair color ...) as well as simple text comments both numeric and textual ( "A few people, I think", "Round the corner" etc.).

In what way can I organize the data in the database, so that the user can fill the data easily and the program should be able to use the information (such as for spotting the recurrence of a description of a vehicle or perpetrator for instance)?

Thanks in advance

 

[PWise]

How about oping a diffrent for for each type of situations

 

[meirfridman]

The problem, I suppose, is that fields where the answers are text will not be searchable, or sortable by the database.

I cannot find any way to make the program search within the answers. I can of course make pull down multiple choice menus for most things, like hair or eye colour where the choices are limited, but that is not quite handy for things like number-plates...

 

[meirfridman]

1. I already have more than a hundred situations (suspicious, some types of attacks, some types of attack with cold weapons, some types of attack with firearms, etc. divide in during the situation / in the past, there are casualties / aren't, etc. and dozens of other types of events who have to be secure)
2. If you mean that every question will be a separate field it will be a bit complicated for a user to add new questions that shall be asked in several situations and also to add new situation. because it means creating new fields and new tables, give them unique names, adding them to the queries etc.

 

[MajP]

Take a look at At Your Survey

http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html.

The normal trick in most survey databases is that the question table has a field identifying the data type of the response
Text
Numeric
Date
Boolean

All responses are entered in a text field, but you then know what type of data it represents.

 

[meirfridman]

Thanks a million for that MajP.

I will study it a little and i hope i find a solution. I have already learned some although not an answer to my specific problem yet.

i will persevere...