Can't contain a Null value?

[Katherine R]

Please help me get started. I need two forms. Do I need
one or two tables?

Form one will include the primary key - ComplaintNo, which
is an auto number, and fields for the location of the
complaint, a description of the complaint, the employee
that takes the complaint, and the employee the complaint
is assigned to for investigation. The employee that takes
the complaint will process this form.

Form two will include the date & time of the
investigation, the findings of the investigator, and the
case number as assigned by the investigator. I need the
case number to be a required field with no duplicates
allowed. The employee that investigates the complaint
(this may be different than the employee that takes the
complaint) will process this form.

If I build both forms from one table, form one returns an
error message that CaseNo can't contain a Null value
because the Required property is set to True.

Do I need two tables and a one-to-one relationship? How
do I do this?

 

[Tim Ferguson]

Please help me get started. I need two forms.

Golden rule = One form for each process (i.e. call logging, processing
actions, signing off resolution, etc)

Do I need one or two tables?

Aha -- this depends on the actual data design and what you are modelling.
And is a much more basic question than the number of forms..!

Form one will ... The employee that takes
the complaint will process this form.

Okay, a call logging type of thing.

Form two ... The employee that investigates the complaint
will process this form.

Okay, investigations. That is fine. Don't forget you'll also need forms for
managing the Employees list, creating new ComplaintTypes and so on.

About tables -- well, there is just not enough information here to offer
definite advice. At a guess, you'll need tables for Employees, Locations,
ComplaintTypes, ResolutionCodes. There will probably be one for
ComplaintCalls. I don't know how you will manage the investigations -- you
might have several investigative processes or phases for each
ComplaintCall, or you might be able to process several related
ComplaintCalls in one investigation. Or perhaps many people make Calls
about the same Incident.

Sorry if that sounds a bit negative, but DB design is about actually
understanding all the parts of the world you are modelling -- and not just
tbe "usual" bits but particularly the things that happen occasionally. DBs
don't die because there is nowhere to record a patient's name; they die
when it's not possible to record two doctors sharing care of one illness.
Similarly, your design will lose its credibility because somewhere down the
line when Jones and Smith both complain about the same thing, but need
separate feedback... Et cetera.

Hope that helps


Tim F