If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
One memo table for multiple main tables?
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. |
#3
|
|||
|
|||
One memo table for multiple main tables?
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. |
#4
|
|||
|
|||
One memo table for multiple main tables?
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. |
#5
|
|||
|
|||
One memo table for multiple main tables?
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. |
#6
|
|||
|
|||
One memo table for multiple main tables?
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. |
#7
|
|||
|
|||
One memo table for multiple main tables?
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. |
#8
|
|||
|
|||
One memo table for multiple main tables?
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. |
Thread Tools | |
Display Modes | |
|
|