3 tables linked to single Postcode table - setup probs

[hozzaone]

Hi all,

(first posting from access newb)

I started with an excel sheet containing 3 main data groups:
1. client info
2. insurer info
3. employer info.
There is address, locality, postcode, state for each

I imported the whole lot into access and created a separate table for each group, but I was duplicating a lot of the address detail - clients in the same town etc.

ie.
client/town/postcode
bill/melbourne/3001
tom/melbourne/3001
and
employer/town/postcode
etc. - Every record in 3 tables had a town entry!

I separated the postcode, state and locality fields into separate tables (one for each data group), but I still now have 3 mostly duplicated tables.

Client/fk_LocalityID
bill/1
tom/1
pk_LocalityID/town/postcode
1/melbourne/3001
2/sydney/2000
-this format is duplicated for the three main groups. so I have 3 "locality" tables, with some duplicate and some unique entries.

how can I consolidate the data into a single table and preserve the unique keyed relationships to the 3 tables?

I tried creating a new Locality table with all the keys (and LocalityID as the new primary key:
pk_LocalityID/clientlocalityID/employerlocalityID/town/postcode
1/1/null/melbourne/3001
2/4/7/sydney/2000

what used to be my primary keys are now just fields (eg. clientlocalityID). I cant see how to convert my 3 tables to all look at the new pk_localityID so as to draw from one data source.

Any pointers would be appreciated, on either the problem, or even the posting!
Hope this was clear enough.

Thanks for any help.

Matt.

 

[jason246day]

Have a main table that contains all of the common fields from your 3 tables. Then have 3 more tables that will have the unique information for each of the three categories. This way, each person will have an entry in the main table, and be in only one of the subtables. This type of a relationship is called an ISA. It gets this name from "A person is a client, or a person is a insurer, or a person is an employer.

You can link them all together by have each entry in you main form have a Primary Key of "personID". Then each of the three tables will have an attritbute called personID as well, which will be your foreign key. Thus, linking your tables together.

 

[hozzaone]

jason,
thanks for the tips re: table structure.

my main problem though, is that (and maybe I didn't make it clear enough) I have 3 separate primary keys linking the "client", "employer" and "insurer" tables to their own "locality" tables (which means 3 similar and partially duplicated tables)

my need is to convert each of the primary locality keys to a new single primary locality key. As I have continued to search on this, I think I need a query that checks my current key against the new key, and then updates it to the new key, so my data is still related correctly, but it is all using one key, from one table.
then I can remove the redundant tables.

I tried an update query to do this, but it errors telling me the records cant be updated due to key violations.

I suspect I cant run the query unless I remove the primary keys?

 

[willir]

hozzaone

I am not sure if this is what you want. And the number of records will be a factor. I had to merge contact information from five or six different databases. It was messy since the designs were very different. And there were duplicates.

I know you can run a nifty UNION statment which may work. But for me, I found a down and dirty append query for each database allowed me to merge the data into the correct format. I was then able to massage the date to remove the duplicates in the actual table.

Once past this stage, I was able to take the work table to create the final normalized database. I used code for this part. Code was better than SQL for this part because it allowed me to update four or five tables at once while walking through the records.

Richard