Nasty Many-Many Join

[ekeithly]

I have a many-to-many join that is wreaking havoc on my database. I have found work arounds to get it to do what I want it to do, but the the truth is everytime I work with it makes fixing things way too cumbersome. I will be heading out of the office today, and thought maybe I could get some suggestions to look through when I got back next week.

Here is the basic design and purpose of the database. I am trying to track the affect of pumping chemicals and fluids into a well on its ability to produce. I have a table that tracks the production of the well for each month for both oil and gas.

TblProduction

ProductionID PK
API - This is a well identification value
ProductionMonth
Oil
Gas

I also have a table that tracks the different stages at which chemicals were out into the well bore. The process is done at stages which occurs at different depths, and I want to monitor what chemicals go in at different depths. However, this usually occurs in all in one month, maybe it might happen at a latter month.

TblFracStages
FracStageID


FracAPI
FracDate - I converted this date to correlate with the ProductionMonth so that they both are on the first of the month
DepthInterval
TotalVolume
TotalVolumeUnits

By combining API/FracAPI and Frac/Production I created a FK to relate both tables. I then queried the FracStageID and ProductionID to get a join query (with a join showing all results from TblProduction). The result ends being a table filled with ProductionID with mostly blank FracStagesID column with the proper columns filled with proper FracStagesID. The problem is the rest of my relates depend on this many-to-many join, and creates a train of many to many joins throughout the database which is a headache. Orginally I was leaving these joins as queries, but soon converted these to join tables because of the nightmare of trying to query with ambigous joins.

Here are the rest of my tables:

TblWellID
WellID
API
WellName
WellNo
Lat
Long

TblChemicalAmounts
ChemicalAmountID
ChemicalAmountAPI
ChemicalAmountDepthInterval (Both API and Interval where combined to relate with with FracStages API and interval to then substituted with the FracStagesID)
ChemicalVolume
ChemicalVolumeUnits
ChemicalID

TblChemical
ChemicalID
ChemicalName
ChemicalType

The goal is to create a query/spreadsheet where I see every production month and the oil and gas produced, but when a frac job was completed I see the stages completed and the chemicals used at each stage. Thank you in advance for any help/suggestions/advice you can offer.

[orange]

I responded to a many to many to many request just recently. It may be useful to you.

https://www.accessforums.net/access/...any-30865.html

Good luck with your project

[ekeithly]

Thank You.

[ekeithly]

After looking over the material, this still doesn't solve the issues I am having with ambigous outer joins. I must show all production data whether it has frac data or not. The join fixes the many to many, but I cannot view all producing months with chemicals showing for the related date.

[orange]

Please post a jpg of your tables and relationships, and show some sample data. One example of what data you must report/select but can not currently.

You should drop the spreadsheet terminology if you are going to proceed with database (Access).
Spreadsheet in the forums = Excel, and it is quite different than database.

If you can post a copy of your database with no personal info, but enough data to show the issue, I'm sure someone will respond.