Reference One Table but save to another

[Help Wanted]

I have a list of addresses that i have saved in a table. I have made a form that allows me to enter a new customer and their address and then save it to that table. Each customer has a special number to identify them.

Now what i want is to be able to build a second table to save their orders in. This will have space for their order, order number, address and customer number.

The form i build to enter these with, is there a way i can make it so i can type in the customer code in a box and it will reference the addresses table and bring up the address for that code in box(es) underneath and then the other half of the form be for me to fill in their order and when i click save it saves the whole lot to the other table that holds the orders?

 

[Moniker]

Very easily you can, but you just asked if it's possible, and that answer is yes. However, you didn't tell us any problem/roadblock you're running into. Have you tried to do this? If so (and I hope you've at least tried it), what is the result? So long as the address table is related to the orders table through a primary key, then you can cascade the updates.

 

[Help Wanted]

My problem is i have had access for a day and just dont know where to start really or any good tutorials to show me how.

I spent my first day on the program learning how to make a table and a form so now i hope to move onto doing what i said and was just hoping for a nudge in the right direction if possible.

 

[namliam]

Use a subform...

If you are just starting out and want to be building a complete system I suggest you hire a pro... Unless you know stuff like Normalisation, database design and proper programming allready you are going to be running into so much problems that this will eventually turn out cheaper than creating this on the run and running into so much problems...

Access may be a low level system, but creating something from scratch is not as easy as it may seem.

 

[Moniker]

This may be a crash course, but what you need is normalization. It's good to start now since you have little experience. Basically, you want a structure like this:

Table: Customers

CustomerID   FName   LName   Addr1     Addr2   City        State   Zip
1            John    Smith   123 Oak           Someplace   ZZ      12345
2            Mary    Jane    456 Elm   #789    Otherplace  AA      23456

Table: Orders

OrderID   CustomerID   OrderDetailStuff
1         1            Personal Waxing Kit
2         1            Personal Wax Removal Kit
3         2            Laugh Track

What you want to do (and the way to think about any DB design) is to put everything into its simplest elements. You only want the data to be saved once, and then you reference it when you need it. In your initial description, it read like you were trying to store the address twice (once in each table). You do not want to do this as the second the address changes in one of the tables, it's wrong in the other table. This is what normalization helps you avoid.

If you have ever ordered stuff off of Amazon and sent it to friends/family, you'll notice that the website "remembers" each address you've sent to. That way, if you always send things to your parents, you don't have to type in their address everytime. They are doing that through normalization. Their DB probably has CustomerID and an AddressID in it (along with a ton of other things). In that way, you can handle multiple addresses for one person.

You're still new to DBs, so don't try to write something way out of your league as your first project. Do something simple, yet useful to you. The obvious example, and one used by many Access Books, is an address book. Think about everything that needs to be separated out but related to each other. For example, you'd probably have separate tables for Name, Address, and Phone. The reason for that is while someone has only one name, you may want to store multiple addresses (work and home), and multiple phone numbers (work, home, cell). However, you only want to store the name once and then link it through lookups and joins.

Table: Contacts

ContactID   FName   LName
1           John    Smith
2           Mary    Jane

Table: Address

AddressID   ContactID   Addr1     Addr2   City        State   Zip     AddrType
1           1           123 Oak           Somewhere   ZZ      12345   Home
2           1           234 Elm   #789    Elsewhere   AA      23456   Work
3           2           345 Fly           Otherplace  VV      34567   Home

Table: Phone

PhoneID   ContactID   PhoneNumber   PhoneType
1         1           1234567890    Home
2         1           2345678901    Work
3         1           3456789012    Cell
4         2           4567890123    Home
5         2           5678901234    Cell

You'd want lookups for AddrType and PhoneType as well as you never want to store the same words multiple times (1=Home, 2=Work, etc.).

Hopefully, this will point you in the right direction.

 

[Help Wanted]

Thanks for your help.