Splitting a table in Access

Question

I imported an excel spreadsheet with many records made up of the following notable fields:

• First Name
• Last Name
• House Number
• Street
• City

I would like to split this table into a table with just names, and a child table with address information as there is a 1:many relationship of people to address.

Can someone suggest how I might best proceed?

Answer 1

Import the Excel worksheet into a temporary holding table, and then create the following empty tables:

Contacts

….ContactID (autonumber PK)

….FirstName

….LastName

Addresses

….AddressID (autonumber PK)

….Street

….CityID (FK)

….ContactID  (FK)

Cities

….CityID (autonumber PK)

….City

Then execute the following 'append' queries:

INSERT INTO Contacts(FirstName, LastName)

SELECT DISTINCT [First Name], [Last Name]

FROM [HoldingTable];

INSERT INTO Cities(City)

SELECT DISTINCT City

FROM [Holding Table];

INSERT INTO Addresses(Street, CityID, ContactID)

SELECT DISTINCT Street, CityID, ContactID)

FROM ([Holding Table] INNER JOIN Contacts

ON [Holding Table].[First Name] = Contacts.FirstName

AND [Holding Table].[Last Name] = Contacts.LastName)

INNER JOIN Cities ON [Holding Table].City = Cities.City;

The above makes the following assumptions:

1.  Each contact can be distinctly identified by their first and last name.  This might be a risky assumption.

2.  Each contact can have one or more addresses, but each address can be occupied by one contact only, i.e. the relationship type between contacts and addresses is one-to-many as you said.

3.  Each city can be identified distinctly by its City name.  Again this might be a risky assumption.

Note that in the final Access tables I have removed the spaces from the imported column names like First Name.  It is not good practice to include spaces or other special characters in object names.  Use either CamelCase as I've done, or represent a space by an underscore character, e.g. First_Name.  I've assumed that the original column names with the spaces have been retained in the temporary holding table.

Note also that I have included a Cities table.  Cities are a separate entity type and therefore should modelled by a separate table.

You'll find an example of how to import and decompose data like this in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file imports a simple Excel worksheet into Access and executes the queries to decompose it into the predefined related tables, with a brief explanation of the query at each step of the operation.

Answer 2

Hi Ken.  Reading your reply more attentively, I returned to my question and noticed I had made an error when phrasing my question, in that in fact the relationship is 1:many for address to people, not the other way around.  In other words, there are sometimes many people that live at the 1 address. 

   I look forward to checking out your shared drive files. Thanks muchly.

Answer 3

Thanks a bunch for that, Ken.  Much appreciated by this newbie, both because of the timeliness and quality of the reply.  I will study it carefully and see where it takes me.

Answer 4

Hi,

I think you can do it just with queries to append records to news tblName and tblAddress

tblNames (structure)

Id     (autonumber)

FirstName

LastName

then create a new append query from original table groupping FirstName and LastName into tblNames.

With a new append Query, where join original table and tblNames table, select  tblNames.Id (from tblNames to IdName), HouseNumber, Street and City from original table.

tblAddress ****(structure)

Id    (autonumber)

IdName (numeric)

HouseNumber

Street

City

Ciao Mimmo

Answer 5

Are there currently multiple entries (addresses) for the same person?

Have you tried creating an Append query to push the Address info into your Address table?  This assumes you have a proper primary key defined in your Access tables to relate the data between the 2 tables with.