Cross Product Query

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

I just set up a simple pair of tables (Tasks, Systems) in A2007. I now want
to "Cross" them, so I get one row for each Task for each System. That is, I
believe, the "cross product" of the two tables. So, if I have 3 rows in the
task table and 5 rows in the systems table, I'll see 15 rows in the query
(3*5).

I have done this for years using A2000-2003, but I just set it up in A2007
and got NO results (0 rows), and no error message. The SQL looks right. I
don't understand. Can anyone help?

I simply set up the QBE with the two tables shown, and no join between them.
Then, to test it, I simply asked it to show me every column from each table.
No results (0 rows).
 
Do you want to get output like:

task_rec_1 | System_Rec_1
task_rec_2 | System_Rec_1
....
task_rec_1 | System_Rec_2
etc.

Or are you wanting:

task_rec_1
task_rec_2
.....
System_Rec_1
etc.

If the first one, then you are correct. Your SQL should look like:

Select [Task].Field1, [Task].Field2,...,[System].Field1, [System].Field2...
From Task, System

If you want the second output, then it would be:

Select [Task].Field1, [Task].Field2,...
From Task
Union
Select [System].Field1, [System].Field2...
From System
 
Back
Top