2 Tables, big nightmare!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Debba - 16 Mar 2006 03:48 GMT

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

Reply to this Message

Anand.V.V.N - 16 Mar 2006 04:30 GMT

Hi,

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

Anand

Signature

"Who will guard the guards?"

> 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

Reply to this Message

John Vinson - 16 Mar 2006 06:36 GMT

>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]

Reply to this Message

Debba - 16 Mar 2006 20:51 GMT

> >I have 2 tables. Table1 contains field 'Streetname' field 'town' and field
> >'postcode'.
[quoted text clipped - 18 lines]
>
> 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!

Reply to this Message

Debba - 16 Mar 2006 23:43 GMT

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

> > >I have 2 tables. Table1 contains field 'Streetname' field 'town' and field
> > >'postcode'.
[quoted text clipped - 21 lines]
> can't grasp the idea of relationships with tables etc, my brain gets 'fried'!
> its a matter of thinking simply and not too complex!

Reply to this Message

John Vinson - 17 Mar 2006 01:40 GMT

>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]

Reply to this Message