Complex Left Outer Join Query

[Randomguy]

Hello everyone, self-taught access for a couple months now, and I'm having trouble trying to figure out how to set up a query. In the end, it should look exactly like the attached image if someone chooses the product FSC 1957. There are 5 "buckets" the user can choose from. Product, IF IP, AMS IP, Flex IP, and Features. These are all displayed on an unbound form for the user to choose. The Product bucket is a combobox bound to the Product Table and the rest of the buckets are displayed as subforms in datasheet view with each Source Object as the corresponding table. All of these tables are in a many-to-many relationship with the others (Product to IF IP, Product to AMS IP, Product to Flex IP, Product to Features, IF IP to AMS IP and so on and so forth). I have junction tables set up for each many-to-many relationship and everything works perfectly. What I want my query to do is to show why each item was selected (whether it was directly related to a product, or if it was related to an AMS IP which came from an IF IP which is directly related to the product). In a sense there is a bit of hierarchy, but a very messy one because the data being put into it is still in development so certain items are kind of floating, but everything does have a relationship to something. I know outer joins are needed and I believe I will have ambiguous outer joins if this was to be set up in one go. Where I'm struggling is to figure out how to actually set it up to have it display like the attached image. I'm under the impression that to do this, I will need a lot of queries. In the sample image, FSC 1957 has IF IP 1 under it, under IF IP 1 is AMS IP 1.0, under AMS IP 1.0 is LDO_1, and LDO_1 has no features under it. AMS IP 1.0 has Feature A under it with no associated Flex IP, IF IP 1 has Feature D under it with no associated Flex or AMS IP, then FSC 1957 has AMS 2.1 under it with no associated IF IP, and under AMS 2.1 is BC1.2_1 Flex IP, and Feature C with no Flex IP.

Let me know if I need to elaborate on something. Seeing as there is a lot of info in words, I'm sure it will be quite confusing and I probably screwed up terminology. Let's hope not! I also would prefer to not use SQL, everything has been done in QBE.

Thanks in advance!


The tables for each "bucket" are:

Product:
Product (PK)
Char Plan (hyperlink field)

IF IP:
IF IP (PK)
Include (Yes/No field)
Best Practices (hyperlink field)

AMS IP:
AMS IP (PK)
Include (Yes/No field)
Best Practices (hyperlink field)

Flex IP:
Flex IP (PK)
Include (Yes/No field)
Best Practices (hyperlink field)

Features:
Features (PK)
Include (Yes/No field)
Best Practices (hyperlink field)

 

[Mihail]

If will post the junctions tables structures or, better, the database, maybe we can help you.

 

[Randomguy]

Thank you for the reply!

The relationships are shown in the attached image, and there are 10 junction tables:

Product-IF_Relationship_Table
Product-AMS_Relationship_Table
Product-Flex_Relationship_Table
Product-Feature_Relationship_Table

IF-AMS_Relationship_Table
IF-Flex_Relationship_Table
IF-Feature_Relationship_Table

AMS-Flex_Relationship_Table
AMS-Feature_Relationship_Table

Flex-Feature_Relationship_Table

The structure of the junction table: the primary key of the first table and the primary key of the second table are dual primary keys of the junction table which you can see in the attached image. I have circled the 5 "buckets" and the rest are just the junction tables. Sorry for the mess, but it's actually one of the neater ways I could think to do it.

 

[Mihail]

I think that you have some cycles in the relationships.
Upload the database.

 

[Randomguy]

I'm not sure if I'm allowed to post it due to company policy, I will try to find out. In the meantime...

The trouble I'm having with a query is that all the fields except the product will need to come from multiple tables to show the correct data. In the attached image in my OP, Feature A (Feature field, 2nd record down) would need to come from the Feature field of the AMS-Feature relationship table, but in the next record, Feature D would need to come from the Feature field of the IF-Feature relationship table. Does that help at all or did I just make it worse?

 

[Mihail]

I'm not sure if I'm allowed to post it due to company policy

Due to company police maybe you are not allowed to show us the records with sensible data. And is no need.
So, keep only few records, replace the real data with false but realistic data and upload the database.
I think that the database itself (the structure) can't be a secret .

 

[Randomguy]

