One memo table for multiple main tables?

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.

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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

"Klatuu" wrote in message
news
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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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.

--
Duane Hookom
Microsoft Access MVP


"Jeff Boyce" wrote:

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

"Klatuu" wrote in message
news
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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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).

--
_________

Sean Bailey


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.

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.
--
Dave Hargis, Microsoft Access MVP


"Jeff Boyce" wrote:

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

"Klatuu" wrote in message
news
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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

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.