one table or many?

[Sprocket]

I have an Access 2003 database split FE/BE. Back end is on a server - local front ends.
One table is causing problems - or to be more precise one field. This is a memo field that is becoming full and hitting an overflow problem. The data in this field is notes about various discussions held over time - up to 5 years. What seems reasonable to do is to separate this filed out for each of the years so now I could have one table called say Yearly Notes with one field for each year 2003 - 2004 - 2005 etc or I could have 5 tables each with just one notes field (plus primary/foreign key relationships) The data would be viewed on a single form, with one large visible field for the current year and thumbnail size fields down one side of the page. I have set the on click value to use Sendkeys command for shift + F2 which is the zoom feature, so if I want to look at previous year notes I click in the field and it expands so I can read the notes.

I have tried this out both ways and both ways work. I can display the data on the same page of the tabbed multi-page form but this is obviously pulling all the information all the time even if it is not always being accessed.

I have also tried creating a totally separate form - for the previous year’s notes - that opens from a command button whenever I need to look at that data - - this also works.

And that is my real problem; as all these alternatives seem to work OK - I just can't decide which way to go.


Any thoughts gratefully received.

 

[Newman]

I am no expert, but wouldn't it be easier to use a link to a doc or txt file instead?

 

[Sprocket]

Hi Newman,

Thanks for your post.

Yes that is another possibility and I have tried it that but there is a fairly serious performance hit going that route as the word application loads up, especially if a colleague has the same record open which is quite often the case.

Thanks... Sprocket

 

[neileg]

Another alternative is to create a new entry in a table for each addition to the notes, then display these records in a meaningful way. Say show just the latest entry in normal view, with a button to show this year's entries or perhaps the entire history.

 

[Len Boorman]

Another thought

If the discussion is tied to a "Subject" or similar then have a table that has 3 fields.
Subject... being FK from appropriate table
Date........Simple enough
Discussion.. Memo

Then you could call up all discussions on a Subject or a date range. Continuous form to display
Report similar but set can grow and can shrink on memo field to yes
Maybe a bit expensive on the memory side but you would only have the field when and if required and additionally it would be very easy to get discussion by any data range the user wants/needs

Just a thought

L

 

[Anonymous_354]

If you're having an overflow issue, why don't you use some SQL stuff? You'd get rid of your overflow issue (unless you happen to write a report in the notes area) and you wouldn't worry about multiple people accessing it at the same time. Again, just a thought.

 

[The_Doc_Man]

The "correct" way to do this is to NEVER allow an append to the memo field. Instead, something like this:

main table:
TopicID (Primary Key)
other data

discussions table:
TopicID (Foreign Key)
When (date/time)
Memo field for ONE DISCUSSION. (That way, your notes can take up to 65K per session.)

Make TopicID and When part of a compound primary key.

Since you didn't start it this way, it would be a bear to convert the old data. But this is what you SHOULD have done. Archiving or cleanup becomes easier because you could then delete discussion notes by date < some limiting data without referencing any other table.

If you chose to instead split the BE into multiple BE and put the archived discussions in an alternate BE, you still could use date criteria for selection.

 

[Sprocket]

Thanks for the input guys. You are certainly making me focus on what I am trying to achieve. When I started the database 10 years ago I never envisaged having clients hanging around for more than 3 to 4 years and I didn't appreciate the issues involved at the time.

I'm not an IT techie just a guy who needed a database - but I bet you have guessed that by now!!!

One thing I perhaps should have pointed out at the beginning is that I do have a visits table which also has a notes field so I can keep detailed notes of each individual visit. The running notes field, which we are dealing with here, holds more "holistic" data; a summary in way but also containing none-visit related data/observations and musings on possible future action. This notes field does need to cover the wider context so does not sit well in a fragmented notes by visit record. However, if I can see a years worth of notes ata time that would be OK.

Regards.... Sprocket