Creating database from existing data

[arturju]

Hello,
I searched through the forums a bit and couldn't quite find what i was looking for so i decided to make a new post. Let me preface by saying that i'm a novice at Access and just picked it up a few months ago. With that said let me explain my problem:

Im working to create a database with an assortment of files that all have common entries but are all in different formats. For example, there is a Port of Loading column for each file I'm working with but each file has sometimes a slightly different spelling, "New York" or "NEW YORK, NY","New York, NY" to name a few. I figured if i have a parent-> child relationship between Port of Loading and the many different companies that have this column i could get some meaningful information out of the database ( like how many shipment each port has).

So my initial approach was to try to standardize all these entries to all have matching names and i could enforce referential integrity. At first i would try to do so via update queries but i found this a little time consuming so i switched to editing the entries before importing them to access using "Advanced Find and Replace Tool". This has worked ok so far with only a few kinks. My problem is now that as I keep adding new companies to the database I also need to add new Ports of Loading to the parent table. Is there an easy/quick way to check which Ports from the company i want to add are not yet on the parent table?

Please let me know of any comments and/or suggestions you may have to facilitate the making of the database. I still feel like my method of changing entries could be improved. Thanks in advance.

[ItsMe]

I would dedicate a table to Ports of Lading. There are international standards for naming ports. Maybe you can incorporate a standard that works for your business.
http://www.page-int.com/?p=pages/res...ign-port-codes

When you enter data into your DB, you can use a combobox and store the Primary Key in relative tables like Purchase Order, etc. Using controls like list boxes and combobox's helps to insure integrity of relations.

Then, when you look at a client's activity you can see a standardized description of the Port. The link I showed you has numbers that are unique to each port. I would still use an Autonumber field as the DB's primary key and have additional columns for the Name and Port Code.


Also, there should be paperwork that has a master or house bill that will describe the relevant port. Make sure you are using an authoritative source for data before you store it in your DB.

[June7]

Maybe you need a combobox that has a RowSource list of the ports.

[arturju]

I would dedicate a table to Ports of Lading. There are international standards for naming ports. Maybe you can incorporate a standard that works for your business.
http://www.page-int.com/?p=pages/res...ign-port-codes

When you enter data into your DB, you can use a combobox and store the Primary Key in relative tables like Purchase Order, etc. Using controls like list boxes and combobox's helps to insure integrity of relations.

Then, when you look at a client's activity you can see a standardized description of the Port. The link I showed you has numbers that are unique to each port. I would still use an Autonumber field as the DB's primary key and have additional columns for the Name and Port Code.


Also, there should be paperwork that has a master or house bill that will describe the relevant port. Make sure you are using an authoritative source for data before you store it in your DB.

Thank you for your suggestion. I had considered implementing a combobox in the future (but not to the extent you mentioned) however I have to make a database out of the files I have first. So far it's been tedious and repetitive so i was hoping someone had a better way to do it.

[ItsMe]

There is an old cliché, "Junk in junk out".

Control what users type into a table by using forms with combos and listboxes.

You might be able to run queries with the LIKE operator and wildcards to clean the data. But avoiding best practices to manage constraints is not a solution, in my opinion.