Table design on an issues database

[Fr0]

Hello all,

I'm creating an database of issues that occur at my company in access, and I need some help with a little bit of table design.

Right now, I have an Issues table that has the following fields:
ID - Autonumber (PK)
User - Lookup to a table of users
IssueDate - date/time
WhatHappened - Memo
SupervisorApproval - Yes/No

I've created forms for users to use for entering issues, searching for issues and viewing issue details.

Now, I want supervisors to be able to add (multiple) comments to each issue when they approve/reject an issue. I figured the two ways of doing this would be:

1) Create a "Comments" memo field in the Issues table, and create a form with a textbox where a new comment is appended to any previous comments with the date and user's name.

2) Create a Comments table that stores the user's name, date, time and the comment, and create multiple forms to create and view the comments for each issue.

I've tried implementing both, and I keep getting weird locking issues with #1 (it says that another user is currently editing the field, then displays "?#Deleted"). I would use #2, but the complexity added with all of the forms to create and view comments might be annoying for the end users, as well as for me.

My questions are:

Why would I be getting the locking issues with #1?

Does anyone have other suggestions for implementing this?

 

[Kiwiman]

Howzit

I would go with #2 with a couple of mods

In regards to the Issues tables you should have as a minimum

tblIssues

• IssueID - PK AutoNumber
• IssueDescription - Memo
• IssueDate - Date\Time
• SupervisorApproval- Yes\No
• UserID - FK (from tblusers)

tblIssueComments

• CommentID- PK AutoNumber
• IssueID - FK (from tblIssues) Long Integer
• Comment- Memo
• CommentDate- Date\Time
• UserID - [FK] (from tblUsers)

Create a relationship between the two tables, enforcing referential integrity - this should create a 1:n (1 to many) relationship.

You should only need two forms:

The frmIssue - bound to table tblIssue, and a frmIssueComments bound to table tblIssueComments.

On from frmIssue, insert a subform control, where the source object will be the from frmIssueComments. The link if access does not do it for you will be on IssueID

When you navigate to eash record on the main form, it should only show the comments relating to that issue...

This should get ya started

 

[Fr0]

Thanks for your quick reply.

I forgot to mention two big things in the original post:

I'm trying to separate out data entry from data viewing, so at the very least I would need two forms for #2 (versus only a form to add a comment for #1, while the comments field could be view with the rest of the issue data).

I would like all of the comments to show on one form, instead of having to click through records to view all of the comments. I was doing this with a subform in #2, and then having the users click on a line within the subform to bring up the details of the comments. That's the reason I said that #2 would add extra complexity.

Edit: Ok, either I didn't read your original post closely enough, or you edited it after I posted this... but ok, I'll try that out. Thanks!

 

[olorin]

Hi,
One other comment, if I may throw in my two-cents worth.
I would use a number field instead of the Yes/No field. You could use 1 for Yes and 0 for No.
I read somewhere that the Yes/No data type in Access 2007 can slow things down in a multi-user environment.
Just a thought.