A One to Many relationship

[exo]

I'm having trouble wrapping my head around the proper way to do this.

I have three tables.

One is a list of wells with some header data.



The second table is a list of chemicals used to treat the well.

The third table relates to the second table, and provides descriptions of each chemcal.

A single well often has multiple chemicals used.

So table 1 lists each well once. It has a primary key (PKey) for each well. (approx 100k records)

Table 2 has a PKey1 field which links to the primary key field in Table 1. There is a separate entry for each chemical used, which means there are duplicate PKey1 entries (one duplicate for each chemical). Each entry then has it's own primary key (PKey). (Approx 1 million records)

Table 3 links to Table 2. It has a field called PKey2 that links to the primary key in table 2. It is a list of descriptions for each chemical by the company who used worked the well (Approx 2 million records)

I can easily link the tables and filter by county to get a manageable table size. For example, in the county of interest there are only 604 wells. If I link the tables by their proper primary keys and run the query I end up with approx 22k entries. So, basically those 604 wells multiplied by the number of chemicals used across all of them.

I have been asked to provide something that is more along the lines of:
Well, Name, etc (Header Info from Table 1)
- Chemical 1 info (derived from Table2 and Table3)
- Chemical 2 info
- Chemical 3 info
Well2
- Chemical 1 info
- Chemical 2 info
- Chemical 3 info
Well3
- Chemical 1 info
- Chemical 2 info
- Chemical 3 info
- Chemical 4 info

I'm at a bit of a loss as to how to provide the data in that format. I have a feeling it's easier than I am making it out to be in my head.

[orange]

It seems in plain English you are in the "well treatment" business.
You have a number of Wells. And a number of Chemicals. Each Well is treated with 0,1 or many Chemicals.

Each table is about 1 subject only.
Well has info on Well only eg. unique identifer, name, location
Chemical has info specific to the Chemical eg. unique identifier, name, iupac name,....
WellTreatment is known as a junction table -used to resolve many to many relationships.
It has info related to Well and Chemical(s) Used for the Treatment

It seems to be a Many to Many relationship.
The table relationships appear to be as follows, if I have interpreted your post correctly.

Well---->WellTreatment<--Chemical
with data along these lines.

well 6 --->6 3<--------Chemical 3 (potassium nitrate)
well 6 --->6 9<--------Chemical 9 (Sodium Acetate)

[exo]

I work for an oil company and we acquired a large database of frac data.

Various frac jobs use various chemicals.

The goal is to get a list that shows each chemical used in each well, without the well itself being duplicated.

The reason I perceived it to be a one to many relationship is that it is 1 well to many chemicals. But maybe I'm looking at it wrong.

Either way, is there a way to join the data in a way that allows me to display the data in a way that is close to what the geologists her have requested?

[orange]

I'm reading your post as

You have many Wells and many Chemicals.
1 Well may have 1 or many Chemicals
1 Chemical can be used in 1 or Many Wells.

So the junction table example in my original post would show

eg. All chemicals used in Well # 6, and could also be used to find
which Wells use Chemical #29 for example.

see this for a free video.

Good luck. If I have misunderstood, then please give a sample of well, chemicals and desired query/report/output.

[exo]

I'll check out the video.

I don't see anything in your original post that suggests how to do this though.

I already have relationships defined and I can easily generate a table with all of the data.

The problem is it duplicates the well for every chemical in the well. My guys are asking for 1 entry for each well, with all relevant chemicals associated to it. Not a different row for each chemical/well combination.

I think you were simply providing me with the table relationship which I believe I already have defined.

[orange]

I see your set up as Many to Many. The junction table is how you resolve a Many to Many to 2 one to many relationships for use in database.
For presentation, you may want to put a number of values on the same line. See Allen Browne funcConcat

But remember data storage and structure is not the same as data presentation.

Your guys may want a fancy gui with blue, red and green stripes.
But you are designing :
a) the data base for proper structure and integrity, and
b) a report/form format to present the data to your guys.

Two separate, but often confused steps.

[ssanfu]

with what orange said 100%. You definitely have a Many-to-many relationship between the two tables. You need a junction table.

If you have trouble setting this up, post a copy of your dB with sample data. We can help you with the design.