Crystal 8.5 report all possible combinations of 2 fields 1

[rradelet]

I am using Crystal 8.5 running an ODBC connection to a SQL2000 database.

Working with a table with fields:

From (number)
To (number)
Cost (number)

There are five possible values for From and To and Cost is the associated cost for each combination of values for From and To.

The data in the table is:

From To Cost
1 1 1.00
1 2 2.00
1 3 3.00
1 4 4.00
1 5 5.00
2 2 2.00
2 3 3.00
2 4 4.00
2 5 5.00
3 3 2.00
3 4 3.00
3 5 4.00
4 4 4.00
4 5 5.00
5 5 5.00

15 records total.

The table contains 5 records where From = 1, but only 4 records where From = 2, 3 records where from = 3, etc. The SQL application is designed to read the From/To combinations both ways, so if a cost for the combination or From 2 / To 1 is needed it pulls the cost for the value of the reverse combination (From 1 / To 2)


There are 25 possible to/from & from/to combinations.

What I need is a report displaying all 25 possible combinations and the cost associated with each:

From To Cost
1 1 1.00
1 2 2.00
1 3 3.00
1 4 4.00
1 5 5.00
2 2 2.00
2 3 3.00
2 4 4.00
2 5 5.00
3 3 2.00
3 4 3.00
3 5 4.00
4 4 4.00
4 5 5.00
5 5 5.00
2 1 2.00
3 1 3.00
4 1 4.00
5 1 5.00
3 2 3.00
4 2 4.00
5 2 5.00
4 3 3.00
5 3 4.00
5 4 5.00

I am note sure how to start with this. Any ideas?

 

[lbass]

I think you could do a union to get the results you want. After adding the fields to the detail section, go to database->Show SQL Query. Copy the SQL statement, add "union", and then paste the statement after the union. Then switch the position of the from and to fields. This should result in {table.from} containing both the from and to fields, and similarly, the {table.to} field containing both from and to. By using a union instead of a union all, you should get only distinct results. The statement will look something like this:

SELECT
table.`from`,table.`to`,table.`cost`
FROM
`table` table
Union
SELECT
table.`to`,table.`from`,table.`cost`
FROM
`table` table

-LB

 

[rradelet]

Thanks lbass!

Your suggestion worked worked perfectly.