Access Query Issue

[thtadthtshldntbe]

Hello,

I am creating a report in Access and as part of the query I am making a series of calculated fields, from which I will get my totals.

There are several calculated fields.

Essentially this report will look at about 20 different fields in a database, and then produce a monthly CSection rate per physician. Part of what makes this complicated is that I have to exclude certain patients from the count. So I am making have the total deliveries calculated field (Total Del) and then DelwExcl as another field.

In simple mechanics, if the record is excluded, this field should return a value of 0. (and then I add up the remaining values for the total).

I have space this out a bit to make it more readable.

Basically, it works fine, so long as I do not included the underline, bolded switch option. I also need to add a couple more switch options so I am kind of flumoxed. The field name that it is looking at is correct (and is visible)

DelwExcl:
CInt
(Switch
((Not IsNull([Infant Delivery Date, Baby B DateTime])),0,
[Gestational Age at Deliv Baby A]<37,0,
[CSection Incidence]="Repeat",0,
[CSection, Primary Indication]="tvrsCmpl",0,
[CSection, Primary Indication]="Macro",0,
[CSection, Primary Indication]="abrplac",0,
[CSection, Primary Ind Other]="*obl*",0,
[CSection, Secondary Indication]="abrplac",0,
[Matecomp-Placenta Previa]="placprev",0,
[Matecomp-Abruptio Placenta]="abr_plac",0,
[Matecomp-Preeclampsia]="Preecl",0,
[Intrapartum Maternal Complicatio]="placprev",0,
[intrapartum maternal complicatio]="abr_plac",0,
[VBAC Baby A]=”Success”,0,
(Not IsNull([Method of Delivery Baby A])),1))

 

[KLin]

Would help if you posted an error message screenshot, and even a link to an access db file with the table(no data or sample data) and query in question for others to assist in troubleshooting.

 

[thtadthtshldntbe]

Would help if you posted an error message screenshot, and even a link to an access db file with the table(no data or sample data) and query in question for others to assist in troubleshooting.

I have commented out most of the statements in the startup procedure, the only thing it will do automatically is load the canned reports form, just exit it.

https://drive.google.com/file/d/0B7T2xcfjh34kbk5qa0RIM2RDYUE/view?usp=sharing

Caveat 1 - I am not a database engineer perse, just someone who has learned how to use it, queries reports etc, between classes and practical need, but I am willing to learn of course.

