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
> Query Suggestions - Variables Based On User?, Access 2016    
 
   
arpirnat
post Mar 2 2018, 02:38 PM
Post#1



Posts: 110
Joined: 25-June 16



Just looking to see if this is possible or not to simplify things on our end.

Our current database runs consult reports for staff. It looks at who is logged in to figure out the alpha split they need to see based on a criteria on the query:
CODE
InStr([Forms]![frmMainLogin]![UserAlpha],Left([qPassThroughAddConsults].[PatientName],1))
for the field with
CODE
> "0"
for the Criteria. It pulls which letters they require from the EmployeeFiles table. This part works fine.
Secondly it splits out which consults each person needs based on an assigned team. Currently the team is assigned in the EmployeeFiles table, for example MedSurg is a team. When the report is initiated the VBA code filters the data out (I know filters are not ideal):
CODE
strMedSurg = "((Clinic = 'ALLERGY')   Or (Clinic ='HBO')  Or (Clinic ='PODIATRY')     Or (Clinic ='WOMENS INFERTILITY') Or " & _
"(Clinic = 'AUDIOLOGY')     Or (Clinic ='HEP-C')    Or (Clinic ='PSYCHIATRY')   Or (Clinic ='WOUND CARE') Or " & _
"(Clinic = 'BRACHYTHERAPY')     Or (Clinic ='IMAGING')  Or (Clinic ='PULMONARY') Or " & _
"(Clinic = 'BURN')  Or (Clinic ='IMAGING (NON-MAMMO/MRI/PET)')  Or (Clinic ='RADIATION THERAPY') Or " & _
"(Clinic = 'CARDIOLOGY')    Or (Clinic ='IMAGING MAMMO')    Or (Clinic ='REI') Or " & _
"(Clinic = 'CLINICAL TRIAL')    Or (Clinic ='IMAGING MRI/PET')  Or (Clinic ='RENAL') Or " & _
"(Clinic = 'COLONOSCOPY')   Or (Clinic ='INFECTIOUS DISEASE')   Or (Clinic ='RHEUMATOLOGY') Or " & _
"(Clinic = 'DENTAL')    Or (Clinic ='INTERVENTIONAL RADIOLOGY')     Or (Clinic ='SART') Or " & _
"(Clinic = 'DERMATOLOGY')   Or (Clinic ='LOW VISION')   Or (Clinic ='SLEEP STUDY') Or " & _
"(Clinic = 'DERMATOLOGY UVB REQUEST')   Or (Clinic ='MATERNAL CARE')    Or (Clinic ='SPEECH PATHOLOGY') Or " & _
"(Clinic = 'DOT')   Or (Clinic ='MENTAL HEALTH')    Or (Clinic ='SPEECH THERAPY') Or " & _
"(Clinic = 'EMG/NCV')   Or (Clinic ='NEUROLOGY')    Or (Clinic ='SURGERY') Or " & _
"(Clinic = 'ENDOCRINOLOGY')     Or (Clinic ='NEUROSURGERY')     Or (Clinic ='TAVR') Or " & _
"(Clinic = 'ENT')   Or (Clinic ='ONCOLOGY') Or (Clinic = 'HEMAC/ONCOLOGY XRT') Or (Clinic ='THORACIC SURGERY') Or " & _
"(Clinic = 'ENT XRT')   Or (Clinic ='OPEN HEART')   Or (Clinic ='TILT TABLE') Or " & _
"(Clinic = 'EP')    Or (Clinic ='OPHTHALMOLOGY')    Or (Clinic ='TRANSPLANT') Or " & _
"(Clinic = 'GENERAL MEDICINE')  Or (Clinic ='OPTOMETRY')    Or (Clinic ='UROLOGY') Or " & _
"(Clinic = 'GENERAL SURGERY')   Or (Clinic ='ORTHO SPINE')  Or (Clinic ='UROLOGY XRT') Or " & _
"(Clinic = 'GI DIAGNOSTIC')     Or (Clinic ='ORTHOPEDICS')  Or (Clinic ='VASCULAR LAB') Or (Clinic ='SA RECOVERY') Or " & _
"(Clinic = 'GYNECOLOGY')    Or (Clinic ='PAIN CLINIC')  Or (Clinic ='VASCULAR SURGERY') Or (Clinic ='IVF') Or (Clinic = 'Pain')) And " & _
AlertType


strMedSurg is then assigned to the strRange variable and the form is opened like so:
CODE
DoCmd.OpenForm "frmUpdatedFullConsults", FormType, , strRange, , , ReportSort


The inherent issue with this is if someone needs to play with the filters, it starts to show them consults they do not need to see... if I could figure out how best to incorporate this in to the initial query that data simply wouldn't 'exist' on the form.

On a small side note the consults are updated and changed frequently so the less places it is kept and needed to be updated the better, so in the current ways method that is just 1 location.
This post has been edited by arpirnat: Mar 2 2018, 02:39 PM
Go to the top of the page
 
GroverParkGeorge
post Mar 2 2018, 03:08 PM
Post#2


UA Admin
Posts: 34,135
Joined: 20-June 02
From: Newcastle, WA


As a general rule, table-driven solutions tend to be more flexible than code solutions.

I'd be looking for a table to handle all of those options, not code.

Each team would be entered in this table, along with the consults appropriate to that team. THis, in turn, means there has to be a table of teams and a table of consults.
Go to the top of the page
 
arpirnat
post Mar 2 2018, 04:57 PM
Post#3



Posts: 110
Joined: 25-June 16



If I do a code like:
CODE
[Forms]![frmMainLogin]![UserConsultGroup]

