Help condensing 10 fields into 3 (1 Viewer)

Hi

I am trying to create a mail merge from an access db where the whole structure of the db is wrong for doing this!

The db takes inputs from a questionnaire about ten local news stories and whether they are of interest to the reader. The reader is asked to rate their top three stories, marking first with a 1, second with a 2 and third with a three. The other 7 stories have a default value 0.

Unfortunately each story has its own field in the db which must contain 0,1,2 or 3.

I want to be able to do a mail merge where there is a brief sentence relevant to each news story they marked as being in their top three and no reference to anything that wasn't.

This would be easy if I had a db structure like this:

UserID TopStory MidStory ThirdStory
1 3 6 10
2 9 1 7
3 2 10 1

But I don't. My DB structure is like:

UserID StoryA StoryB StoryC StoryD StoryE etc.
1 0 1 3 0 0
2 1 0 2 0 0

Can anyone think of a way to transform my bad structure into something useable? Unfortunately there are 3700 records in it, so it can't easily be done mannually.

Thanks in advance for any suggestions.

Tim P

timp said:

Can anyone think of a way to transform my bad structure into something useable? Unfortunately there are 3700 records in it, so it can't easily be done mannually

Click to expand...

You have a design flaw - a repeating group. The fact you have StoryA, StoryB, StoryC, etc. is an indication that your database requires a further table.

Do a search on database normalisation. With an understanding of this you can see where and why you have a poor design and maybe how it can be remedied. It will take changes to the structure and then a few action queries to ensure your data is stored correctly.

I fear, though, that your database may be too far developed via queries, forms, etc. that only a complete redesign may be effective.

On the other hand, it could possibly be done in query using a complex VBA function to determine the result by passing in all ten fields as parameters and determining their rating.

Hello

Thanks for your reply.

Yes, I realise I have a flaw in the design and I know it is not normalised. That's why I want to correct it!

The structure was done the way it was because initially it was conceived only to help with a statistical analysis of responses, for which this structure works fine (and indeed a properly normalised one wouldn't).

I now want to repair the 'damage'. There is no other bagage - forms etc. The data was put in over the web using asp pages and has now finished - there will be no more data added so nothing to lose by changing the structure.

Unfortunately I am not a vba expert, but any clues as to how to go forward would be much appreciated.

Thanks again for your input.

You can use a union query to normalize the structure.
Select UserID, "StoryA" As StoryName, StoryA As Story
From YourTable where StoryA <> 0
Union All Select UserID, "StoryB" As StoryName, StoryB
From YourTable where StoryB <> 0
Union All Select UserID, "StoryC" As StoryName, StoryC
From YourTable where StoryC <> 0
etc.