Creating two tables and having them get info from each other. (Noob Question)

[plaguepoop]

Hello everyone,

I am very new to access and I am trying to create two tables called Customers and Orders.

The fields in Customers are:
CustomerID (Primary Key), First Name, Last Name, Address.



The fields in Orders are:
Order# (Primary Key), CustomerID, Product Purchased, Address.

There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.

The problem I am having now is with the Address field.


GOAL:
What I am trying to do is have the Orders table automatically get the Address from the Customers Table.

If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.

How do I accomplish this? If this is explained in a tutorial, I would be happy to read through it. I just dont know what to search for at this point.

[recyan]

Not the answer, just a few questions :
Why do you want to store the address in the Orders table?
Is there a chance that, at times, the Order address might be different from the one stored in the Customer table ?

whenever I create a new Order, I want to enter the customerID

Usually, an Order is created against a Customer.

Thanks

[plaguepoop]

Not the answer, just a few questions :
Why do you want to store the address in the Orders table?
Is there a chance that, at times, the Order address might be different from the one stored in the Customer table ?


Usually, an Order is created against a Customer.

Thanks

When I say "I want to enter the CustomerID into the Order table," what I mean is that I want the Address associated with the CustomerID i input to appear in the orders address field.

I dont want the address to be stored in the orders table, I would like for all of the customer information to be in the customer table.,

However, I would like for it to also appear under the order table.

The address from the Order address and Customer address will always be the same.

When you say an order is created against the customer, this is how I want it to be.

Usually, do the two tables have nothing but the CustomerID in common? Would a form or report be created to show all of the necessary information? (Again, Im very new to Access)





In other words, I want the address field in the orders to "autofill" based on the information from the Customers table.

[recyan]

Usually, do the two tables have nothing but the CustomerID in common?

Yes

Would a form or report be created to show all of the necessary information?

Yes

In other words, I want the address field in the orders to "autofill" based on the information from the Customers table.

Yes, that's the way it's done, but it should be done in the Form or Report. In case, you have a possibility that the Customer's address might change & you want to record the address used for a particular Order, then perhaps, storing the address in the Orders table or having another table CustomerAddresses & storing the CustomerAddressID in the Orders table can be looked in to.

Thanks

[plaguepoop]

Alright thanks for the info! You answered a lot of my questions

[recyan]

Glad to be of help.

Thanks