TWO links between tables (AND or OR logic)

[smilbert]

CR XI and MS SQL 2000

I’m trying to create a report that shows the Transmit Status for each REPORT for each EXAM. Some EXAMs can have more than one report, and there may or may not be a ‘status’ for each of them.

There is a tbExams table that contains an ExamID.
There is a tbExamReport table that contains the ExamID as well as an ExamReportID (that is 0 for the first report, 1 for the second, etc)

NOW, the Transmit Status is in another table tbReportQueue. This table contains the ExamID as well as the ExamReportID.

I’ve tried creating TWO links from my tbExamReport table to the tbReportQueue table, but they seem to use OR logic instead of AND. It seems to only match on the ExamID, and I get the same results for every Report. In some cases, there is NO entry for the ExamID/ReportID combination (that’s what I’m looking to report on!!!)

I tried linking the tbExams.ExamID and tbExamReport.ExamReportID to the tbReportQueue also—no luck.

Hope I described this accurately enough. I can provide any more info if needed. Thanks in advance for your help!

--Scott

 

[synapsevampire]

From what I can gather, you want : there is NO entry for the ExamID/ReportID combination (that’s what I’m looking to report on!!!)

So try adding the tbReportQueue table twice, each joined by the different fields (still hgard to know the joins, you use descriptivew instead of technical information) and changing the joins to a left outer and in the report->selection formula->record use:

isnull({tbReportQueue.exam_id})
or
isnull({tbReportQueue2.examreport_id})

That should get you close.

You'reprobably better served to not chat so much about what you tried unseuccessfully and instead describe the tables and the joins used to which field from which field.

-k