Copying data from one table to another

[Plumber]

I have a form using name, address, phone, etc. from Customer table and job details from Jobs table. The Jobs table also has address, phone, etc. There are instances when the address, phone, etc., are the same on the Customer table as on the Jobs table. I want a control (button, check box or radio button) that when clicked, will copy the address, phone, etc. from Customer table to Jobs table. Please help.

 

[boblarson]

1. You should not be copying that information to the jobs table.

2. You store the Customer ID in the jobs table and then you have the address already with the Customers table you can get it when you want by using a QUERY (which can be used in 99% of the places a table is used).

 

[Plumber]

Bob,

Thanks for getting back to me so quickly. What happens when to information in the Jobs table is different from that in the Customers table?

 

[boblarson]

Bob,

Thanks for getting back to me so quickly. What happens when to information in the Jobs table is different from that in the Customers table?

What do you mean? The customers table has different data than the jobs table. The customers table stores customer information(Customer Name, Address, City, Region, PostCode, Phone, Email, Website, Credit limit, etc.) whereas the Jobs table stores information about the job. The only thing about the customer would be their ID stored in the job table. However, if you have multiple customers on a job then you would need what is called a JUNCTION table where you store the job ID and the Customer ID and then use a subform to add customers to jobs. Then you can have as many customers as needed on the job but again - the information ABOUT the customer is not stored a second time. It is only RETRIEVED with a QUERY which joins the two tables together based on JOB ID and then you can DISPLAY the customer information you need.

 

[Plumber]

Bob,
There are instances where the Customer may have several locations where jobs are performed.

 

[boblarson]

Bob,
There are instances where the Customer may have several locations where jobs are performed.

Okay, so are these always ONE TIME locations or locations that may be used again?

 

[Plumber]

With some Customers the customer address and job address are the same. Other Customers may have multiple job locations. For instance a Real Estate office may have a job performed at the office address or at several other rental properties. Where a job is performed at their office, I want to copy the office address to the job location. I hope I'm being clear enough in my explanation.

 

[Plumber]

The locations may be used more than once.