Decision support tool - need to match across tables

[cmray58]

I'm creating a clinical decision support tool for our clinic with access. Without getting into too much detail, our clinic is required to review all medications that we prescribe to patients to make sure the patient has the correct certification to receive that med. Each certification allows for a specific set of drug classes.

In the DB, I have the prescriptions data imported (which includes the drug class code for each patient's Rx), a list of the patient's current certifications, and a crosswalk that outlines what certifications are allowed under each drug class code. So far, I've been able to pull in patients' certs and drug class codes they're currently using into a single query. The final step is to decide whether the patient's drug class code is allowed for under their current certs. This is the part I can't figure out.

Example: Query 1 contains Patient A who is on drug class code 1 and has certification A. Table 1 shows that for drug class code 1 patient must have certs B, C, or D. How do I create a query that gives me an output that says "No Match" for patient 1?

UPDATE: I'm trying to do something like Iif("*[Certs_Combined]*" Like "[Cert1]", "Yes", "No"). [Certs_Combined] is a list of all the patient's current certs in one combined string. [Cert1] is one of the allowable certs for that drug class code. This formula isn't working though. Anyone see an obvious reason why?

 

[plog]

Can you post data to demonstrate your example? Please include table and field names and enough data to demonstrate all cases. You can post in Excel and upload if necessary.

 

[cmray58]

Can you post data to demonstrate your example? Please include table and field names and enough data to demonstrate all cases. You can post in Excel and upload if necessary.

Surely! Please see the attached file

 

[plog]

You need to fix your Drug-Cert Crosswalk table. When you start suffixing field names with numbers, you are doing it wrong. The crosswalk should only have 2 fields in it: [ClassCode] and [Cert]. Then you simply keep adding rows until all your data is in the table.

Using your first 2 ClassCodes as examples, this is what the table should look like:

DrugCertCrosswalk
ClassCode, Cert
A1D, J45.909
A1D, 496
A1D, 493.00
A1D, 493.0
A1D, 493.9
B0A, J32.9
B0A, J31.0
B0A, 473.9
B0A, 472.0

that's how the data in that table should look. It should have a ton of records, not just 1 record per ClassCode.

 

[cmray58]

You need to fix your Drug-Cert Crosswalk table. When you start suffixing field names with numbers, you are doing it wrong. The crosswalk should only have 2 fields in it: [ClassCode] and [Cert]. Then you simply keep adding rows until all your data is in the table.

Using your first 2 ClassCodes as examples, this is what the table should look like:

DrugCertCrosswalk
ClassCode, Cert
A1D, J45.909
A1D, 496
A1D, 493.00
A1D, 493.0
A1D, 493.9
B0A, J32.9
B0A, J31.0
B0A, 473.9
B0A, 472.0

that's how the data in that table should look. It should have a ton of records, not just 1 record per ClassCode.

Right...relational database rookie mistake. Thank you! Where would you go from there?

 

[plog]

I'm still not 100% on what you want. Do you want to identify Patient Prescriptions that they don't have a Cert for? Perhaps you can post a slimmed down example set with just 5 or so patients and then show me what data you expect your query to produce for those 5 patients.

 

[cmray58]

I'm still not 100% on what you want. Do you want to identify Patient Prescriptions that they don't have a Cert for? Perhaps you can post a slimmed down example set with just 5 or so patients and then show me what data you expect your query to produce for those 5 patients.

I need to know whether a patient has the right cert for the prescription that they're on based on the drug-cert crosswalk. E.g., Patient 1 is on a prescription with Drug Code 1 and Cert A. The crosswalk says that in order for a patient to be on Drug Code 1, they must have Certs B, C, or D. Therefore, in this case, the database would tell me the patient's prescription is not valid. Does this make more sense?

 

[plog]

Data examples would help the most. Give me 2 sets of data:

A. Starting sample data in your tables. Include table/field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with based on the data in A.

 

[cmray58]

Data examples would help the most. Give me 2 sets of data:

A. Starting sample data in your tables. Include table/field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with based on the data in A.

Here you go! See attached

 

[cmray58]

Here you go! See attached

To walk you through the file...The output sheet pulls each patient's prescription from the "Patient Prescriptions" sheet (table), then pulls the patient's corresponding cert from "Patient Certs". It then references the patient's drug code and cert combo with the "Drug-Cert Crosswalk" sheet to see whether the patient is allowed to have that Drug with that Cert. I.e., patients must have certain Certs in order to receive certain drugs. One thing to keep in mind, patient's will definitely have multiple drug codes and multiple certs. And, as you can see from the crosswalk, drug codes and certs are not a 1-1 match. There are many certs allowed under each drug class.

 

[plog]

Your expected output is wrong, you've included a field that shouldn't be in there. Either Cert or DrugClass. I don't know which, you tell me.

Actually, the best thing to do would be to include more sample data by adding multiple Certs/DrugClasses for existing patients.

 

[cmray58]

Your expected output is wrong, you've included a field that shouldn't be in there. Either Cert or DrugClass. I don't know which, you tell me.

Actually, the best thing to do would be to include more sample data by adding multiple Certs/DrugClasses for existing patients.

I want both cert and drug class code to show up. The output is essentially arow for each patient-drug code combo. In the output, there's also a column that lists all of the patient's certs (concatenated). Finally, an column with either Accepted or Denied depending on whether the patient's cert(s) (any of their certs) lines up with what's allowed via the crosswalk.

 

[plog]

You want to find out if a patient is certified for a drug class. Showing the patients' certs isn't necessary for that, so I didn't do it. You can search this forum for cancatenating field values into one field. Allen Brown has a formula, but again, showing it isn't necessary for the logic.

Here's the logic for what you want. First create a sub-query using [Patient Certs] and [Drug-Cert Crosswalk] (life would be easier if you eliminated spaces and hyphens in your table names). Here is the SQL for that sub-query:

SELECT [Patient Certs].ID, [Patient Certs].Cert, [Drug-Cert Crosswalk].DrugClass
FROM [Drug-Cert Crosswalk] INNER JOIN [Patient Certs] ON [Drug-Cert Crosswalk].Cert = [Patient Certs].Cert;

Name that query '_sub1'. It gets all the Drug classes a patient is certified for. Then to get the SUGGESTIONS you use this SQL:

 SELECT [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted") AS SUGGESTION
FROM [Patient Prescriptions] LEFT JOIN _sub1 ON ([Patient Prescriptions].DrugClass = [_sub1].DrugClass) AND ([Patient Prescriptions].ID = [_sub1].ID)
GROUP BY [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted");

Then again, to show all the certs of a patient search this forum for Allen Brown's function.

Lastly, I'm pretty sure there was an error with your OUTPUT tab. My query shows that ID=3, DrugClass=A1D is Denied, not accepted as you have it.

Let me know if you have any questions.

 

[cmray58]

You want to find out if a patient is certified for a drug class. Showing the patients' certs isn't necessary for that, so I didn't do it. You can search this forum for cancatenating field values into one field. Allen Brown has a formula, but again, showing it isn't necessary for the logic.

Here's the logic for what you want. First create a sub-query using [Patient Certs] and [Drug-Cert Crosswalk] (life would be easier if you eliminated spaces and hyphens in your table names). Here is the SQL for that sub-query:

SELECT [Patient Certs].ID, [Patient Certs].Cert, [Drug-Cert Crosswalk].DrugClass
FROM [Drug-Cert Crosswalk] INNER JOIN [Patient Certs] ON [Drug-Cert Crosswalk].Cert = [Patient Certs].Cert;

Name that query '_sub1'. It gets all the Drug classes a patient is certified for. Then to get the SUGGESTIONS you use this SQL:

 SELECT [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted") AS SUGGESTION
FROM [Patient Prescriptions] LEFT JOIN _sub1 ON ([Patient Prescriptions].DrugClass = [_sub1].DrugClass) AND ([Patient Prescriptions].ID = [_sub1].ID)
GROUP BY [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted");

Then again, to show all the certs of a patient search this forum for Allen Brown's function.

Lastly, I'm pretty sure there was an error with your OUTPUT tab. My query shows that ID=3, DrugClass=A1D is Denied, not accepted as you have it.

Let me know if you have any questions.

I believe this worked!! Thank you so much! I may have some questions when I try to pick apart your SQL if you don't mind.

 

[cmray58]

You want to find out if a patient is certified for a drug class. Showing the patients' certs isn't necessary for that, so I didn't do it. You can search this forum for cancatenating field values into one field. Allen Brown has a formula, but again, showing it isn't necessary for the logic.

Here's the logic for what you want. First create a sub-query using [Patient Certs] and [Drug-Cert Crosswalk] (life would be easier if you eliminated spaces and hyphens in your table names). Here is the SQL for that sub-query:

SELECT [Patient Certs].ID, [Patient Certs].Cert, [Drug-Cert Crosswalk].DrugClass
FROM [Drug-Cert Crosswalk] INNER JOIN [Patient Certs] ON [Drug-Cert Crosswalk].Cert = [Patient Certs].Cert;

Name that query '_sub1'. It gets all the Drug classes a patient is certified for. Then to get the SUGGESTIONS you use this SQL:

 SELECT [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted") AS SUGGESTION
FROM [Patient Prescriptions] LEFT JOIN _sub1 ON ([Patient Prescriptions].DrugClass = [_sub1].DrugClass) AND ([Patient Prescriptions].ID = [_sub1].ID)
GROUP BY [Patient Prescriptions].ID, [Patient Prescriptions].DrugClass, IIf(IsNull([Cert]),"Denied","Accepted");

Then again, to show all the certs of a patient search this forum for Allen Brown's function.

Lastly, I'm pretty sure there was an error with your OUTPUT tab. My query shows that ID=3, DrugClass=A1D is Denied, not accepted as you have it.

Let me know if you have any questions.

Hi Plog, thank you again for helping me out in this matter! I've almost completed the database. One problem, the Output query is not allowing me to update any records. Essentially, the provider needs to go through the query, look at the suggestion, and update a separate column with their official decision. Do you know why the "recordset is not updateable"?

 

[plog]

Which query, the second one? That's an aggregate query with a LEFT JOIN, so no way making it updateable. It's generally not a good idea to update via a query anyway even if it will allow it.

You should make forms, based on tables and do edits there.

 

[cmray58]

Which query, the second one? That's an aggregate query with a LEFT JOIN, so no way making it updateable. It's generally not a good idea to update via a query anyway even if it will allow it.

You should make forms, based on tables and do edits there.

That's unfortunate...

How do you suggest I pull in the suggestion from from the query (which is based on the patientprescription table) and the provider decision field (also in the patientprescription table) into one form so the provider can see the suggestion and also update the decision field? All while still keeping a datasheet view?

 

[plog]

I would use a Dlookup. I really don't understand this from an input perspective (barely understand it to produce the query I did). So, perhaps you start a new thread in the Forms section.

 

[cmray58]

I would use a Dlookup. I really don't understand this from an input perspective (barely understand it to produce the query I did). So, perhaps you start a new thread in the Forms section.

I'm getting some errors using D-lookup. Here's my syntax:

=Dlookup("[Suggestion]", "Output", "[Auth NBR]='" & [Auth NBR] & "'")

When I run the query, the program totally freezes up and takes the query really long to run. There's only ~530 entries.

 

[plog]

That's good syntax, how many times is it being called on your form? Is it a continous form? If so, then that could be the culprit, DLookups take a while to run and if you are doing it for 530 form items its probably going to crash.