One memo table for multiple main tables?

[Doctor]

Can I set up one Memo table that would serve as the table to store all Notes
about Events, Clients, etc. in? Is this practical? Right now I have many memo
tables for many main tables. There are some additional areas as well that I
could see benefiting from this line of thought.

Right now I am specifically looking to do this where the memo tables are on
a one-to-one relationship with the main tables they are associated with. But
I would also like to do this with one-to-many relationships.

If this would be helpful to do, would I add a field in the memo table to
reference a table? And if so, how do I reference each table? By name? I guess
I could build a query that has all ID's and table names from msysobjects if
it should be by number.

Please let me know how appropriate this is. Thanks so much in advance.

 

[Klatuu]

Why do you need tables for your memos? Memo is a field data type and can be
included in the record. There is no need to establish a one to one
relationship for that.

 

[Doctor]

Well, here's why, so I've been told. I used to keep memo fields in the main
tables, But then someone brought to my attention that a memo field or any
other field will take up as much space in the database regardless of whether
it is empty or full. So they suggested putting memo fields in another table
so that only memo fields that were full took up space in the database.

Is this correct, or have I been mis-informed?

By the way, thank you so much for all your help on these forums. I know that
you specifically have helped me many times before. I can't get over how
helpful everyone is on this site.

 

[Klatuu]

You were given incorrect information. The Jet (Access) database engine uses
only the space to contain the text you have entered for a memo field.

 

[Jeff Boyce]

And to add to Dave's response, I believe the Memo field doesn't "count
against" the total number of characters allowed per record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

I question a table with multiple memo fields as this seems to suggest
un-normalized table structures.

To get back to your original question, if you were to use a single table
with a column/field to identify the "reference a table", I would question the
updateability of a query including the memo table.

 

[Beetle]

Hi Dave

I've seen other MVPs suggest keeping memo fields in a
separate table related 1:1 to the main table not because
of space issues, but because memo fields are more
susceptible to corruption, so if it does become corrupted
you only lose the memo field, not the whole table.

Do you agree with that?

BTW - good to see you back in th ng (seems like you were
gone for a while).

 

[Klatuu]

Yes, I believe that is correct. I had forgotten about that.
As I recall, a memo field does take some space in the record, but is really
is a pointer to where the data is actually stored. So it already is not part
of the physical record.