asked on

Many to one relationship in Crystal - How can I set this up?

I have a crystal report that I am trying to make. Data is coming from a SQL database.
I have a table for all of the tests performed on a product.
Test1 Name, Test1 Min Value, Test1 Max Value
Test2 Name, Test2 Min Value, Test2 Max Value
(repeated 25 times)

Then I have a table that outlines the Test Method information
Test Name, Reported Name, Test Method Number

I need the first table to connect to the second table for each test number so that I can pull the Reported Name, Test Method Number, Min Value and Max Value for each Test (all 25).
The Test Name in the first table is what matches the Test Name in the second table.

I have tried a left outer join but I get blank results as soon as I try to join Test2 to the TestName on the 2nd table. Do I have to do a view i SQL to achieve these results? I tried a subpreport as well and did not get that to work.

What am I missing here? I know this has to be possible somehow.

Thank you!

vasto

Do you mean that your first table contains columns:
Test1 Name, Test2 Name, Test3 Name, .... Test25 Name
or these are values ?

Can you post a picture of the tables schema in the database expert?

Joe Brown

ASKER

Each test name (Test1, Test2, TEst3) are strings
This name matches one of the LabMethod_TestNames. That is how I am trying to link the tables. I need the LabMethod_ReportedName and LabMethod_Number for each tests for each product code.

Does that make more sense with the attachment file with the screen shots of the two tables.
Doc1.docx

Mike McCracken

If I understand this correctly, the table on the right has a
Product with 25 tests

You need to use the test name to select a record from the other table.

You willl need to add the table on the left 25 times to the report. You will then connect each copy of the table to ONE of the test name fields.

mlmcc
linking-example.xls

Joe Brown

ASKER

So, there is no way to link the left table to the right table without making 25 copies of the right hand table?

Mike McCracken

The table with 1 test name field has only 1 value in a record so you can only connect it to 1 field in the other table and get a match.

I assume my linking of the table was in the correct order.

mlmcc

ASKER CERTIFIED SOLUTION

vasto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

That would be nuch better

mlmcc

Joe Brown

ASKER

I did restructure the database. I left the Test Method table and the Product table alone, I added a table for the Product Tests that includes the Product ID, Test Method Name and the min and maximum results. I joined the Product Table to the Product Tests table with an inner join on the Product ID, I then inner joined the Product Tests table with the Test Method table on the Test Method Name and it works like a charm. Thank you for your time and patience in helping me resolve this issue.