Many to one relationships

[VWilson39]

This problems seems real complicated to me. I have been working on it work several weeks. There is definitely something I don't understand. I have a database that is set up for a youth program. The primary field is SSN. The data base has demographic and it tracks up to 3 goals and activities per client. The database also tracks work history and case notes. I was trying to get a form that would allow the user to put in several casenotes per client. Ex: Shawn is a case manager with 80 clients. She need to enter case notes per client. Each client may need 3-4 casenotes entered on different dates. When I set this up I use one memo field for case notes well as I figured out that will not work with multiple casenotes per client. I was able to get a form with a subform going to multiple casenotes but the problem is if I enter a casenote in client 1 then the other 79 clients have the same case note entered in their case note field. I hope someone can help and this is not totally confusing.

 

[Pat Hartman]

The casenotes table needs an autonumber field as its primary key. It should also contain the date and time of the note. Use the Now() function to supply a value for the date/time in the form's BeforeUpdate event. Don't let the user enter this information. The table also needs to contain the ClientID so that the casenotes table can be joined back to the client table.

Once you have added the correct fields, you can open the relationship window and define a relationship between the two tables. Check the enforce referential integrity box and then check the cascade delete box. This will tell Access that if you delete a client record, it should automatically delete the related casenotes records.

To manage the notes, use a subform on your main form. Set the master/child links to link the forms on ClientID and Access will keep the forms in sync.

Take a look at northwind.mdb or orders.mdb. The order to orderDetails relationship is the same type of relationship that you have between client and casenotes.

 

[VWilson39]

Thanks! I will try this.

 

[VWilson39]

Thanks alot Pat! This did work. I was in the right direction, I just did not have the relationships right. So I follow your direction and saw my mistake. Regret I did not seek advise earlier. This is a great site.

Vaughn