Normalize current database

[jmhicsupt]

I have a database that I started about 2 years ago. I know I designed it incorrectly so now I want to try to right my wrong.

My database has a table that has:
Product1
OneTimeCost1
RecurringCost1

Product2
OneTimeCost2
RecurringCost2

Product3
OneTimeCost3
RecurringCost3
...all the way up to Product5...

Now what I want to do is convert this into:
Product
OneTimeCost
RecurringCost

Is there a way that I can take whatever is in Productx (where x is 1,2,3,4 or 5) and put that in "Product". And then take OneTimeCostx and put that into "OneTimeCost" -- and then RecurringCostx into "RecurringCost"?

Then I want to do a one to many relationship joining on the CustName.

I hope I gave all the information needed to answer my question. If not, let me know.

Thanks in advance.

 

[KenReay]

You could use a UNION query to produce a "Normised" list of your data, something like

SELECT Product1 As PRoduct, OneTimeCost1 As OneTimeCost, RecurringCost1 as RecurringCost FROM MyTable
UNION
SELECT Product2 As PRoduct, OneTimeCost2 As OneTimeCost, RecurringCost2 as RecurringCost FROM MyTable
UNION
...etc to product5

then use that query in an append query to populate your "Normalised" table

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jmhicsupt]

I have created a UNION query and now I have all of my data in four columns -- Client, Product, OneTimeCost, RecurringCost.

How do I now do "then use that query in an append query to populate your "Normalised" table"?

Thanks so much for the quick response.

 

[lespaul]

Assuming you already have NewTable setup:

INSERT INTO NEWTABLE (SELECT * FROM NormalizedQuery)

I believe there's also a way to tell access to convert the query, maybe change the query type?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design