Allow multiple Memo type fields per record?

[gmlwong]

What do you think: Access PowerUsers/Veterans

In a customer/claims database, should user's be allowed to add unlimited "Notes" every time they access a record?

The idea is to try to control the user's access to previously entered notes by locking the current notes field upon record exit. Then the user is responsible for whatever he/she entered originally and must rectify their mistake(s) in the following notes field data entry.

Offering and being allowed to click an "ADD NOTES" button for any record and to add a memo sized note for a record every time they access it would cause a considerable swell in this database's file size coupled with a recent problem of records 'blowing up' the main Claims table by inserting random data and illegal values in all fields, leads me to believe that the responsibility must rest with the users and in training them to enter all notes appropriately and having a consequence if they do not.

Can this proposed unlimited "ADD NOTES" functionality work effectively or will it simply cause more grief than it's worth?

What do you think?

 

[The_Doc_Man]

Make a subsidary NOTES table linked to the claim's prime key.

tblClaim
fldClaimID, long, PK, possibly autonumber but maybe not...
data about claim....

tlbClaimNotes
fldClaimID, long, FK
fldNoteStamp, date/time
fldTheNote, memo

Then do a one-to-many relationship.
This IS unlimited - but controlled.

And you can build a sub-form to restrict the input, or use permissions on the secondary table so that you cannot edit an existing entry but could create a new one. I.e. allow APPEND but note UPDATE.

I think that would be the more traditional view. It is very similar to the way a trouble-ticket system is set up to allow detailed work histories for as many actions as required to resolve the problem.

Now, as to whether you would allow it at all... you can add a flag to a claim entry that blocks note entries. You could make the form enforce a rule that says, "one note per day, please." You could also assign permissions such that only certain people could make note entries. Perhaps as a different role. Say, for example, that you have someone responsible for note entries. That person has the permissions required. But Joe General User doesn't have the required permissions. Joe has to go through your representative. Then you only need to educate the representatives with those permissions.

 

[gmlwong]

Very helpful!

Thanks Doc!

I'll review this with my Claims manager to make sure this is wanted; someone mentioned yesterday to simply keep a Word file running but that can be quite difficult to manage and sort so I think we'll give this a go!

Make a subsidary NOTES table linked to the claim's prime key.

tblClaim
fldClaimID, long, PK, possibly autonumber but maybe not...
data about claim....

tlbClaimNotes
fldClaimID, long, FK
fldNoteStamp, date/time
fldTheNote, memo

Then do a one-to-many relationship.
This IS unlimited - but controlled.

And you can build a sub-form to restrict the input, or use permissions on the secondary table so that you cannot edit an existing entry but could create a new one. I.e. allow APPEND but note UPDATE.

I think that would be the more traditional view. It is very similar to the way a trouble-ticket system is set up to allow detailed work histories for as many actions as required to resolve the problem.

Now, as to whether you would allow it at all... you can add a flag to a claim entry that blocks note entries. You could make the form enforce a rule that says, "one note per day, please." You could also assign permissions such that only certain people could make note entries. Perhaps as a different role. Say, for example, that you have someone responsible for note entries. That person has the permissions required. But Joe General User doesn't have the required permissions. Joe has to go through your representative. Then you only need to educate the representatives with those permissions.

 

[The_Doc_Man]

In theory you can do this Word-file suggestion, too. Access has the ability to open a Word document as an application. Your problems, however, become serious headaches when you go to migrate your data to other database types (if you happen to head that way in the future...)

While I do not recommend this method, it IS possible for folks who can stomach extensive VBA operations. When you talk to the folks who made that suggestion, explain to them that automating the update of a separate Word file has specific weaknesses:

1. Folder glut as the number of claims increases over time.
2. Slowed response as the folders become glutted.
3. More extensive programming required hence higher costs related to
3.a. More programming to update the file from Access.
3.b. More programming to display the file from Access.
4. Will probably have to revert to my suggestion later if you ever upgrade the database to something like SQL server or ORACLE server.
5. Your Ops people will hate you when they do backups of that many individual files.
6. Data recovery becomes an issue.
7. Security of the (now separate) claim file becomes an issue.

Oh, by the way: If they say "Do the Word Update manually, not through Access" then only item 3 goes away.

 

[stormin_norm]

What do you think?

Add the extra notes table. You have a claim tracking system, why not give the users the power to lookup what other users thought or why they entered the info. You have this ability with Siebel or other CRM applications
Plus - all the more reason your database will become the "one stop answer guide" to a claim. You have built the indispensable tool for your colleagues.

Also- Size shouldn't be a big deal. These notes should not be that lengthy.
How many claims? how many notes on average?? 1-3?

 

[gmlwong]

Yes, the notes table is what we'll do...

Thanks "Doc" and "stormin_norm"!

Locking these fields once completed will help to protect data entered and prevent that eraser ability and mentality.

Any given record may need ±50 notes over the course of its life and each note (abbreviations highly recommended) generally contains about 20-25 words; time/date stamp, shop name/contact, and failure description(s). About as much as seen in this paragraph is typical.

Add the extra notes table. You have a claim tracking system, why not give the users the power to lookup what other users thought or why they entered the info. You have this ability with Siebel or other CRM applications
Plus - all the more reason your database will become the "one stop answer guide" to a claim. You have built the indispensable tool for your colleagues.

Also- Size shouldn't be a big deal. These notes should not be that lengthy.
How many claims? how many notes on average?? 1-3?

 

[gmlwong]

How to...

Thanks again for the notes table help!

How can I add code, "behind the scenes", to identify a particular user and when they access any given record and to what field(s) they make changes to so that I can audit this activity?

In theory you can do this Word-file suggestion, too. Access has the ability to open a Word document as an application. Your problems, however, become serious headaches when you go to migrate your data to other database types (if you happen to head that way in the future...)

While I do not recommend this method, it IS possible for folks who can stomach extensive VBA operations. When you talk to the folks who made that suggestion, explain to them that automating the update of a separate Word file has specific weaknesses:

1. Folder glut as the number of claims increases over time.
2. Slowed response as the folders become glutted.
3. More extensive programming required hence higher costs related to
3.a. More programming to update the file from Access.
3.b. More programming to display the file from Access.
4. Will probably have to revert to my suggestion later if you ever upgrade the database to something like SQL server or ORACLE server.
5. Your Ops people will hate you when they do backups of that many individual files.
6. Data recovery becomes an issue.
7. Security of the (now separate) claim file becomes an issue.

Oh, by the way: If they say "Do the Word Update manually, not through Access" then only item 3 goes away.