UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pass-through Query Help, Access 2013    
 
   
ry94080
post Jun 13 2019, 09:48 AM
Post#1



Posts: 995
Joined: 27-July 05



Hi all,

I have a pass-through query that currently works fine.

CODE
SELECT sid, testcode, result, testreviewercomments, instrumentid, rundatetimets
FROM IM_SM.tinfo
INNER JOIN IM_SM.sinfo ON IM_SM.tinfo.Specimen = IM_SM.sinfo.Specimen
WHERE result is not null
GROUP BY sid, result, TestReviewerComments


I want to add an additional table (IM_SM.pinfo). Then, join IM_SM_sinfo.Patient = IM_SM_pinfo.Patient, and include the LocationFacility column from the IM_SM_pinfo table.

Can someone help me format this SQL statement to include the additional table and join? I wasn't quite sure how to format it.

Thanks
Go to the top of the page
 
theDBguy
post Jun 13 2019, 10:29 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. This is just a guess and not tested. Plus, even if it doesn't error out, it might not produce the correct result you're expecting since you're grouping on the data. Cheers.
CODE
SELECT sid, testcode, result, testreviewercomments, instrumentid, rundatetimets, LocationFacility
FROM IM_SM.tinfo
INNER JOIN IM_SM.sinfo ON IM_SM.tinfo.Specimen = IM_SM.sinfo.Specimen
INNER JOIN IM_SM.pinfo ON IM_SM.sinfo.Patient = IM_SM.pinfo.Patient
WHERE result is not null
GROUP BY sid, result, TestReviewerComments, LocationFacility

Wait a minute, did you say your original query works? Without grouping on testcode, instrumentid, and rundatetimets? Interesting...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Daniel_Stokley
post Jun 13 2019, 11:15 AM
Post#3



Posts: 298
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello, just curious, since your SELECT clause has no aggregation, why do you have a GROUP BY clause?
Go to the top of the page
 
theDBguy
post Jun 13 2019, 11:23 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Ah, that's a good question!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 09:42 PM