Linked tables, relationships

[fateyes]

Hi,

My contacts database has a couple of tables in it - and one company can have many contacts. I spit this information from an original flat (1 table database). Some of the companies kept all the contacts linked, while some kept the same separation. (i.e

Smith Ltd + Joe
Bob
Kevin
(correct)

Jones Ltd + John

Jones Ltd + Pete

Jones Ltd + Sam

(not correct!)


How do i rectify this?

Cheers for any help

P.S

And , I've just realised I have a direct relationship between the two tables (one to many). Should there be a linking table? I guess I will still have to rectify the problem even if i do put a linking table in.

 

[The_Doc_Man]

You've made a good start by splitting the tables.

You point out that one company can have many contacts. So you (correctly) state that you need a one (company) to many (contacts) relationship.

Your question about a linking table revolves around this question:

Are the contacts free-lancers who can work for more than one company at a time?

If YES, then you need a linking table between them. If NO, then you can get by with two tables, and the company name or ID becomes the basis for the relationship between person data and company data.

 

[fateyes]

Exactly as i thought - my problem remains the same however - on splitting some of the companies (as viewed on the datasheet) amalgamated their employees whereas most didnt (see above layout)

How do i get all the companies to reduce themselves if you see what i mean...

Thanks

 

[Pat Hartman]

Splitting a flat file into two relational tables is a multi-step process. You first need to create the 1-side table, then add the generated autonumber back to the flat file, then create the many-side table.

1. The first query needs to "Select Distinct" company related fields and append them to the new 1-side table.
2. Add a new long integer column to the flat file table to hold the new autonumber key of the 1-side table.
3. The next query is an update query that joins the 1-side table back to the flat file on ALL columns except the autonumber and updates the long integer column in the flat table with the autonumber from the 1-side table.
4. The final query selects all rows from the flat table but just the columns with the contact info plus the long integer field that will become the foreign key in the many-side table and appends the rows to the many-side table.

So, given this file
COMPID, Contact
comp1, John
comp1, Mary
comp2, Al
comp3, Jim
comp3, Alice

Step 1 creates the 1-side table
NewCompID, OldCompID
1, comp1
2, comp2
3, comp3

Step 3 puts the linking field back into the flat file
COMPID, Contact, NewCompID
comp1, John,1
comp1, Mary,1
comp2, Al,2
comp3, Jim,3
comp3, Alice,3

Step 4 creates the Many-side table
ContactID, Contact, NewCompID
1, John,1
2, Mary,1
3, Al,2
4, Jim,3
5, Alice,3

 

[fateyes]

ok, that makes some sense. However, what has happened is that i just used the split database feature, so i no longer have a flat database. I have the two related tables with all the info in them. Obviously a lot of the company info is replicated (see my previous example) - does your suggestion still work?

 

[Pat Hartman]

You need a table that was identical in structure to the original table. Join the two tables to rebuild your original or reimport the data.