Copy information from one table to another...

[Joven76]

I am using the order entry template that Access has to learn from and build off of...

In the template, there is a section called "orders by customer" which stores the customer's information such as name, address, phone number, then allows you to put in orders, print invoices, etc...

I have a mailing list, however not every person on that list has or will buy from me... So feel it would be very confusing if I put every person on my mailing list in the "orders by customer" since not everyone will have an order...

So, what I would like to do is this... Create a table/form with just the basic customer information (i.e. name, address, phone, email, etc.)... Then, using the customer# that is formed when I put information into the mailing list table, pull all of the information into the identical fields in the "orders by customer" table/form...

The reason I need to do this, is to get the rest of the forms, queries, reports, etc are based off of the fields in the "orders by customer" table/form... Just having a field with it's control source like [customer#].[column](2) won't work... The information needs to be stored in the field of "orders by customer" itself...

So, is there a way to do this??? Any help is greatly appreciated...

 

[armoredcars]

I'm having the same problem in Access 2007. All I want to do is take the phone # and name from my CustomerContactInfo table and put it on my order forms automatically when I enter the customer #. I have created a one to many relationship between the tables but can't get anything to work.

 

[neileg]

In a well designed relational database you only store information once. So you only need to store your person data once with a person ID. Where you want to link that person to another piece of data, say an order in another table, all you store is the person ID in the order table. You link the two tables in a query and this will return all the person data related to the order when you select that order record in the query. Creating a relationship is only the first step, you need the query too.

Caveat: Where the person data is subject to change over time, eg address changes, then you may choose to store the snapshot info which was relevant at the time. This might also apply to prices subject to change.

 

[armoredcars]

Using a query would be adding an extra step for me. All I need to do is have the customers name and phone number so we can print out a copy of the form instantly once it is filled out.

 

[The_Doc_Man]

armoredcars, you want that query because you can use it as the basis of a form or report just as though it were a table.

From the question you just asked, I sense that you need to read up on the concept of normalization, which has the effect of saving you a LOT of space in the long run. Access has help on that topic. If you choose, you can also Google-search for it. If you do, limit your reading to the .EDU sites for colleges or universities you trust. Stop after the third or fourth article out of the gazillion hits you'll make on "Database Normalization" as a search topic.

 

[neileg]

Using a query would be adding an extra step for me. All I need to do is have the customers name and phone number so we can print out a copy of the form instantly once it is filled out.

?

It will take seconds to write the query and you only do it once!