How to use cross join in MS Access?

Question

Using MS - Access 2003

Table

S.No   Details

1      name
2      address
3      email
4      phone
5      fax

and so on...,

I want to combine all the details for the s.no’s. Like

Expected Output

S.No Details

1    name
     address 
     email
     Phone
     Fax
2    address
     name
     email
     phone
     fax
3    email 
     name
     address
     phone
     fax
4    phone
     name
     address
     email
     fax
5    fax
     name
     address 
     email
     Phone

and so on..,

Cross join is not accepting in access 2003? Any other solution for this.

Need query help.

Answer 1

I'm not sure about what you want to accomplish, but the syntax for a full cartesian product (cross join) is select * from table1, table2

If you don't want to cross everything but only some columns, something like

SELECT *
FROM (select id from details) b, (select detail from details) c
;

should work:

id  detail
1   name
2   name
3   name
4   name
5   name
1   email
2   email
....

Hope this helps.

Answer 2

If you are trying to do this in the graphical query builder all you need to do is add both tables and ensure there is no link between them.