Populating data from one table to another

Question

Hi

I have created a database which details the sales of vehicles we manufacture.  I have been asked to expand on this by adding the facility to create sales quotations and manage vehicle ordering.

I would appreciate some guidance on the best way to populate data into a main table with data from other tables please.  Firstly, we would order a number of base vehicles to keep in stock and await a sale - These details I would like to keep in one table.

Next, we would like to be able to generate a sales quotation to a customer - Again, these details would be in their own table.

Finally, in the main table, when a sale is made against a particular quotation, I need to be able to use the data from the quotation which details the type of vehicle and the work we will do to it and populate this into the main sales table.

The idea is; the guy who orders the base vehicles for stock concentrates on that only.  Date of order, specification, forecast delivery, etc.  The sales dept generate quotations and can keep track of them and how they are going.  Then when a sale is made, the main table can 'somehow' pull that data in without somebody having to type it out all over again.  The main table should only keep records of vehicle sales and not quotations/enquiries that may never come to anything.

Can somebody guide me in the right direction with this one please.

---

Chris | UK

Answer 1

Hi Chris,

A properly designed relational database usually doesn't "move" data from one table to another. Instead, a reference to the data in the main table is stored in the related table, and the additional information is "pulled" in using queries. The concept is called "Normalization." If you're not familiar with it, may I recommend you take a look at this Wiki article at UtterAccess.

Just my 2 cents...

Answer 2

Thanks DBG

A little night time reading me thinks.  Your 2 cents have proven to be worth considerably more than historically.

---

Chris | UK

Answer 3

Hi Chris. Let us know if you have any questions. Cheers!

Answer 4

Hi DBG

After a little studying, I completely get the rules of normalisation.  Enter it once and then use it many times in many places.  It just makes sense.

So the project I have been working on has now received some serious attention.  I can't say I have completely cracked it yet; I am still working through a couple of issues that don't work but I'm sure I'll figure it.

If not, I may back!!

Thanks for your help.  I've learned  a number of pearls of wisdom these past couple of days.

---

Chris | UK

Answer 5

Hi Chris,

One rarely goes wrong by following Normalisation rules. They help manage your database better. If you run into other issues, please don't hesitate to ask. Good luck with your project.