in the query to look at the group the user is in and compare it to the group assigned to a consult, is there a way to make it work with mixed responses? In short we have 3 groups, Admin, MedSurg, and HomeCare. These all work great by themselves with this.
However there are some people who cover more than 1 group, for instance MedSurg and HomeCare. Their group in the current system gets marked as MedSurg/HomeCare. Now I could make this work by having all consults listed multiple times, one time assigning it to MedSurg and the second time to MedSurg/HomeCare, one time to MedSurg/Admin and so on... but just curious if there is a way in the query to have it so if the field is located anywhere in the Criteria (a backwards 'like'?).
Go to the top of the page
 
GroverParkGeorge
post Mar 2 2018, 10:53 PM
Post#4


UA Admin
Posts: 34,135
Joined: 20-June 02
From: Newcastle, WA


That's why I suggested a table-driven solution based on a junction table. Have you worked with those before?

And, yes, that will result in one record in this table for each combination of groups and consults. That's how relational databases work best. A query based on such a table gathers all of the needed results.

You can then transform those results if you want to DISPLAY them differently. But unlike Excel where both data and layout are part of a single "surface", a relational database allows you to take advantage of good table design AND to implement whatever display format you see necessary.
This post has been edited by GroverParkGeorge: Mar 2 2018, 10:57 PM
Go to the top of the page
 
stevep
post Mar 3 2018, 05:02 AM
Post#5



Posts: 78
Joined: 9-November 14



George is correct that a table based solution will serve you better. And once you have built it, you will find it useful for other filtering tasks.

However, your use of so many "or" (more than 2) tells me you may be unfamiliar with the use of "in". In this case it could make your code more readable. It will be useful for tasks other than this one.

CODE
         strMedSurg = "((Clinic in 'ALLERGY', 'HBO', 'PODIATRY', 'WOMENS INFERTILITY', 'AUDIOLOGY','HEP-C','PSYCHIATRY', " &  _
                          " 'WOUND CARE', 'BRACHYTHERAPY', ........." &  _
                          " 'VASCULAR   SURGERY', 'IVF','Pain')) " & _
                          " and " & AlertType

Also, if you really want to keep using this method, I would throw this in a function and use the function in this query (and others, preserving your desire to keep this list in a single place)

CODE
         Public Function MedSurgList () as String
                    
                    MedSurgList = "(Clinic in 'ALLERGY', 'HBO', 'PODIATRY', 'WOMENS INFERTILITY', 'AUDIOLOGY','HEP-C','PSYCHIATRY', " &  _
                          " 'WOUND CARE', 'BRACHYTHERAPY', ......... _
                          " 'VASCULAR   SURGERY', 'IVF','Pain')
                    
                    End Function


and put the function in the query =MedSurgList() & " and " & AlertType

Or, for ease of readability when you want to review which clinics are in the list

CODE
         Public Function MedSurgList () as String
                    
                    dim st as string
                    
                    ' maybe alphabetize these for ease of review, inserting new clinics in their alphabetical sequence rather than at the bottom
                    st = "'ALLERGY', "
                    st = st & "'HBO', "
                    st = st & "'PODIATRY', "
                    ..........
                    st = st & "'VASCULAR   SURGERY', "
                    st = st & "'IVF', "
                    st = st & "'Pain' "
                    
                    MedSurgeList = "Clinic in (" & st & ")"
                    
                    End Function


And for a quick look at what clinics are in the list without going back into your code, create this sub and call it from a command button. This is probably a friendlier way of letting users see what is on the list ("is clinic xxx on this report?")

CODE
       Public Sub ShowMedSurgList()
        
         msgbox medsurglist()
        
         End sub


You have to be careful with the use of " and ' but use of "in" is worth learning.

Just to be clear, a table based solution is the right answer here. Especially if the list keep changing. I just posted to outline the use of "in" and using functions to store long reusable strings that can be used in queries and procedure calls. I don't think Constants can be used in queries.

I am self taught and wish someone had shown me the use of "in" much earlier in my career. And "in" is not a very Google friendly search term, too short and common.
This post has been edited by stevep: Mar 3 2018, 05:18 AM
Go to the top of the page
 
GroverParkGeorge
post Mar 3 2018, 08:37 AM
Post#6


UA Admin
Posts: 34,135
Joined: 20-June 02
From: Newcastle, WA


Thanks for that alternative. I have found In() to be a bit tricky sometimes. Performance can be less optimal, but it is very valuable in the right circumstances.
Go to the top of the page
 
projecttoday
post Mar 3 2018, 09:49 AM
Post#7


UtterAccess VIP
Posts: 10,370
Joined: 10-February 04
From: South Charleston, WV


But, to be clear, MedSurg and HomeCare are distinct groups. Right?
Go to the top of the page
 
zaxbat
post Mar 3 2018, 10:14 AM
Post#8



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you will allow the user to do filtering....then you cannot depend on the filter... Instead you need to put your filter criteria into the query for the control source of the child subform and then do a requery/refresh of the child subform from the gotfocus event for the 0 tabstop textbox of the parent form, each time the user changes current record in the parent form.

At least, that is how i do it...oh....not sure if you have to clear the users filter or if that is automatic on requery.
Go to the top of the page
 
projecttoday
post Mar 3 2018, 12:35 PM
Post#9


UtterAccess VIP
Posts: 10,370
Joined: 10-February 04
From: South Charleston, WV


tblClinics
tblGroups
tblGroupClinics
tblConsults
tblConsultGroups

Your search form will have a datasheet or listbox which you can check off all the groups you want in your report. You will build a SQL string by looping through the temporary table of the listbox.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th December 2018 - 02:08 PM