Splitting Tables

[Murray]

Hi all
Currently I have inherited a Database that has all of the customer and
employees in one table. The Table has fields for company name address etc.
The problem is when a new customer employess is enter the whole details of
the company name address etc have to be re entered. I would have like to
create seperate tables to store the Customer names and addresses and then
link it to a table containing the customer employees. The table currently
contains about 10000 entries so retyping it all is not an option. Is there an
automatic way of splitting the tables, removed doubled entries and relink the
tables so that the employees of the company are still pointed to the right
company.
If I have not written this explaination very clearly please let me know and
I will give any additional information needed.
Thanks

 

[Jeff Boyce]

Murray

Yes. (the explanation of how is a bit more complex...<g>)

First make a backup copy of your .mdb file. Maybe two.

Next, before splitting, shut off your PC and use paper/pencil to sketch out
your data structure. The keywords here are "normalization" and "relational
design". If these terms are ... 'noise', plan to spend some time studying
up on them. (Hint: you are absolutely correct that there's no need to
repeat that addressing information -- that's how you'd do it if you were
limited to a spreadsheet.)

When you have your "entities" (things about which you want to keep data) and
"relationships" (how the things relate to each other) sketched out, turn the
PC back on and build your "permanent" data/table structure and set the
relationships.

Now, map how to get from here (what you have now) to there (the new
structure). Which fields in the old version map over to which fields in the
new version?

Finally, create as many queries as you need to extract the pieces from the
old version and either append or update that info to your new (permanent)
tables. You may need to embed additional (calculated) fields in your
queries to help get the proper fields/values to put in your new tables. You
may need to add ID#s to your current data/table to help coordinate related
pieces.

If you aren't scared off yet, good luck! Post back here with more specific
questions as you get into it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Murray]

Thanks for the quick reply.
I have already mapped out what I want and I created a make table query to
move the fields such as Company name, address etc to the new table. But now I
have to create relationship to link the tables together and that is where I
am stuck at the moment.

 

[John W. Vinson]

Thanks for the quick reply.
I have already mapped out what I want and I created a make table query to
move the fields such as Company name, address etc to the new table. But now I
have to create relationship to link the tables together and that is where I
am stuck at the moment.

Please post your current thinking about your new table design so that folks
might have a chance to give you relevant advice.

Do note that you can and will run Append queries to migrate your existing
"wide-flat" data into the new tables; you shouldn't need to retype much at
all.

 

[Murray]

The current table has fields:
ID, CompanyName, FirstName, LastName, Address, city, Type, ShippingAddress,
shipcity, phone, Mobile and a few others that are not so important at the
moment.
Type is to say weather the record is an employee, customer, supplier.
The new Table should have:
ID, CompanyName, Address, city, type
Another table (if needed) should hold the FirstName, Lastname, Mobile so if
I had a record of a company called "Acme" with employee "joe Bloggs" then I
would end up with "Acme" in one table with all the relevant fields and "Joe
Bloggs" in the other table with his relevant fields.
In the original table I could also have records like
Company - FirstName - Last Name
Acme - Joe - Bloggs
Acme - Harry - Held
Acme - John - Doe

The new table should have one record for Acme and the 3 names should be
linked to that company
Hope that helps with my explanation