Caveat 2 - I have been trying to avoid VBA, in part due to the fact that its been like 9 years since I did anything with it at all and in part because, if they approve my capital plan, I will get an addon on product wherein the EMR that this data comes from will be autoexported into a full SQL database (meaning every query I try to build in this thing, I try to make full possible in SQL so I can adapt it as I learn more (I can do basic stuff in SQL, at this point).

So with that in mind.

If you open that database and look for the report titled

"Stat - Csection by Provider by Month minus Exclusion

That is derived from a working and more simple report

"Stat - Csection by Provider by Month".

Those exclusions represent maternal deliveries which have complications that mean we can exclude them from reporting in various stats. For example, if a mother has placenta previa, she has to have a csection, etc.

In this report I am trying to create a calculated field in the query for each particular reported stat.

I am working with the most complicated one first, Total Deliveries minus exclusions. What makes it kind of complicated although I am slowly altering the data entry into the EMR itself to solve this, is that these exclusions are documented all over the fucking place and in many case freetexted with several "standard" (meaning shorthand that everyone uses though not necessarily official shorthand) abbreviations. I have begun to make those complications checkboxes instead, so they are more easily searchable.

Anyway, when I first started these queries (this whole statistics gig from this proved by the vendor birthlog was something that sort of fell on me because my bosses found out that I knew my way around access at I guess you would say an intermediate level) I wrote them and would add exclusions in as they came up, and then just manually get lists of patients from several different queries and combine by hand if it was too complex. But I want to simplify that. In part because as my stats job grows, that takes too much time and in part because in the addon product, you can program in the whole thing and automate it.

So, I first came up with a series of iif statements that if an exclusion existed, then it would return 0, if none existed the chain would return 1 and then I would add up the total for that column (and thanks to you Klin, I have been using that way to do this sort of thing since you first mentioned it a year or so ago in a prior question).

I ran into the fact that there is a limit on nested iif statements.

So trying to keep it SQL, and avoid VBA I came up with this (space for clarity)

[FONT=&quot]DelwExcl:[/FONT]
[FONT=&quot]CInt[/FONT]
[FONT=&quot](Switch[/FONT]
[FONT=&quot]((Not IsNull([Infant Delivery Date, Baby B DateTime])),0,[/FONT]
[FONT=&quot][Gestational Age at Deliv Baby A]<37,0,[/FONT]
[FONT=&quot][CSection Incidence]="Repeat",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="tvrsCmpl",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="Macro",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="abrplac",0,[/FONT]
[FONT=&quot][CSection, Primary Ind Other]="*obl*",0,[/FONT]
[FONT=&quot][CSection, Secondary Indication]="abrplac",0,[/FONT]
[FONT=&quot][Matecomp-Placenta Previa]="placprev",0,[/FONT]
[FONT=&quot][Matecomp-Abruptio Placenta]="abr_plac",0,[/FONT]
[FONT=&quot][Matecomp-Preeclampsia]="Preecl",0,[/FONT]
[FONT=&quot][Intrapartum Maternal Complicatio]="placprev",0,[/FONT]
[FONT=&quot][intrapartum maternal complicatio]="abr_plac",0,[/FONT]
[FONT=&quot](Not IsNull([Method of Delivery Baby A])),1))[/FONT]

I have several more exclusions to add. I want to get to this (There are potentially more exclusions in more places to add to this, some are really rare and the PI person just mentions them to me when they come up. She used to do this whole process by hand, going through every chart).

[FONT=&quot]DelwExcl: [/FONT]
[FONT=&quot]CInt[/FONT]
[FONT=&quot](Switch[/FONT]
[FONT=&quot]((Not IsNull([Infant Delivery Date, Baby B DateTime])),0,[/FONT]
[FONT=&quot][Gestational Age at Deliv Baby A]<37,0,[/FONT]
[FONT=&quot][CSection Incidence]="Repeat",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="tvrsCmpl",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="Macro",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="abrplac",0,[/FONT]
[FONT=&quot][CSection, Primary Ind Other]="*obl*",0,[/FONT]
[FONT=&quot][CSection, Secondary Indication]="abrplac",0,[/FONT]
[FONT=&quot][Matecomp-Placenta Previa]="placprev",0,[/FONT]
[FONT=&quot][Matecomp-Abruptio Placenta]="abr_plac",0,[/FONT]
[FONT=&quot][Matecomp-Preeclampsia]="Preecl",0,[/FONT]
[FONT=&quot][Intrapartum Maternal Complicatio]="placprev",0,[/FONT]
[FONT=&quot][intrapartum maternal complicatio]="abr_plac",0,[/FONT]
[FONT=&quot][VBAC Baby A]=”Success”,0,[/FONT]
[FONT=&quot][Fetal Presentation Baby A] = “Compound”,0,[/FONT]
[FONT=&quot][Fetal Presentation Baby A] = “Breech”,0,[/FONT]
[FONT=&quot](Not IsNull([Method of Delivery Baby A])),1))[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]I am fairly certain that I am not missing a comma anywhere.[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]The message that I get is usually[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]"Expression to complex to evaluate" or something similar.[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]My research today tells me that either I have hit the maximum about of things you can put into a switch statement, or I am running into the maximum character limit on Access SQL statements, which is something like 1024 characters in Access 2007 (which is the version that I have).[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]I thought at first that I was running into an access 2007 bug that it has to saving things inside the query inside reports.[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]So, if I what I found is true, is my only option to write VBA code.
[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]At that point, if so, I would basically have the VBA code evaluate a series if If/then/else statements, stopping when it found an exclusion. I don't think that I can use a Select Case because there are multiple fields that I have to look into, some of which contain different data types.[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Any help is appreciated. I am always willing to learn
[/FONT]

 

[KLin]

I'm using access 2010 and the query in the report runs without any errors for me. You should try running the report in access 2010 to see what happens.

 

[KLin]

Right now your switch statement is at 768 characters. the 1024 character max is for within a cell in a query.

 

[thtadthtshldntbe]

The query with the additions like I posted in this thread? I can't actually put them in it because access won't update the property due to the "too complex to evaluate error".

Hmm, I have 2013 at home here and there is no patient data. (I can't bring the patient data home. Maybe I'll bring my laptop in tomorrow and play with the empty database on it.

 

[KLin]

Definitely give it a try it in access 2013.

 

[thtadthtshldntbe]

Definitely give it a try it in access 2013.

When I copy paste

[FONT=&quot]DelwExcl: [/FONT]
[FONT=&quot]CInt[/FONT]
[FONT=&quot](Switch[/FONT]
[FONT=&quot]((Not IsNull([Infant Delivery Date, Baby B DateTime])),0,[/FONT]
[FONT=&quot][Gestational Age at Deliv Baby A]<37,0,[/FONT]
[FONT=&quot][CSection Incidence]="Repeat",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="tvrsCmpl",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="Macro",0,[/FONT]
[FONT=&quot][CSection, Primary Indication]="abrplac",0,[/FONT]
[FONT=&quot][CSection, Primary Ind Other]="*obl*",0,[/FONT]
[FONT=&quot][CSection, Secondary Indication]="abrplac",0,[/FONT]
[FONT=&quot][Matecomp-Placenta Previa]="placprev",0,[/FONT]
[FONT=&quot][Matecomp-Abruptio Placenta]="abr_plac",0,[/FONT]
[FONT=&quot][Matecomp-Preeclampsia]="Preecl",0,[/FONT]
[FONT=&quot][Intrapartum Maternal Complicatio]="placprev",0,[/FONT]
[FONT=&quot][intrapartum maternal complicatio]="abr_plac",0,[/FONT]
[FONT=&quot][VBAC Baby A]=”Success”,0,[/FONT]
[FONT=&quot][Fetal Presentation Baby A] = “Compound”,0,[/FONT]
[FONT=&quot][Fetal Presentation Baby A] = “Breech”,0,[/FONT]
[FONT=&quot](Not IsNull([Method of Delivery Baby A])),1))

[FONT=&quot]into a ne[FONT=&quot]w field, even eliminating the extra carr[FONT=&quot]iage returns, I get the "expression to com[FONT=&quot]plex to evaluate"[FONT=&quot]. U[FONT=&quot]sually as [FONT=&quot]soon as I try anything[FONT=&quot] like checking off the bo[FONT=&quot]x to make it visible[FONT=&quot] for example[FONT=&quot]. I can't even get to the point of running it[FONT=&quot].[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]
[/FONT]

 

[KLin]

I think there's a limitation on the number of expressions the switch function can evaluate.

I removed the last 3 expressions and it worked.
I then took 1 of the 3 I removed and replaced the last expression and it worked.
I then added one of the other 2 I removed and it didn't work.

 

[thtadthtshldntbe]

One would think that MS would just include that in the documentation, but they don't for IIF as well. Its like they are trying to make people guess at the limitations on their crap.

So my options are

either go to VBA code and create a function

or

break up the exclusions into 2 separate fields and then run a combined total on both.

thanks for your help bro.

 

[KLin]

Try this:

1. Create a new query for LDExport
2. Put in the exclusions as criteria for each field
3. Add this new query to the report query and do an outer join to the patient id in ldexport so it shows all records in ldexport, and related records in the exclusion query.
4. Add in a new expression field like "Included: iif(IsNull(newquery.[patientid]), 0, 1)"

 

[thtadthtshldntbe]

I'm getting

"the specified field '[csection, primary indication] could refer to more than one table listed in the FROMclause of your SQL statement

the SQL statement is

SELECT LDExport.[Patient ID], [Delivery Doctor Long].[Delivery Doctor Long], LDExport.[Infant Delivery Date, Baby B DateTime], LDExport.[Infant Delivery Date Baby A DateTime], LDExport.[Method of Delivery Baby A], IIf(([CSection, Primary Indication]="tvrsCmpl" Or [CSection, Primary Indication]="PlacPrev" Or [CSection, Primary Indication]="Macro" Or [CSection, Primary Indication]="abrplac" Or [CSection, Primary Ind Other]="obl" Or [CSection, Secondary Indication]="abrplac" Or [Matecomp-Placenta Previa]="placprev" Or [Matecomp-Abruptio Placenta]="abr_plac" Or [Intrapartum Maternal Complicatio]="*placprev*" Or [intrapartum maternal complicatio]="*abr_plac*"),0,(IIf([Method of Delivery Baby A]="C/S",1,0))) AS CSec, LDExport.[CSection Incidence], (IIf([CSection Incidence]="Primary",1,0)) AS CSPri, (IIf([CSection Incidence]="Repeat",1,0)) AS CSRep, LDExport.[Gestational Age at Deliv Baby A], LDExport.[VBAC Baby A], LDExport.[Fetal Presentation Baby A], LDExport.[CSection, Primary Indication], LDExport.[CSection, Primary Ind Other], LDExport.[CSection, Secondary Indication], LDExport.[CSection, Secondary Ind Other], LDExport.[MateComp-Placenta Previa], LDExport.[MateComp-Abruptio Placenta], LDExport.[MateComp-Preeclampsia], LDExport.[Intrapartum Maternal Complicatio], LDExport.[Intrapartum Mat Comp Other], DelminusExcl() AS DelEx
FROM ([Delivery Doctor Long] INNER JOIN LDExport ON ([Delivery Doctor Long].[Delivery Doctor] = LDExport.[Delivery Doctor]) AND ([Delivery Doctor Long].[Delivery Doctor] = LDExport.[Delivery Doctor])) LEFT JOIN Exclusions ON LDExport.[Patient ID] = Exclusions.[Patient ID]
WHERE (((LDExport.[Infant Delivery Date Baby A DateTime])>=[start date] And (LDExport.[Infant Delivery Date Baby A DateTime])<[end date]))
ORDER BY LDExport.[Infant Delivery Date Baby A DateTime];

I have deleted a couple of things in the overall query to simplify this for now btw, from the version of the query that you have

 

[KLin]

yea, you need to put "[LDExport]." in front of all field names that you are selecting in the report query since you have duplicated field names in the query. The query doesn't know if you want the field from LDExport or the new exclusion query.

 

[thtadthtshldntbe]

note as an aside I am trying to work this out in VBA.

I kind of remember the basics, declaring procedures, passing values into and out of the functions, etc. What is flumoxxing me is the referencing of the database. I think that I did that correctly

Option Compare Database
Option Explicit
Dim d As Database
Dim r As Recordset

Public Function DelminusExcl() As Integer
DelminusExcl = 1
Set d = CurrentDb()
Set r = d.OpenRecordset("LDExport")
If r![Gestational Age at Deliv Baby A] < 37 Then DelminusExcl = 0
End Function

I wanted to build the VBA and add the exclusions in 1 by 1 to test.

I called it like so

DelExcl: DelminusExcl()

but when I do it, I only get 1 for every record. Its like the conditional can't see the database field. Or it cant evaluate it.

I am gonna watch some access videos this weekend, on referring to record sets. Everyone one I have seen so far, refers to manipulating the entire record set in the function, but I need it to produce a value per record.

 

[KLin]

note as an aside I am trying to work this out in VBA.

I kind of remember the basics, declaring procedures, passing values into and out of the functions, etc. What is flumoxxing me is the referencing of the database. I think that I did that correctly

Option Compare Database
Option Explicit
Dim d As Database
Dim r As Recordset

Public Function DelminusExcl() As Integer
DelminusExcl = 1
Set d = CurrentDb()
Set r = d.OpenRecordset("LDExport")
If r![Gestational Age at Deliv Baby A] < 37 Then DelminusExcl = 0
End Function

I wanted to build the VBA and add the exclusions in 1 by 1 to test.

I called it like so

DelExcl: DelminusExcl()

but when I do it, I only get 1 for every record. Its like the conditional can't see the database field. Or it cant evaluate it.

I am gonna watch some access videos this weekend, on referring to record sets. Everyone one I have seen so far, refers to manipulating the entire record set in the function, but I need it to produce a value per record.

You need to give the function the patientId.

then reference patiendid in the function itself

Public Function DelminusExcl(byval patientid as long) As Integer

DelminusExcl([PatientId]) would go in the query field.

Then do 'SELECT * FROM LDExport where PatientId = " & patientid' in the openrecordset.

 

[thtadthtshldntbe]

first of all thanks for your help.

yea, you need to put "[LDExport]." in front of all field names that you are selecting in the report query since you have duplicated field names in the query. The query doesn't know if you want the field from LDExport or the new exclusion query.

For now I deleted all the other calculated fields, just to get the point of testing. And then it worked. The only thing I had to change was the results if true and false. You had 0,1 and that returned 0 for those patients to be included. Once I switched that, I can now run totals on that calculated value over any given period, or by physician or other sorting criteria. (This query is something that will apply to several different reports). I can now, for example use this method, putting all the exclusions in that query and then referencing it in a particular calculation like for primary csections, vaginal deliveries, etc and it will return 1 for and I can total, etc. (I should name it after you, lol).

You need to give the function the patientId.

then reference patiendid in the function itself

Public Function DelminusExcl(byval patientid as long) As Integer

DelminusExcl([PatientId]) would go in the query field.

Then do 'SELECT * FROM LDExport where PatientId = " & patientid' in the openrecordset.

Ok, I get it. I had kind of assumed that Access would do this automatically. Like as it pulled each record and ran the calculation it ran it on each record.

----

Presuming this all works as intended. Once sort of general questions.

I currently work inside this access database. My original "training" years ago included both SQL and VB basics, but I turned away from software trying to get into pure hardware either IT or electronics. Then this job came along and it sucked me back into the software side of it, and I kind of enjoy it, at least the statistics part.

So, my understanding is that there is a limit on complex SQL expressions in the Jet Engine. And that the way that you work around it in Access is to use VBA.

If they approve my budgetary request, one of the things that I will get is a massive SQL database, which my EMR exports all data to every 10 minutes or so (currently we only get 10% to 25% depending on whether its a mother or baby, I have some custom pulls I do).

Should I continue to try to do things more purely in SQL and increase my knowledge and experience there of or will this VBA coding translate into something useful on that end.

The product uses Crystal Reports to configure reports from the SQL database.

 

[KLin]

It wouldn't hurt to learn VBA to help you automate exporting data, running reports, and other things. Depending on on how you want a certain task to perform, you would need to decide if it's better in VBA code or in SQL.

 

[KLin]

oh and you're welcome

 

[KLin]

first of all thanks for your help.



For now I deleted all the other calculated fields, just to get the point of testing. And then it worked. The only thing I had to change was the results if true and false. You had 0,1 and that returned 0 for those patients to be included. Once I switched that, I can now run totals on that calculated value over any given period, or by physician or other sorting criteria. (This query is something that will apply to several different reports). I can now, for example use this method, putting all the exclusions in that query and then referencing it in a particular calculation like for primary csections, vaginal deliveries, etc and it will return 1 for and I can total, etc. (I should name it after you, lol).




Ok, I get it. I had kind of assumed that Access would do this automatically. Like as it pulled each record and ran the calculation it ran it on each record.

----

Presuming this all works as intended. Once sort of general questions.

I currently work inside this access database. My original "training" years ago included both SQL and VB basics, but I turned away from software trying to get into pure hardware either IT or electronics. Then this job came along and it sucked me back into the software side of it, and I kind of enjoy it, at least the statistics part.

So, my understanding is that there is a limit on complex SQL expressions in the Jet Engine. And that the way that you work around it in Access is to use VBA.

If they approve my budgetary request, one of the things that I will get is a massive SQL database, which my EMR exports all data to every 10 minutes or so (currently we only get 10% to 25% depending on whether its a mother or baby, I have some custom pulls I do).

Should I continue to try to do things more purely in SQL and increase my knowledge and experience there of or will this VBA coding translate into something useful on that end.

The product uses Crystal Reports to configure reports from the SQL database.

oh and nope it doesn't automatically happen. You need to design it the way you want it. The way you were doing it, you were just grabbing the first record and evaluating the field. That's why you got 1's across the board.