Query Design Troubles

[Just_Some_Guy]

Hey everyone, I'm trying to design a query but I can't quite figure out how to go about doing it. I know I'll need to use outer joins, and I've seen how to get around the "ambiguous outer joins" message, but I'm still stuck. I think what's tripping me up is the fact that I want to pull the same field from different queries. The photo below shows how I would like the query to come out.





FSC 1957 has IF IP 1 under it, along with AMS IP 2.1. Those two are directly related to FSC 1957. LDO_1 is related to AMS IP 1.0 which is related to IF IP 1. Feature D comes from under IF IP1. BC 1.2_1 is under to AMS IP 2.1, and Feature C is under AMS IP 2.1 Basically if you follow from right to left you can see how each record is related to the product. It's sort of a hierarchy mixed with a relational database I think. Anyway, as you can see AMS IP 1.0 comes from IF IP 1 and AMS IP 2.1 comes from the Product itself. This happens again with the feature field: Feature A in the second record comes from AMS IP 1.0 whereas Feature D in the next record below it comes from IF IP 1. How should I go about setting up my queries and joins to make this work?

Let me know if I need to elaborate more on anything.

Thanks in advance!

[offie]

My god that was so hard to read. I'm not quite sure I understand how you data is set up with all these relationships you're talking about, but it seems that you have entered your data in a wrong format? Again, not sure. Maybe show what your tables look like, and a screenshot of the relatioships you're using in the query you want.

[Just_Some_Guy]

Thanks, there are 5 tables: Product, IF IP, AMS IP, Flex IP, and Features. The primary key is just a field with the name of the table. Each one has an Include (Yes/No) field that the user will select from a form. The rest of the fields are irrelevant. All the tables are in a many to many relationship with the other tables, giving me 10 junction tables. The junction tables just have the primary key from each table participating in the join, and I use those both as primary keys in the junction table. Below are the relationships I have and I've circled the 5 tables, the rest are just junction tables. I'd like for the user to select whatever options they want, then a query that will show exactly how a certain item got there; whether it was inside an AMS IP or came directly from the product. Does this make more sense?

[offie]

Alright so my advice is to make a query, with only the tables that you NEED to have the relationships, delete the existing relationships (if you do this is a query it won't affect the the overall relationships), and work from left to right with what you need to get.

So I think:
Start with product table, whatever table you need to connect it to IF IP, IF IP, so on until Features. It looks like you want to use left joing (double click on a relationship line and choose option 2), this way the query allows there to be blank fields. With your data, I think, you won't want to make a line, but start with product and then have differect left joins that go to whatever you want to get.

Your current relationships look way too complex. I've never working with something like this but I noticed that there were at least 3 ways to get from Product_Table to Features_Tables, which is not something that you want. I like not make any relationships in the Relationship window, and just make the ones I need in the queires that I need them. In case you don't know, just drag a field to another in the query desgin view to make a join.

[Just_Some_Guy]

Thanks. I'll get working on that and see how it goes. Unfortunately it's setup how the user wants. One question, if I want to get a value from the Feature field in one table, then another value from the Feature field but in a different table, how would that work? Like in my first post, the first feature shown, Feature A, is from an AMS IP, so that would come from the AMS-Feature junction table. But the next record down, Feature D comes from an IF IP table, so it would be in the IF-Feature table. How would I get both to show?

[offie]

[Table1].[Feature] is different from [Table2].[Feature] so even though they have the same name they are different, unless you try to join them. Access automatically differentiates between them. If you want to rename them you can type "IPFeature:" without the parentheses.

It looks like you want then all in one field so I would would an iif statement to say what you want to show up when ___ happens.
So you can say that if field 1 = this, or is not null, or something, AND field 2 = ???, then the true statement is ???