Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal 8.5 report all possible combinations of 2 fields 1

Status
Not open for further replies.

rradelet

IS-IT--Management
Oct 28, 2003
35
CA
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?
 
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
 
Thanks lbass!

Your suggestion worked worked perfectly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top