How to query data based on account no.

[NerdyOne]

I have 7 tables in my database. Table 1 is the "master" that lists all of our accounts. Tables 2-7 are those accounts divided up by zone. What we are trying to do is (for example) have table 2 look at table 1 and if the account numbers match then pull 3 fields that are unique to only table 1 and put them in table 2 (or a new table if need be).

So here is it is in a nutshell.

=========
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
Table 1: |Account_No|Prefix|House_No|Street|Suffix
FIELD1 FIELD2
Table 2: |Account_No|Whole Address|

New Table: |Account_No(from Table2 only)|Prefix|House_No|Street|Suffix

Is that clear as mud? I hope not, I'm a newbie and stumped at this. Thank you in advance for your help.

 

[fredk]

Do you seperate tables contain the same fields as table 1 (with the only exception being "zone") ? If so, why not just add a "zone" field to table 1 and eliminate the other tables?

HTH

Fred

 

[NerdyOne]

We figured it out. We just ran the query wizard and included the two tables. Access knew that they both shared the same field (Account_No) and did a join based on that. The results of the query were exactly what we needed!

 

[fredk]

Glad you got it - however, If the only purpose of the additional tables is to store the zone - you should change the format of table 1 to include the zone and then run an update query to update the zone from the old tables into table 1

Fred

 

[willir]

NerdyOne

I have to agree with Fred. The idea of using a relational database is to remove redundancies. Please consider reviewing the following link...

http://www.alphabetcitydataworks.com/fundamentals.htm

I have found that databases that are not "normalized" to at least to the third level (read link for the rules, and an explanation on why) may become a maintenance nightmare as the database gets larger.

A good design at the start goes a long way in saving you grief later one. For example, I am familiar with a database that was not normalized properly. The database works, but some of the queries are over 6,000 characters long! The users complain about the tediousness of the work, and the developer complains about how long it takes him to make the modifications required by management.

The idea of a table is to track only things unique to the table. For example, in comparing table 1 and two, the account number and the address are just about the same. Ditto for table 3.

Richard