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
> Subqueries Cannot Be Used Error, Access 2016    
 
   
jklinephd
post Aug 16 2019, 09:14 AM
Post#1



Posts: 78
Joined: 11-September 07
From: Missouri


I have created a query. In that query I have a field ("Findings") that has 4 values - Incompetent, Competent, Permanently Incompetent, and a blank. In a report that I am building from the query, I want to group the data in the report by the "Findings" field, but not in alphabetical order. I want the sorting to be something like Blank, Incompetent, Competent, Permanently Incompetent.

So in the query, I created a calculated field, called "sort_term" that uses embedded iif expressions to set the value of the calculated field to A for Blank, B for Incompetent, C for Competent, and D for Permanently Incompetent. The iif expressions correctly assign the values. In my query I have used the Totals and have to use Max on sort_term because if I don't the query pulls more records than I want. I keep getting the error:

Subqueries cannot be used int he expression (Max(IIf([Findings]="Incompetent","B",(IIf([Findings]="Competent","C",(IIf([Findings]="Permanently Incompetent","D","A"))))))

here is my SQL:

CODE
SELECT QryCRAFT_Admit_Current_Dx.[DMH ID], QryCRAFT_Admit_Current_Dx.[Consumer Full Name], QryCRAFT_Admit_Current_Dx.[Admission Date], QryCRAFT_Admit_Current_Dx.[Current Diagnosis], QryCRAFT_Admit_Current_Dx.[Admission Diagnosis], Max(QryFSH_Clients_Last_Evaluation.Date_Report_sent) AS [Date of Report], Max(QryFSH_Clients_Last_Evaluation.Examiner) AS Examiner, Last(QryFSH_Clients_Last_Evaluation.Findings) AS Findings, Max(QryFSH_Clients_Last_Evaluation.reason_admit_1) AS [Reason for Admit 1], Max(QryFSH_Clients_Last_Evaluation.reason_admit_2) AS [Reason for Admit 2], Max(QryFSH_Clients_Last_Evaluation.reason_admit_3) AS [Reason for Admit 3], Max(QryFSH_Clients_Last_Evaluation.reason_admit_comm) AS [Reason for Admit Comment], Max(QryFSH_Clients_Last_Evaluation.Next_due) AS MaxOfNext_due, Max(IIf([Findings]="Incompetent","B",(IIf([Findings]="Competent","C",(IIf([Findings]="Permanently Incompetent","D","A")))))) AS sort_term
FROM QryCRAFT_Admit_Current_Dx LEFT JOIN QryFSH_Clients_Last_Evaluation ON QryCRAFT_Admit_Current_Dx.[DMH ID] = QryFSH_Clients_Last_Evaluation.med_rec_num
GROUP BY QryCRAFT_Admit_Current_Dx.[DMH ID], QryCRAFT_Admit_Current_Dx.[Consumer Full Name], QryCRAFT_Admit_Current_Dx.[Admission Date], QryCRAFT_Admit_Current_Dx.[Current Diagnosis], QryCRAFT_Admit_Current_Dx.[Admission Diagnosis];


I have searched here an other places with nothing that makes sense. If I leave the calculated field without a Max on the Totals line it over selects
Thanks
Go to the top of the page
 
cheekybuddha
post Aug 16 2019, 09:58 AM
Post#2


UtterAccess VIP
Posts: 11,487
Joined: 6-December 03
From: Telegraph Hill


Try just sorting without including in the SELECT list:
CODE
SELECT
  qc.[DMH ID],
  qc.[Consumer Full Name],
  qc.[Admission Date],
  qc.[Current Diagnosis],
  qc.[Admission Diagnosis],
  Max(qf.Date_Report_sent) AS [Date of Report],
  Max(qf.Examiner) AS Examiner,
  Last(qf.Findings) AS Findings,
  Max(qf.reason_admit_1) AS [Reason for Admit 1],
  Max(qf.reason_admit_2) AS [Reason for Admit 2],
  Max(qf.reason_admit_3) AS [Reason for Admit 3],
  Max(qf.reason_admit_comm) AS [Reason for Admit Comment],
  Max(qf.Next_due) AS MaxOfNext_due
FROM QryCRAFT_Admit_Current_Dx qc
LEFT JOIN QryFSH_Clients_Last_Evaluation qf
       ON qc.[DMH ID] = qf.med_rec_num
GROUP BY
  qc.[DMH ID],
  qc.[Consumer Full Name],
  qc.[Admission Date],
  qc.[Current Diagnosis],
  qc.[Admission Diagnosis]
SORT BY
  IIf(Last(qf.Findings) = "Incompetent",
    1,
    IIf(Last(qf.Findings) = "Competent",
      2,
      IIf(Last(qf.Findings) = "Permanently Incompetent"
        3,
        0
      )
    )
  )
;

Note: Using LAST is almost always a risky option - Perhaps choose MIN or MAX here as well.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 10:40 AM
Post#3


UA Admin
Posts: 35,692
Joined: 20-June 02
From: Newcastle, WA


First, how did you set up that field with the four choices? It's not a multi-value field (MVF) is it? Is it a Value List in a combo box?

Or is it, as it should have been set up, a plain old field that is a Foreign Key to a Lookup table with the four options? I REALLY hope this is the way it was done because it offers the best combination of robustness, efficiency and flexibility. PLUS it'll support your sorting requirement with no hassles.

Thanks for the details so we can start offering ideas.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 10:52 AM
Post#4


UA Admin
Posts: 35,692
Joined: 20-June 02
From: Newcastle, WA


I always avoid Last() and First(), precisely because of cases like this. Min() and Max() are much more reliable. The current sort order determines what is "first", as you know. But the problem here is that the sort order is not going to reflect alphabetic sorting anyway, so there has to be a Helper field for that.

If, as I really, really hope, this is a valid Foreign Key to a Lookup table containing the four records for "Findings", then it is a simple matter to add a second field to that lookup table containing the desired sort order, 1 through 4 (and beyond as additional Finding types are added). Then, in queries, that Finding Sort Order field handles the sort in plain old standard SQL. Done and dusted.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dale.fye
post Aug 16 2019, 11:57 AM
Post#5



Posts: 160
Joined: 28-March 18
From: Virginia


I think, if you remove the Max() from that computed column, and then add that into the GroupBy, you should get what you are looking for. And you might try the Switch Function instead of the embedded IIFs().
CODE
SELECT QryCRAFT_Admit_Current_Dx.[DMH ID]
, QryCRAFT_Admit_Current_Dx.[Consumer Full Name]
, QryCRAFT_Admit_Current_Dx.[Admission Date]
, QryCRAFT_Admit_Current_Dx.[Current Diagnosis]
, QryCRAFT_Admit_Current_Dx.[Admission Diagnosis]
, Max(QryFSH_Clients_Last_Evaluation.Date_Report_sent) AS [Date of Report]
, Max(QryFSH_Clients_Last_Evaluation.Examiner) AS Examiner
, Last(QryFSH_Clients_Last_Evaluation.Findings) AS Findings
, Max(QryFSH_Clients_Last_Evaluation.reason_admit_1) AS [Reason for Admit 1]
, Max(QryFSH_Clients_Last_Evaluation.reason_admit_2) AS [Reason for Admit 2]
, Max(QryFSH_Clients_Last_Evaluation.reason_admit_3) AS [Reason for Admit 3]
, Max(QryFSH_Clients_Last_Evaluation.reason_admit_comm) AS [Reason for Admit Comment]
, Max(QryFSH_Clients_Last_Evaluation.Next_due) AS MaxOfNext_due
, SWITCH([Findings]="Incompetent","B",[Findings]="Competent","C",[Findings]="Permanently Incompetent","D", True, "A") AS sort_term
FROM QryCRAFT_Admit_Current_Dx LEFT JOIN QryFSH_Clients_Last_Evaluation ON QryCRAFT_Admit_Current_Dx.[DMH ID] = QryFSH_Clients_Last_Evaluation.med_rec_num
GROUP BY QryCRAFT_Admit_Current_Dx.[DMH ID]
, QryCRAFT_Admit_Current_Dx.[Consumer Full Name]
, QryCRAFT_Admit_Current_Dx.[Admission Date]
, QryCRAFT_Admit_Current_Dx.[Current Diagnosis]
, QryCRAFT_Admit_Current_Dx.[Admission Diagnosis]
, SWITCH([Findings]="Incompetent","B",[Findings]="Competent","C",[Findings]="Permanently Incompetent","D", True, "A");

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
jklinephd
post Aug 19 2019, 11:55 AM
Post#6



Posts: 78
Joined: 11-September 07
From: Missouri


Dave,

Thanks. I tried your code and it kept giving me an syntax error. Could not figure out what was missing. it was a straight copy and past. I agree about Last versus Max. I have tried both. In the Query's current iteration (without the additional calculated field), If I use Last I get the right results. If I use Max I get the wrong results. While this Query is based on Joins of two queries, the table that the data comes from has multiple records from the each client. The records represent assessments. When I use "Max" the query displays the date of one of the assessments ("Date of Report") and it is always the last report. But the finding is almost always the finding from an earlier report. When i use Last, the data comes out fine, unless I mess up the sort.

Thanks

Jeff
Go to the top of the page
 
jklinephd
post Aug 19 2019, 12:04 PM
Post#7



Posts: 78
Joined: 11-September 07
From: Missouri


QUOTE
First, how did you set up that field with the four choices? It's not a multi-value field (MVF) is it? Is it a Value List in a combo box?

Or is it, as it should have been set up, a plain old field that is a Foreign Key to a Lookup table with the four options? I REALLY hope this is the way it was done because it offers the best combination of robustness, efficiency and flexibility. PLUS it'll support your sorting requirement with no hassles.

Thanks for the details so we can start offering ideas.


The field in the original table is not linked to a lookup table, it is strictly a combo box on a form. You are right that a linked table with key would have been nice. Creating the calculated field in the query works (Dale.Frye's SQL works, sort of). I also got around this originally by creating a calculated field on the Report and that worked too. I agree with the use of Last versus Max, but as I said above, it generates incorrect results.

Thanks

Jeff
Go to the top of the page
 
jklinephd
post Aug 19 2019, 12:25 PM
Post#8



Posts: 78
Joined: 11-September 07
From: Missouri


QUOTE
I think, if you remove the Max() from that computed column, and then add that into the GroupBy, you should get what you are looking for. And you might try the Switch Function instead of the embedded IIFs().


Dale,

This SQL works, sort of. it generates the Calculated field correctly. What happens when I add this is that the Query results expand. even if i switch to Max findings, it expands. When I have the query without the added Calculated field it captures 70 clients. The number it should capture. If I use "Last" in the totals for the "Findings" field then query outputs the correct result. 70 records with the correct "Finding." If I switch to using "Max" "Finding" then the output is the same 70 clients but it switched the "Finding" to the "Finding" associated with an earlier report date.


Consumer Full Name|Date of Report|Findings |
Smith, John | 3/2/2018 | Incompetent |

if I switch it back to "Last" finding it says:

Consumer Full Name|Date of Report|Findings |
Smith, John | 3/2/2018 | Competent |


if i use your SQL, it adds the calculated field. The use of Last or Max does not change anything. and I get this result.

Consumer Full Name|Date of Report|Findings | sort_term |
Smith, John | 3/2/2018 | Competent | C
Smith, John | 10/31/20117 | Incompetent | B

So with your query, the sort term is correct. But the underlying data includes every report on each client. Expanding the number of clients from 70 to 117. Basically, every single client with more than one assessment has all of their assessments displayed. The query is supposed to only display their last assessment. If I try to add a "Max" to the calculated control I get an error.

I appreciate the suggestions. I have no idea why Max is not working to display the correct info. The "max" finding for each of these clients should be the last assessment.

Thanks!
Go to the top of the page
 
jklinephd
post Aug 19 2019, 01:01 PM
Post#9



Posts: 78
Joined: 11-September 07
From: Missouri


Oh, BTW. I have not been double checking my data, and using Max or Last for the "Finding" field is not consistently working. Some people's data is correct others is not. Not certain why.

Jeff
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd September 2019 - 05:34 AM