Access 2010 Design question

[Smashie]

Hi,
I have a user who wants a form to have loads of qualitative data on it. Looking at her requirements it would mean at least 50 memo fields. To do this on a form and then save the data would quickly create a huge database.
My initial thought was to export the form to MS Word and use some VBA to save the file as clientNumber.doc but I soon saw that you can only export the data - and I don't want all those memo fields as data clogging up my DB!

My next idea was to store the quantitative data in the main client table and all the memo fields in a secondary one - only using that table when I needed to.

Does anyone have any ideas as to how I can satisfy my client (who wants all the info on one form) and not create a monster DB!

MS Access solutions preferred although ultimately I may use other means to achieve the goal.

Many thanks for your kins assistance.

Steve

 

[dhookom]

I would look at using a related table with one memo field. You would need to also store the primary key from the main table and another field to describe which memo you are storing.

Duane
Hook'D on Access
MS Access MVP

 

[Smashie]

Thanks. The 'business user' will have lots of memo fields though.

My suggestion at this stage is to double key unfortunately. She wants ALL the information on a form and not as tick boxes or in a quantitative way. So unless there is a better solution I intend to store data in the database where it is pertinent and to have a link to a Word document that is linked by Client number.

Does that sound ok?!!

Thanks

 

[dhookom]

Why "lots of memo fields"? Did you understand my suggestion?

For instance, if you have a table of projects with comments fields for each project participant like:
tblProjects
========================
ProjectID
SafetyNotes
ProductionNotes
HRNotes
FinanceNotes
---etc---

I would move all of the Notes fields to a table like:
tblProjectNotes
====================
ProjNotesID
ProjectID
NoteFrom (values like Safety, HR, Production, etc)
NotesDate
Notes (your memo field)

This would allow for any number of notes about the project.


Duane
Hook'D on Access
MS Access MVP