Hi,

I am trying to re-design our database and have so much data to manage that I'd like to know if there is a quick and tidy way of re-designing all of this data.

Our "database" at the moment is one table with over 2000 individuals and over 54 columns. A large part of these columns are: 'Date1' 'Notes1' 'Date2' 'Notes2' 'Date3' Notes3' etc up to 9.

What I think is the best way to tabulate these entry notes (1-9) is by giving them each a 'note_ID' and creating a table including only these dates/notes and the 'Individual_ID' which I will also create.

Hence, my question is how can I tease out all these 'date1' 'notes1' 'date2' etc into a managable table with only 4 columns ('Individual_ID' , 'Note_ID' , 'Date' , 'Notes') without losing sight of which indiviual they each belong to? (And without having to go through thousands of indivuals separately!)

Please let me know if a further explanation of my situation is needed - many thanks.

Sarah

Hi Sarah

Thank you for question.

Your solution seems to be the correct one. The database should comprise a series of related tables . This can be somewhat involved, and requires a knowledge of data normalisation, which is covered on the intermediate level course.

Having created your new database you need to transfer the data across. In principle this is fairly straighforward and would involve using "Update Queries". The trick however is to make sure that the data is consistent, e.g. that the correct person is associated with their correct notes. The precise way in which this would be achieved depends on the exact nature of your existing database.

I would recommend that you attend an Access Intermediate course before you tackle this, as you would gain a clearer insight of the issues and solutions. As an alternative you might consider arranging some consultancy where one of our trainers could attend your office and work through the solutions with you

Regards
Stephen