Organizing many types of data in one field

[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

 

[TheAceMan1]

How are ya meirfridman . . .

Better to post this in forum700

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

What I have seen in other databases, where the data type is only really known at run time (a common example is when you allow the user to create their own "custom" fields), is that there are multiple fields, each of a different data type, out of which only one is used as the actual field at run time.

In your case, you might have:

AnswerBoolean, AnswerText, AnswerDate

In your Questions table you could have an AnswerType field that indicates if the expected answer is Boolean, Text or Date.

Mind you, this will make your queries, forms and reports more complicated to set up. From the point of view of the user, there is only one Answer field. You basically need to pick at run time which of the 3 fields is the actual field to display, and then alias that as the "Answer" field. In your queries this would require a couple of embedded "IIF" functions. In your forms, you would need to do something like making one control visible while the other two are invisible (based on the AnswerType field).