Notes field in a Database

[AmyLynnHill]

I have a database housing insurance policies, agents and groups(schools that have the policies). Currently, we call groups and put a "note" on a policy. This is a very basic database. I created a field called "notes". The callenge I have with this field is that only one note can be contained. Therefore, if we call several times the dates and note information all go to the same field. Any suggestions on setting up some type of table that would allow multiple note fields? how would I link the table to the main information table?or another approach? Any suggestions would be helpful.......

 

[Kodo]

make another table called policy_notes.
In that table make three fields
note_id of type autonumber: (unique record ident)
policy_id of type number: this is the foreign key and will hold primary key value of the particular policy.
note_note of type memo: this will hold the note information.

When you query for the data you will need to join the data on the policy_id from both the policy table and this new table.

 

[AmyLynnHill]

Thanks for your help. I think I understand the note table structure. I'm confused on how this will allow for multiple notes on one policy? I guess i could put a button for add record to the note table?

 

[Kodo]

think of it this way. The notes table is going to hold the policy id of the policy the note is placed on right? so your data might look like this

policy ID note
1 lorem ipsum for policy 1
2 lorem ipsum for policy 2
1 lorem ipsum for policy 1
1 lorem ipsum for policy 1
4 lorem ipsum for policy 4
5 lorem ipsum for policy 5
5 lorem ipsum for policy 5
2 lorem ipsum for policy 2
6 lorem ipsum for policy 6
6 lorem ipsum for policy 6

so when you need too look up notes for say policy 1, you can pass the policy id as the parameter to search on and pull up the notes or you can use a join to get the policy info and the notes at the same time.

 

[ssteinke]

Posting your questions multiple times will be less likely to be answered... you need only to continue on the same thread you started

http://www.access-programmers.co.uk/forums/showthread.php?t=100865
http://www.access-programmers.co.uk/forums/showthread.php?t=100864