Your suggestions on a database design please

Urbane

Registered User.
Local time
Tomorrow, 05:39
Joined
Feb 25, 2013
Messages
11
Hello,

I have created a database to record, categorise and assign responses to correspondence received by my organisation. I'll outline what the database needs to do and how I arranged it. I would then really appreciate some tips on how I might design it better in the future. Please keep in mind that I am an absolute amature at Access and have been working my way through this by trial and error.

We use Access 2003.

Needs
Letters come into our organisation. Data entry people need to enter standard info such as name, address etc. into the database via a form. Professional staff then analyse the letters. They use a form to tick boxes to categorise the content of the letters, and fill in a memo field summarising it. More than one category is common. Based on what boxes have been ticked, the database then needs to draw together a series of standard responses to go into a response field. Responses are constantly subject to being changed. The data is finally used in a mail merge so that the correspondents receive a letter back with responses to their concerns.



Current Design
  • I have one massive table that contains all the personal details, summary field, and seperate fields for each category tick box (there are 60 different categories).
  • This table also has seperate fields for a category code for each category which gets populated via a series of update queries (if check box is ticked, the category code is inserted into the relevant field. A seperate series of queries runs after these which goes through a nulls the code field where the box has been unchecked).
  • In the same table, there are more fields for a response to each category
  • I have separate table for the categories. It has fields for a code for each category, a description of the category, and a standard response to the category.
  • A series of update queries links the two tables and where the category code is present and matches, it inserts the response from the category table in the relevant response field on main table.
  • An update query then merges all the response fields into a single final response field.
  • A function in VBA then tidies up the final response field so that there are two lines in between each response and removes excess carriage returns.
  • A macro runs all the queries (about 180 in total) and the function.
  • The data can then be used in our mail merges.
The problem
It does what we need but It's very akward and clunky and needs so many queries! Changes to the responses are fine (simply change the responses in the category table and run the macro), but adding new categories is a huge pain. The main table is also massive and I'm approaching the limit to the number of fields it can accommodate. There's lots of data; we commonly receive thousands of letters at a time, so the main table has thousands of rows.

Ideas?
Can anyone suggest a better way for us to design this database so that it does what we need it to? Any help is much apreaciated!

Thank you!
 

Users who are viewing this thread

Back
Top Bottom