2 Tables, big nightmare!

[Debba]

I have 2 tables. Table1 contains field 'Streetname' field 'town' and field
'postcode'.
Table2 contains customer details including addresses. How can I use the
street and postcode table1, to populate the address details in the customer
table2? I have a total mind block! thanks for help
Deb

 

[Anand.V.V.N]

Hi,

Do you have any common fields between the two tables that can link them?
Like customerid?

Anand

 

[John Vinson]

I have 2 tables. Table1 contains field 'Streetname' field 'town' and field
'postcode'.
Table2 contains customer details including addresses. How can I use the
street and postcode table1, to populate the address details in the customer
table2? I have a total mind block! thanks for help
Deb

Umm...

You don't.

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".
Information should be entered and stored once, and only once.

If the postcode uniquely identifies the streetname and town, then your
customer table should contain ONLY that customer's postcode. You would
create a Query joining the two tables, joining on postcode; pick the
customer information from the customer table, and the street and town
from Table1.

John W. Vinson[MVP]

 

[Debba]

Umm...

You don't.

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".
Information should be entered and stored once, and only once.

If the postcode uniquely identifies the streetname and town, then your
customer table should contain ONLY that customer's postcode. You would
create a Query joining the two tables, joining on postcode; pick the
customer information from the customer table, and the street and town
from Table1.

John W. Vinson[MVP]

thanks alot John. I have made databases for various things before, still
can't grasp the idea of relationships with tables etc, my brain gets 'fried'!
its a matter of thinking simply and not too complex!

 

[Debba]

Ok I have added fields to a query like you said to, but when i create a form
from the query, i cant seem to choose from the list of street names in
table1. i dont think i have completed the query accurately? when you say
joining on postcode, how do i do that? thanks

 

[John Vinson]

Ok I have added fields to a query like you said to, but when i create a form
from the query, i cant seem to choose from the list of street names in
table1. i dont think i have completed the query accurately? when you say
joining on postcode, how do i do that? thanks

You would not base your Form on the Query (usually, sometimes you
can).

Instead, you would base your Form on the customer table. You could
have a Combo Box bound to the Postcode field, but showing the town and
the streetname as well (I don't know what information you have
available when you're entering data so I don't know what the combo
should show).

For a Report you would create a Query by adding the Customer table and
the Postcodes table to the query grid; join the Postcode field in the
Customer table to the Postcode field in the Postcodes table. Select
the other customer information from the Customer table, and the town
and the streetname from the Postcodes table.

This does imply that, given a postcode, you can uniquely identify that
postcode's streetname and town - that is NOT the case in the US for
five-digit Zip codes but it may be in your region.


John W. Vinson[MVP]