Need more space

[Sprocket]

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

 

[Simon_MT]

Your Continuous Notes should be in a separate file as they can expand and can be "hooked" into the Client and/or Visit. This could be presented as a Subform back on the Viewing Form.

To hyper-link the information needs re-purposing which I would try to avoid.

Simon

 

[Sprocket]

Simon_MT,

Thanks for your response but I do not really understand what you mean when you say a serarate file - do you mean a separate table? If so what field type do I use to hold and dislpay the data. My reading of the manuals and help files appears to state that the "memo" type field holds the largest volume of text, However, this is insuffient for my purposes. Is there another way of displaying this data?

Regards... Sprocket

 

[KeithG]

I replied to your other post of this same question.

 

[Sprocket]

Sorry KeithG - I didn't realise I had posted twice - didn't mean to.

I make brief notes related to each visit but instead of having a notes field in the "Visits" table I have put this field in the "Client" table and called it "Continuous Notes". This way when I bring up a client I can see all my previous notes in one place without looking at the record for each individual visit. My problem is I have run out of space in the type of field I am using and I can't find another field type that will hold more data.

Hope this clarifies the problem.

 

[KeithG]

You need another table for your notes with a one to many relationship from Client to Note. This way you can have many notes for one client. You can use a subform to display all of your notes on your form.

 

[Simon_MT]

Sorry, I should have said another Table. When you think about it if you have Notes embedded in the Client there is a overhead albeit small created everytime this Table is accessed. By separating the notes this information is only accessed when required.

As I said it may not make a lot of perceptable diifference, however if you have thousands of records it is a more efficient method of handling these type of fields.

Simon