Updating table from excel

[SamDemon]

I have an excel spreadsheet with the following column headers:
OperatorID, RegName, Australia, Canada, Caribbean, Central Africa, East Africa, Eastern Europe, Far East, Indian Ocean, Middle East, North Africa, South Africa, South America, South Asia, UK, US, West Africa and West Europe. Each operator has its own unique ID from 1 - 1000 and can have access to different geographical areas. For every geographical area they able to sell to there is a "1" in the cell.

Geographical areas are made up of countries which are in turn made up of airport codes. What I need is to be able to import into my access database all the airports that each operator is entitled to.

I have created the following tables in access but dont know the best way to get the information into them:

tblOperator (OperatorUID - PK, RegName)
tblOperatorRouteEntitlement (OperatorRouteEntitlementUID - PK, OperatorUID - FK, 3LC -FK)

tblAirport (3LC - PK, AirportName, CountryUID FK)
tblCountry (CountryUID - PK, Country, GeographicalAreaUID - FK)
tblGeographicalArea (GeographicalUID - PK, GeographicalArea)

As you can tell I've created a joining table for the many-many relationship between operator and airport code, but now i need to know how to get the information out of excel and into the database.

If someone could advise / help I would be very grateful!

Thanks
Sam

It's just common sense, shame sense isn't common!

 

[Remou]

If you can link to the Excel table, this should be easy enough (it is not terribly difficult even without a link). It is probably easiest to first create a normalised table, say:

SELECT OperatorID, RegName, "Australia" As Country 
FROM linkedExcelsheet WHERE Australia=1
UNION ALL
SELECT OperatorID, RegName, "Canada" As Country 
FROM linkedExcelsheet WHERE Canada=1
<...>
SELECT OperatorID, RegName, "West Europe" As Country 
FROM linkedExcelsheet WHERE [West Europe]=1

As you can see, it is now simple enough to use a append query with the saved Union query to update the relevant tables.

http://lessthandot.com