Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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!
0
Joe Brown
Asked:
Joe Brown
  • 3
  • 3
  • 2
1 Solution
 
vastoCommented:
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?
0
 
Joe BrownAuthor 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.
Doc1.docx
0
 
mlmccCommented:
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

TestTable
ID
TestName
ReportedName
MethodNumber

ProductTestTable
ID
ProductCode
TestID

TestDataTable
ProductTestID
MinValue
MaxValue


The sql will be something like this:
SELECT *
FROM TestTable t
LEFT JOIN ProductTestTable pt on pt.TestID=t.ID
LEFT JOIN TestDataTable td on pt.ID=td.ProductTestID
0
 
mlmccCommented:
That would be nuch better

mlmcc
0
 
Joe BrownAuthor 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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now