Help needed with improving poor table design

[Bachfile]

Hello everybody,

I have a table called Sponsors which lists the sponsors and the concerts each sponsor has sponsored over the past ten years. The only problem is that everything is on a single table, when it would be much more user friendly to look at if the data was arranged on a Form in a one to many relationship (i.e each sponsor with the many data as all the concert data). The many data could easily be displayed on the Form as a Subform. How would I go about it without having to reenter all that data manually in a subform? Is there a way?

Thank you very VERY much in advance!

 

[John Big Booty]

This is how I would do it, others on this forum may have alternate methods.

Step 0
WARNING: Make a back up of your database before embarking on this process

Step 1
Create a new table, that has at least two fields one for and ID number (Autonumber) the second for the filed you wish to normalize.

Step 2
Create a select query (Group by) that will select the records you with to normalize. when you run the query you will easily be able to spot any anomalies in your date ie. things like Rd., rd, RD, and variations there of, and correct them.

Step 3
Correct any anomalies, then run the query again to ensure you have removed all anomalies. As your query is a select (group by) query you should have only one occurrence of each record.

Step 4
Change your select query into an append query and append it into the new table you created in step 1

Step 5
You will now need to create a new select query that selects both fields in your new table and the related record in your original table. Check that you are happy with the way the query is selecting the records and matching them with your new table.

Step 6
Once you are happy with the results of step 5 convert your new query to an update query to update the record in your original table to the matching record ID in your new table.

Step 7
Check your data to ensure that the query has run as expected. This is the point where your back up becomes important, in case you have made an error in the construction of your update query.

Step 8
Get rid of the two queries your wrote for this process, that part of your data should now be normalized.