How to Creat a Good Clean Dbase

[EverardoBalboa]

Good morning, I am new to Dbase Development.

Project:

I have a table with Oil/Gas Operators and every the facilities that they operate.  I wanted to create a Master Table with All contact information.  Then I wanted to create Tables for Chemicals that they use.  Everyone uses diffrent chemicals at different locations.  Sometimes they use multiple chemicals in a single facility.

Ideal Situation

One Table that with Link to Chemical TableI(s) up to 31 different chemicals.  Again all of them different.

Obstacles.  I have the master file and created 31 table each will capture chemicals, but it is only working with one record.  When I go to Records 2 and click on the Chemical table it wants to replace the existing data.  How can I get it to where master file has two records and the chemical tables to follow each record from the master file.  I am doing this in Acces which eventually will make a connection to ArcGIS.

Thank You

Eve

[AsrujitSengupta]

personal_geodatabase‌managing_data‌data management‌access‌

Managing Data‌

Just adding tags and mentioning a relevant group so that you can get a better response. if possible move this question to the Managing Data‌ place for a wider audience.

[EverardoBalboa]

How do I move it to Managing Data?

[AsrujitSengupta]

Moving Content

[EverardoBalboa]

Thank you.  Getting use to this.

[TedKowal]

I am only guessing what your are asking .....   You will have to add a third table to track what chemicals each facility needs.  Pictured below is a relationship that was made to allow multiple chemicals of the same type to multiple facilities.  I also attached a working database with an example query called ChemicalsNeededByFacilities.

Hope this helps,

Ted

[EverardoBalboa]

Thank you.  I will give it a try. 

[EverardoBalboa]

Thank you for the information.  I am barely getting to this project.  I have facilities that have several different chemicals and amounts on site.  The example you provided is to be use as one Chemical for facility Correct?  The thing I have facilities that range from one chemical to one facility having thirty on site.  How can I capture that and display it on a map without having to display 29 empty fields for the facility that only has one chemical.

[TedKowal]

I only sent a simple example on how this could be accomplished with MS Access.  In the example, each facility can have an unlimited amount of chemicals.  The database only restriction is that each site can only have one entry for that one particular chemical but can have many different chemicals

example

ChemicalFacilitiesNeed Table

Facility 1   -  chemicalA -- Quantity 5

Facility 1  -   chemicalB -- Quantity 1

Facility 1  -  chemical C  -- Quantity 10

....

Faciltiy 2 - chemicalA -- Quantity 100

Facility 2 - chemicalC -- Quantity 2

...

Facility 3 - chemicalC -- Quantity 3

...

The only restriction is that Facility 1 can only have one chemical of each type and the related quantities for that type

Facility 1 is identified in the MasterTable

chemical are identified in the chemical table

Does this make better sense?