A form that shows all possible combinations from a relationship join

[securitywyrm]

EDIT: SOLUTION: A Cartesian join will give you all possible combinations, which you get by NOT joining two tables in a query, but rather just pulling the fields in directly. I was trying so hard to use joins that it didn't occur to me NOT to use them.

Greetings! I've somehow landed a job doing MS Access design, and I'm learning as I go. I'm building my first real relational database, and here's my situation.



Edit for clarity



Table One has three programs. Table Two has four titles. Thus there should be twelve possible combinations. I want a query that will show both full and empty combinations. I currently have three data points in Table three.

I have tried the error message advice about making the first step a query and then using that as part of a second query, but it gives just the first query results with no additional lines.

Any help would be much appreciated.

[CJ_London]

Sounds like you need to use left joins

In the query designer, join your main data table to each of the other three tables. For each join, right click and select the second option - include all records from maintable and only those...... Include all columns from all tables at this stage, you can refine it later

run your query. You will now see blanks in the other table columns where there is no matching data to maintable.

if you only want to see records where there is missing data in one or more table, it is easier to set a criteria rather than trying to filter the query

criteria would be something like

ProgramID is Null or ContactsID is Null or TitlesID is null

[securitywyrm]

I"m getting an error about ambiguous outer joins. I'll keep digging into it. I understand the theory, but the flaw is somewhere in my implementation. I'm going to create a test database just for this task instead of working in my active database, and try again.

[isladogs]

Something like this will create the ambiguous joins error



To fix, ensure the join direction is consistent as below

[securitywyrm]

I think I understand the above. My issue is that I need them to go to the middle, as going in one direction wouldn't get the exponential results I want.

Table 1 has 4 programs
Table 2 has 3 titles
Table 3 is where they link up, and has three data points.



However there are twelve possible combinations (4x3) and I want a form that will show all 12 of those combinations. I've tried doing what the error message says and making the first join a query and then using that in the second query, and that only give three results instead of the 12 I'm expecting.

[CJ_London]

Your joins are goint the wrong way, they should be from maindata, not to maindata.

And if you want every possible combination then use what is called a Cartesian query - no joins at all

[securitywyrm]

Your joins are goint the wrong way, they should be from maindata, not to maindata.

And if you want every possible combination then use what is called a Cartesian query - no joins at all

Sounds Exotic. I'll look into that, thank you!

EDIT; I GET IT NOW, THANK YOU! I was trying so hard to use joins that it didn't occur to me NOT to use them!

[isladogs]

Whilst Cartesian joins (AKA no joins) can at times be useful they should only be used in very limited situations
Otherwise you will get lots of superfluous results if misused

Just to clarify if the programs are called A,B,C and the titles are 1,2,3,4 you are saying you could have A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4 ?
Perhaps you should review your table structure.

[securitywyrm]

Whilst Cartesian joins (AKA no joins) can at times be useful they should only be used in very limited situations
Otherwise you will get lots of superfluous results if misused

Just to clarify if the programs are called A,B,C and the titles are 1,2,3,4 you are saying you could have A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4 ?
Perhaps you should review your table structure.

It's for a contacts database. The old structure was one giant flat file. Unfortunately stuff keeps getting changed, which would break queries constantly, so now I'm making one where every field that could be changed can be changed without breaking anything.

Right now my difficulty is using that cartesian query with a form so I can edit the main data table

[isladogs]

Right now my difficulty is using that cartesian query with a form so I can edit the main data table

I've just seen your other thread!
If you had said that it needed to be editable, the cartesian join would never have been suggested by ajax!

[securitywyrm]

I've just seen your other thread!
If you had said that it needed to be editable, the cartesian join would never have been suggested by ajax!

Well I can use the cartesian join with the reports (I think) so it displays the empty fields.