How to import Excel sheet to Access tables and respect the relationships

[inepu]

I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that when I import the sheet, the proper tables are filled correctly.

For example, the excel sheet is in the format

clientID Name phone City Country

In acces, I have a "Cities" table and a "Coutries" table, with the fields (e.g. for cities)
ID (primary key)
City (non duplicable)

The Clientes table is something like
ClientID Name phone cityId CountryId

and I would like the Clients, Cities and Coutries tables to be filled correctly, with the Clients' cityID and CountryID pointing to the right fields

I've followed this topic http://www.thescripts.com/forum/thread190306.html but I can't get it to work. In the end, when I try to execute the query, i get "You have chosen to add 0 rows etc etc" and It doesn't add anything to the db.


Thanks in advance

[nico5038]

I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that when I import the sheet, the proper tables are filled correctly.

For example, the excel sheet is in the format

clientID Name phone City Country

In acces, I have a "Cities" table and a "Coutries" table, with the fields (e.g. for cities)
ID (primary key)
City (non duplicable)

The Clientes table is something like
ClientID Name phone cityId CountryId

and I would like the Clients, Cities and Coutries tables to be filled correctly, with the Clients' cityID and CountryID pointing to the right fields

I've followed this topic http://www.thescripts.com/forum/thread190306.html but I can't get it to work. In the end, when I try to execute the query, i get "You have chosen to add 0 rows etc etc" and It doesn't add anything to the db.


Thanks in advance

When importing Excel data into Access, the easy way is to link a table to the excel sheet.
Next use Maketable or Append queries to fill your tables.
Using an ID for City and Country will make your imports harder, I would just use a table with "City" and "Country".
But when you want such an ID then first use an append query to add the distinct cities like:

select into tblCity (City) select distinct city from tblExcel
 

Next create a query that joins the tblExcel by the City and use the CityID field instead of the city. Now you can use that query to append the tblClients.
(Ofcourse the same mechanism can be used for the Countries).

Getting the idea ?

Nic;o)

[inepu]

hi Nic, thanks for the reply

I'm a bit rusty on my relational DBs, so I'm a little confused about not using the cities/coutries table with and ID field.

If my Clients table has several clients from the same city or country, won't that mean that my DB will have several duplicate strings? I was using the ID field as a pointer (thinking that it's better to have a duplicate integer than a duplicate string, and easier for the database to index and search=

Thanks in advance

[nico5038]

About "If my Clients table has several clients from the same city or country, won't that mean that my DB will have several duplicate strings?", you're correct.
We use a tblCity to have control over the cities that can be entered and to follow the normalization rules. These don't describe however that you need to use an ID field instead of the city as a ForeignKey (FK). I often use just the City as the sole field in a separate city table.
Thus the size of the foreignkey is indeed a bit larger but I save the additional space needed in the tblCity and the hassle of maintaining the ID.
The advantage of an additional tblCity is the fact that when referential integrety is active you can change the name of a citi in tblCity and that the FK will show the corrected name in all related tables.
Under the hood Access will use an internal index to make sure that the correct cityname is displayed for a FK. So basically Access builds such an index (ID) field already.

Getting the idea ?

Nic;o)