Complex(?) conditions query

[Michael R]

Hello all.

I have a Jet SQL question:

I'm trying to write a query based on these 2 tables:

table1:
A: B:
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2
4 2
5 2

table2:
C: D:
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2
4 2
5 2

Now the query needs to show all the combinations of all the fields:

SELECT table1.A, table1.B, table2.C, table2.D
FROM table1, table2;

but without the rows where A = C and without the rows where both of these conditions occur on the same row: A = C + 1, B<>D.

Please help me to combine this query without using UNION, if possible

Thank you, I appreciate your time.

[ADezii]

Try:

SELECT Table1.A, Table2.C, Table1.B, Table2.D
FROM Table1, Table2
WHERE (((Table1.A)<>[Table2].[C] And (Table1.A)<>[Table2].[C]+1) AND ((Table1.B)=[Table2].[D]));

OUTPUT:

A    C    B    D
5    1    1    1
4    1    1    1
3    1    1    1
5    2    1    1
4    2    1    1
1    2    1    1
5    3    1    1
2    3    1    1
1    3    1    1
3    4    1    1
2    4    1    1
1    4    1    1
4    5    1    1
3    5    1    1
2    5    1    1
1    5    1    1
5    1    2    2
4    1    2    2
3    1    2    2
5    2    2    2
4    2    2    2
1    2    2    2
5    3    2    2
2    3    2    2
1    3    2    2
3    4    2    2
2    4    2    2
1    4    2    2
4    5    2    2
3    5    2    2
2    5    2    2
1    5    2    2
 

[Michael R]

Works well, thanks!
I guess you can't build everything with Access query builder.

[ADezii]

Actually, this Query you can (both Tables Shown but not Joined):

Field: A                                            B             C         D
-----------------------------------------------------------------------------------
Table: Table 1                                      Table1        Table2    Table2
-----------------------------------------------------------------------------------
Criteria: <>[Table2].[C] And <>[Table2].[C]+1       [Table2].[D]
-----------------------------------------------------------------------------------
 

[Rabbit]

It's just easier to show SQL rather than showing the query designer.