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!
Joe BrownIT ManagerAsked:
Who is Participating?
vastoConnect With a Mentor Commented:
I will highly recommend you to change your data structure, before to start working on the report. As it is not the tables are denormalized.
I believe that you have to reorganize your data in a structure like this:




The sql will be something like this:
FROM TestTable t
LEFT JOIN ProductTestTable pt on pt.TestID=t.ID
LEFT JOIN TestDataTable td on pt.ID=td.ProductTestID
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 BrownIT ManagerAuthor Commented:
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.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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.

Joe BrownIT ManagerAuthor Commented:
So, there is no way to link the left table to the right table without making 25 copies of the right hand table?
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.

That would be nuch better

Joe BrownIT ManagerAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.