Multiple relates to one table and key

[ekeithly]

I am fairly new to Access and I have a problem where I have one table that uses one key to relate to 5-6 other tables. I am working with well data and I am trying to capture in the database all the chemicals and fluids pumped into it. In one table I have my well identification that includes name, number, and location. In another table I have the different stages in which a well job was completed. Each stage occurs at a different depth, so one well may have any number of stages completed (1-m). My problem occurs when it comes to tracking all the fluids that get pumped into the well at each stage. Any number of chemicals (sand, acid, gas, etc.) get pumped into the well at different concentrations, volumes, and particle sizes. Sometimes the chemicals run are for multiple purposes. An acid might be run early in the process to clear extra cement, but that same acid might be run to apply pressure to fracture the rock (or another chemical type altogether). My current organization has a table for each purpose linked back to the stage it occured on, but this usually results in a query error. My new idea is to create a table for each chemical type with a column tracking the intended use of chemical. Here is the current organization of database. I appreciate any help in advance.

TblFracStage
FracID
WellID - I am using a combo of WellID and Depth to create my relates
Depth
Stage#


Geologic formation

TblAcid
AcidID
WellID
Depth
AcidName
Volume
Concentration

TblGas
GasID
WellID
Depth
Gas
Amount(mcf)
Quality

TblFracFluid
FracFluidID
WellID
Depth
Chemical
Concentration
Volume

TblPrePadFluid
PrePadFluid
WellID
Depth
Chemical
Amount
Concentration

TblFlushFluid
""ID
WellID
Depth
Name
Concentration

TblProppant
''"ID
WellID
Depth
Name
MeshSize
PropQuantatity
PropQuality

[ekeithly]

Let me clarify real quick that while there is concentration in mutliple tables sometimes the fluid is not an acid, it may actually be a gel or water treatment.

[ssanfu]

Looking at your proposed structure, I would say it is not normalized. Designing a table structure is an art and a science....... I still struggle with it.
With the exception of the table "TblFracStage", all of your tables are about fluids. So they should be in one table.

These are the tables as I see them. It is just a start:
For the well info:

tblWells
--------
WellID_PK      autonumber
WellName       text
Well_Location  text?
other well info

And for the fluids:

tblFluid
-----------
FluidID_PK         autonumber
FluidName           text   (ex:Acid name, Frac Fluid name, Gas, Flush Fluid name, drilling mud, etc)
Fluidtype            text (or long if fluid types in a table)
Concentration      single
ConcentrationUnits text (or linked) (ex:ppm, MMP,etc)
'other fluid data (MSDS number, stock number?)

To tie them together:

tblFracStage  (or maybe tblWellTreatment)
-------------
FracID_PK           autonumber
WellID_FK            long
Depth                 single
Stage_Number         integer
Geologic formation  text(?)
Amount                single    (of fluid for this record)
Amount_Units          text or linked to table (gal,bbl,lbs,percent,etc)
FluidID_FK            long (which fluid)
other data about this job.....

My $0.02 .....
Hopefully, one of the members that is good at structure design will jump in and help.

[ekeithly]

Thanks. Normalization is something I understand when I read about it, but I struggle to apply it to my own data. I think this stems from the fact that I already have vast amounts of data that I am trying to import. My question is how do I deal with the fact that each chemical type may have multiple measurements based upon what it is? So acid may have amount poured and concentration, but proppant may have grain size and and max quality. The FluidID_Key also brings me to another question. I have been storing a single row for each chemical run even if its the same chemical over and over again because the quantaties may change. Should I have a chemical table and a chemical amount table? As such:


TblChemical
ChemicalID
Name
Type


TblChemcialAmount
ChemicalAmountID
ChemicalID (FK)
Amount
Units
Concentration
Etc.

I think this gives me enough to work with for a bit and see if I can't figure something out. Thanks!

[ssanfu]

but I struggle to apply it to my own data

Same here. Art and science..... I struggle with the art part. (and sometimes with the science part)

Should I have a chemical table and a chemical amount table?

That sounds logical.

each chemical type may have multiple measurements based upon what it is? So acid may have amount poured and concentration, but proppant may have grain size and and max quality

Another way is to have a "concentration" table> In your main table (tblFracStage) you could select the chemical, then select the concentration. Don't know your requirements or data, so these are broad suggestions. (kinda like a taillight guarantee )


I usuallly start out with pencil and paper (or whiteboard) and layout all of the fields. them try to group them into tables. After I create the table structure, I use the Table Analyzer wizard to see how I did. I don't blindly accept the wizard's recommendations, but it helps me to see other arrangements.

--------
Be aware that "Name" and "Type" are reserved words in Access and shouldn't be used as object names. That is why I usually try to put the table name as the prefix. Plus, if you have a lot of tables with a field "Name", it get confusing - which "Name" field are you referring to?
For a list if reserved words, see: http://allenbrowne.com/AppIssueBadWord.html
(Also, don't use spaces in names)

[ekeithly]

I want to thank you for your help. After working with your suggestions I think I have at least come up with a proto-type for by databse.

[burrina]

FYI Be sure and NOT name any of your table fields the same as it will come back and bite you later on with your queries. No spaces, no hyphens or underlines, etc... Keep it simple. Naming conventions is a thing people use their own way but generally they keep it organized.
tblsomething
frmsomething
qrysomething
rptsomething

As examples. When your ready, maybe post a diagram of your relationships so someone can help. Good Luck with your project.