Multiple Rows in Query

[gjmac2]

I have two tables linked by ID number in a query. I am grouping by the tissue specific deviation field, but am getting repetative returns like the one below.

Region Tissue Compare Category TissueOverallDispositionText DonorTracCaseID StatTracId TissueSpecificDeviationValue
Southeast Bone Recovered Donor 35951 5651464
Southeast Bone Recovered Donor 35951 5651464 Discarded by processor due to + serology

I need to know if there is a formula or function that seaches a given field and returns the individual results based on that field. In othere words, if the tissue specific deviation field is populated, then I want that record. If not, I want the null record.

Thanks

 

[r937]

LEFT OUTER JOIN? ISNULL function?

pretty hard to guess without seeing your query

r937.com | rudy.ca

 

[gjmac2]

Sorry about that.

dbo_VW_RecoveryInfo RIGHT JOIN TBL-Donor Trac Data Table-Main

no isnull functions

 

[r937]

table layouts (column names and datatypes)?

complete query?

r937.com | rudy.ca

 

[gjmac2]

Here is the SQl view of the query

SELECT Format([tbl-donor trac data table-main]![statusdate],"mm\,yyyy") AS [Format Status Date], [TBL-Donor Trac Data Table-Main].StatusDate, [TBL-Donor Trac Data Table-Main].Region, IIf([dbo_vw_recoveryinfo]![category]="skin","Skin",IIf([dbo_vw_recoveryinfo]![organtissue]="Pericardium","Pericardium",IIf([dbo_vw_recoveryinfo]![organtissue]="Heart for Valves","Heart Valves",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Saphenous Vein*","Saphenous Vein",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Anterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Posterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Femoral Vein*","Femoral","Bone"))))))) AS [Tissue Compare Category], [TBL-Donor Trac Data Table-Main].TissueOverallDispositionText, [TBL-Donor Trac Data Table-Main].DonorTracCaseID, [TBL-Donor Trac Data Table-Main].StatTracId, dbo_VW_RecoveryInfo.TissueSpecificDeviationValue

FROM dbo_VW_RecoveryInfo RIGHT JOIN [TBL-Donor Trac Data Table-Main] ON dbo_VW_RecoveryInfo.CaseID = [TBL-Donor Trac Data Table-Main].ID

WHERE (((dbo_VW_RecoveryInfo.Category)<>"eyes") AND ((dbo_VW_RecoveryInfrganTissue) Not Like "Peroneous Longus Tendon*" And (dbo_VW_RecoveryInfrganTissue) Not Like "Illiac Crest*" And (dbo_VW_RecoveryInfrganTissue) Is Not Null) AND (([TBL-Donor Trac Data Table-Main].Region)<>"north") AND ((dbo_VW_RecoveryInfo.RecoveryPersonFullName) Is Not Null))

GROUP BY Format([tbl-donor trac data table-main]![statusdate],"mm\,yyyy"), [TBL-Donor Trac Data Table-Main].StatusDate, [TBL-Donor Trac Data Table-Main].Region, IIf([dbo_vw_recoveryinfo]![category]="skin","Skin",IIf([dbo_vw_recoveryinfo]![organtissue]="Pericardium","Pericardium",IIf([dbo_vw_recoveryinfo]![organtissue]="Heart for Valves","Heart Valves",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Saphenous Vein*","Saphenous Vein",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Anterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Posterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Femoral Vein*","Femoral","Bone"))))))), [TBL-Donor Trac Data Table-Main].TissueOverallDispositionText, [TBL-Donor Trac Data Table-Main].DonorTracCaseID, [TBL-Donor Trac Data Table-Main].StatTracId, dbo_VW_RecoveryInfo.TissueSpecificDeviationValue, dbo_VW_RecoveryInfo.NonRecoveryReasonValue

