Normalisation of Tables and Form Issue

[Dave177]

I am trying to set up a form for users to use for reviewing reports of sites. The reports contain information (positive and negative comments) regarding eg. staff, food, and facilities. The idea is that the person reviewing the report would easily be able to tick the appropriate check box for any of the listed criteria. There should be two lists on the form - one for positive comments and one for negative. Some criteria appear in both lists and can be ticked for both positive and negative comments.
Initially I thought I would have one [tblReports] table with [Board] and [Date] to be combined as a primary key. I would then have various fields eg. [PFood] for a positive comment about Food and [NFood] for a negative comment on food. These fields would be Yes/No check boxes. A lot of the fields would remain unchecked and it also appears that it is an Unnormalised database. Is this correct?
eg.
tblReports
Board Date PFood NFood PStaff NStaff etc.
A 2001 1 1
B 2001 1 1
C 2001 1 1
A 2002 1 1
B 2002 1 1 1
C 2002 1 1

Is this Unnormalised?
I felt it was so thought I should set up the following tables

[tblPositiveIssues]
with field: [PositiveIssue] eg. Food, Staff, Activity

and
[tblNegativeIssues]
with field: [NegativeIssue] eg. Food, Staff, Delays

and
[tblReports]
with fields: [Date] Combines to make primary key
[Board] Combines to make primary key
[Author}

and
[tblReportedPositiveIssues]
with fields [ReportedPositiveIssue] as an autonumber?
[Date]
[Board] above two are foreign
key for [tblReports]
[PositiveIssue] effectively foreign key
for [tblPositiveIssues]

and
[tblReportedNegativeIssues]
with fields [ReportedNegativeIssue] as an autonumber?
[Date]
[Board] above two are foreign keys for
[tblReports]
[NegativeIssue] effectively foreign key for
[tblNegativeIssues]

This seems to me, though tell me if I am wrong, more normalised. However, I cannot find a way in which I would be able to set up a form with check boxes to highlight positive/negative issues. Is it a choice between either check boxes or normalisation? Or am I worrying unnecessarily about normalisation?

Thanks for any advice.

Davod

 

[PHV]

You may have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dave177]

PH,
Thanks alot - I will give it a read.

D

 

[Dave177]

PH,
I've read the article but am still having problems.

I have the following tables and fields

tblReports
ReportID (primary key autonumber)
Board
Date

tblNegativeIssues
NegativeIssueID (primary key autonumber)
NegativeIssueDescription

tblNegativeIssuesReported
NegativeIssuesReportedID (primary key autonumber)
ReportID (foreign key to tblReports)
NegativeIssue (foreign key to tblNegativeIssues)
Commented (a check box)

I have made a form from tblReports and then a subform for tblNegativeIssuesReported and linked them by ReportID. However what I would really like is for a check box to appear in the subform for all of the categories in tblNegativeIssues. Then the user would be able to tick off the relevant boxes. I am stuck on creating such a query for this subform?
Do you have any ideas? I would be very greatful for any help.

D

 

[PHV]

Instead of lot of CheckBoxes you may consider the NegativeIssue control as a ComboBox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dave177]

PH,

Thanks for the idea. The only problem is that there are a lot of possible negative issues (about 50) so it would involve a lot of scrolling etc on the combo boxes. Also, the reports are quite long so the user may forget whether a Negative Issue has already been entered. With a list of check boxes this would not happen.
I have just been looking at thread702-887861 and a person uses a list box for a similar problem. Would this be an idea?

D