Table design question - memo fields How many is too much,

[ToyFox]

I am going to be building a database app for a user that has been entering her data into a spreadsheet. The sheet contains about 8 or so text fields that may exceed 255 characters.

After importing and converting into Access about 800 records
The table would grow by 50 records per month....is that many memo fields going to cause me issues at some point.

 

[dhookom]

When I see more than 2 memo fields, it raises a red flag regarding normalization. Can you tell us anything about how the fields are used, what you are storing, etc?

Duane
Hook'D on Access
MS Access MVP

 

[ToyFox]

Essentially the user currently tracks breaches. She enters a lot of text into a spreadsheet, and there are 8 fields of text. It looks like it is possible that any one of the fields could contain 250-300 characters...

 

[dhookom]

You have told us twice about the same thing. You haven't provided how the fields will be used or what exactly will be stored in the memo fields. We already knew there would be 250-300 characters.

Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

ToyFox, what is IN each of these fields? WHY do they have to be 300 characters?

Are they phone messages or something?

If they can be broken up into smaller fields, you'll need to do that to achieve normalization.

A couple of questions you can ask yourself:

Is there a lot of text that is repeated in each of these fields?

If that's the case, then you should split up the fields and link them.


Like dhookom said, you're going to have to be more specific about your fields before you can get help. If you could post a couple examples of what is in these giant fields, that would probably help a lot.

 

[dhookom]

I don't think the answer would be to split the fields into smaller fields. I would probably consider creating more records with fewer memo fields.

Duane
Hook'D on Access
MS Access MVP

 

[ToyFox]

OK, here are the fields. Item_Summary, Item_Details, Corrective_Action, Scope_Description, followup_Updates, information_Shared

Now, I can speak with the user about combining Item_Summary and Item_Details....but my question is

If these fields are memo types, I envision a table of about 900 records upon converting from Excel to Access and a growth of maybe 200 per year.

Or...should she keep this in Excel.

 

[dhookom]

I would still probably recommend using separate records for each memo. Consider creating a Notes or Comments table with a foreign key to your existing table. Add a field for comment type, date of comment, maybe author, etc.

Duane
Hook'D on Access
MS Access MVP

 

[BlueHorizon]

Hi,
What is the purpose of putting this in Access? Usually, it's to use the relational aspects to enable querying and reporting functionality.

I can't see the purpose of using Access to store memo notes like you're describing. I think Excel would be your best tool.

HTH,

Best,
Blue Horizon

 

[dhookom]

I think Excel would be your best tool

You might want to check out thread68-1525860 regarding storing long text in Excel. IMO, a database is definitely much better at storing on formatted text.


Duane
Hook'D on Access
MS Access MVP