HAVING ((([TBL-Donor Trac Data Table-Main].StatusDate) Between #10/1/2006# And #11/1/2006#) AND (([TBL-Donor Trac Data Table-Main].TissueOverallDispositionText)="recovered donor") AND ((dbo_VW_RecoveryInfo.NonRecoveryReasonValue) Is Null))

ORDER BY [TBL-Donor Trac Data Table-Main].Region, IIf([dbo_vw_recoveryinfo]![category]="skin","Skin",IIf([dbo_vw_recoveryinfo]![organtissue]="Pericardium","Pericardium",IIf([dbo_vw_recoveryinfo]![organtissue]="Heart for Valves","Heart Valves",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Saphenous Vein*","Saphenous Vein",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Anterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Posterior Tibialis Tendon*","Tibialis",IIf([dbo_vw_recoveryinfo]![organtissue] Like "Femoral Vein*","Femoral","Bone")))))));

 

[r937]

i don't understand why there's an extra column in your GROUP BY -- and in fact i don't understand why you even have a GROUP BY

change the RIGHT OUTER JOIN to INNER JOIN

that humungous nested IIF should be isolated into its own query, i.e. write a "select star" query against dbo_VW_RecoveryInfo where you use the nested IIF to produce an additional column, and then use this query instead of dbo_VW_RecoveryInfo for the problem

r937.com | rudy.ca

 

[gjmac2]

Thank you for your help.

Please forgive my ignorance, but what is a "select star" query?

 

[lespaul]

SELECT star = SELECT *

retrieves all fields from the identified table in the FROM clause.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[r937]

SELECT *
, IIf([category]="skin","Skin"
  , IIf([organtissue]="Pericardium","Pericardium"
  , IIf([organtissue]="Heart for Valves","Heart Valves"
  , IIf([organtissue] Like "Saphenous Vein*","Saphenous Vein"
  , IIf([organtissue] Like "Anterior Tibialis Tendon*","Tibialis"
  , IIf([organtissue] Like "Posterior Tibialis Tendon*","Tibialis"
  , IIf([organtissue] Like "Femoral Vein*","Femoral","Bone"))))))) 
      as [Tissue Compare Category]
FROM dbo_VW_RecoveryInfo

save this query under some meaningful name, e.g. dbo_VW_RecoveryInfo_plus

then you can us this query in your main query

SELECT Format(M.[statusdate],"mm\,yyyy")
           AS [Format Status Date]
     , M.StatusDate
     , M.Region
     , R.[Tissue Compare Category]
     , M.TissueOverallDispositionText
     , M.DonorTracCaseID
     , M.StatTracId
     , R.TissueSpecificDeviationValue
  FROM dbo_VW_RecoveryInfo_plus as R
RIGHT
  JOIN [TBL-Donor Trac Data Table-Main] as M
    ON R.CaseID = M.ID
WHERE R.Category <> "eyes"
  AND R.OrganTissue Not Like "Peroneous Longus Tendon*"
  And R.OrganTissue Not Like "Illiac Crest*"
  And R.OrganTissue Is Not Null
  AND M.Region <> "north"
  AND R.RecoveryPersonFullName Is Not Null
  and M.StatusDate Between #10/1/2006# And #11/1/2006#
  AND M.TissueOverallDispositionText = "recovered donor"
  AND R.NonRecoveryReasonValue Is Null
GROUP
    BY Format(M.[statusdate],"mm\,yyyy")
     , M.StatusDate
     , M.Region
     , R.[Tissue Compare Category]
     , M.TissueOverallDispositionText
     , M.DonorTracCaseID
     , M.StatTracId
     , R.TissueSpecificDeviationValue
     , R.NonRecoveryReasonValue
ORDER
    BY M.Region
     , R.[Tissue Compare Category]

i moved the HAVING conditions into the WHERE clause where they belong (stupid microsoft query builder!)

notice your GROUP BY has one more column than your SELECT, and yet this extra column is supposed to be NULL -- i still think the GROUP BY is wrong

r937.com | rudy.ca

 

[gjmac2]

Thanks a bunch, r937! You have gone above and beyond being helpful.

Now, having said that, the code you suggested still did not solve the problem.

Man, this is frustrating.

 

[r937]

try removing the GROUP BY, and change the RIGHT OUTER JOIN to INNER

r937.com | rudy.ca