Hi,
I have a multi-table database in Access 2003 - the problem I have concerns just 2 of the tables the rest is fine.
Table 1 Client
Table 2 Visits
The client table records the client’s personal details - name, age, gender etc.
The visits table records the date of the visit and a series of option groups to record what the visit was about and what follow-up action is proposed, from a range of predetermined resources.
There is a one to many relationship - each client can make many visits.
There is one rogue field however, "Continuous Notes", although this really relates to visits it is actually in the Client table. This is because when seeing a client for any particular visit I need to be able to review the notes from all previous visits without going back through the record of each individual visit.
Anyway I am using a memo field for this and it has worked well for several years. However, I now find that the volume of data in this field has hit the field "overflow" barrier. My quick and dirty fix has been to set up a hyperlink for those records that have hit the barrier and copy all the previous years (sometimes several years) notes into the hyperlinked document.
Again this works but I have the horrible feeling that this is not the right way to go about this. The main problem, apart from speed, is that the data is not self contained anymore - the hyperlinked documents being in a separate folder.
Any thoughts about how to improve my design would be most welcome.
PS another thought - The main database is password protected and encrypted but the hyperlinked documents are not. How do I protect the hyperlinked documents separately but allow the database to access them?
Regards... Sprocket
I have a multi-table database in Access 2003 - the problem I have concerns just 2 of the tables the rest is fine.
Table 1 Client
Table 2 Visits
The client table records the client’s personal details - name, age, gender etc.
The visits table records the date of the visit and a series of option groups to record what the visit was about and what follow-up action is proposed, from a range of predetermined resources.
There is a one to many relationship - each client can make many visits.
There is one rogue field however, "Continuous Notes", although this really relates to visits it is actually in the Client table. This is because when seeing a client for any particular visit I need to be able to review the notes from all previous visits without going back through the record of each individual visit.
Anyway I am using a memo field for this and it has worked well for several years. However, I now find that the volume of data in this field has hit the field "overflow" barrier. My quick and dirty fix has been to set up a hyperlink for those records that have hit the barrier and copy all the previous years (sometimes several years) notes into the hyperlinked document.
Again this works but I have the horrible feeling that this is not the right way to go about this. The main problem, apart from speed, is that the data is not self contained anymore - the hyperlinked documents being in a separate folder.
Any thoughts about how to improve my design would be most welcome.
PS another thought - The main database is password protected and encrypted but the hyperlinked documents are not. How do I protect the hyperlinked documents separately but allow the database to access them?
Regards... Sprocket