Sorry for the delay, but we finally got the okay. Here's the database with all the relevant info (I hope). All the tables are there that would be needed in the query (ideally). The user could select a reference product (or start at any "bucket"), click auto fill, then maybe make a few edits to the options selected (mostly check something that was unchecked) and then press continue. When this happens, I'd want a query in the background that would show why each item got selected ( if it came from an AMS IP, or IF IP, or maybe just linked directly to the product, etc.) For now, the report that opens when continue is clicked is fine, and don't worry about the 'Tests" bucket on the main form. Thanks in advance, I appreciate it!

 

[boblarson]

forth). I have junction tables set up for each many-to-many relationship and everything works perfectly.

Actually your structure needs fixing. One way you can tell that you likely have a problem is if your relationships form a circle (what Mihail referred to as cycle). So, I think we need to start over a bit and kind of get an explanation of what you are trying to do with the database and the business logic.

From your initial descripton you have a table for each product which should not be. They should all be in the same table as records. Where there are differences amongst products, you can use a structure similar to my sample here:
http://downloads.btabdevelopment.com/Samples/misc/SAMPLE-StoreDiffData.zip

which allows for maximum flexibility while maintaining a normalized structure.

 

[Randomguy]

Well there isn't a single table for a single product, there is a table for each "bucket" (Product, IF IP, AMS IP, Flex IP, and Features) So you are suggesting that I have each of those buckets all in one table?

 

[boblarson]

Well there isn't a single table for a single product, there is a table for each "bucket" (Product, IF IP, AMS IP, Flex IP, and Features) So you are suggesting that I have each of those buckets all in one table?

I'm suggesting that you give us more information about what it is you are tracking and your business processes are so we can help you determine that. But it sounds like it based on your initial question and cursory look at the relationships.

 

[michaeljryan78]

I agree with boblarson. We need some details about the process and what you are tracking. Looking that the AMS_IP_Table table the data in the IP Iniside AMS IP violates the first normal form. To be in 1NF, each column must contain only a single value and each row must contain the same columns. There are multiple data values in a single field. How can that be queried? The data needs to be split an normalized.

 

[Randomguy]

The columns that say "IP Inside" are just placeholders so the user can see what is directly related to that IP they are looking at for now.

The user will be testing a new product that will most likely be similar to a product in the database (if not, they make it from scratch using the IP in the database). When they choose the reference product and press auto-fill, it will show all the IP that was inside of that product, therefore all the IP they might need to test (depending on how similar the product is). The form works exactly as it should. However, the users agreed that there would be value in being able to see how each piece got there.

For instance, Feature A could be directly related to multiple levels such as AMS IP 1.0 and a specific product, so how do we show that? Perhaps this new product being tested isn't like any other product, but has Feature A inside along with AMS IP 1.0. (Feature A shows up in two places) The user wants to know how Feature A showed up twice, and not just see that it shows up twice. Almost like a flow chart as to how everything came to be selected. The reason there are so many tables is because each product could have many IF IPs, many AMS IPs, many Flex IPs, many Features, and most likely a combination of all of them. And each of those IPs and Features could show up in multiple products. IF IP's could have many AMS IPs, many Flex IPs, and many Features. They are all many-to-many relationships with each other. Does this help at all?

 

[boblarson]

I don't have enough time to go into great detail at the moment nor try to fully analyze what you have and where you need to go. But this data model is similar (a Bill of Materials where something can be part of a larger product)
http://www.databaseanswers.org/data_models/bom/index.htm

 

[Randomguy]

Thank you, I'll look into it!

 

[michaeljryan78]

The columns that say "IP Inside" are just placeholders so the user can see what is directly related to that IP they are looking at for now.

In a normalized database this can be accomplished in a listbox that is requeried.

So everything start at the product. Lets start there. Lets start to redefine the data a bit, how about "Charactoristics"? So we have a charactoristics table. THe Charactoristics of the Product are FLEX, AMS, IF... Take a look I hope that I am on to something...

 

[Randomguy]

Okay, I see. Now what about next level down, IF IP. Those each will have their own characteristics too along with AMS IP, and Flex IP. Sorry if this seems basic

 

[michaeljryan78]

With that in mind you can have the charatoristics table that houses what the detail is about (project, AMS, IP) and the description, and still ink that to the Project table.

CharID (PK)
ProecjtID (FK)
CharTYpe (Project, AMS, IP, IF)
Char Description

 

[Randomguy]

I'm not quite following sorry . Do you mean a characteristics table for each level? It would be nice if the user could look up what is within a piece of IP if they aren't sure of what it is or have never used it before.