Number of times switched

[joe789]

Hi Folks,

I am trying to find out how many times a person switched from Medicaid to Non-Medicaid Status and visa-versa for a non-profit government agency. I have filtered the data to show three fields: Client_Indentifier, Effective_Date, and Medicaid_Status (either flagged as MCD or NON). Each row is unique, and the data is sorted by Client_Identifier and then by Effective_Date. In attempting to arrive at the number of times an individual has switched between Medicaid (MCD) and Non-Medicaid (NON) Status, the problem is that sometimes Medicaid (MCD) spans are found back-to-back since the data must be sorted by Effective_Date and it offers occurs that there is a lapse in spans when dealing with Medicaid. I would like to ignore incidences where Medicaid spans are back-to-back, as the individual never truly switched between Medicaid/Non-Medicaid but just had a break of service in his/her Medicaid span. Is there any way to do this? Any help would be greatly appreciated. If I can't use a query to do this, would code be able to do it?


Thank you,

Joe

 

[dcx693]

You can do this in a query, but it's probably best to use code. That way, you can produce the result using a custom function and pass the result to queries as a calculation.

In the function, I would open the data as a recordset, then go through the data line by line to see which coverage the user started out with, then I'd note every time the coverage flipped from MCD to NON or from NON to MCD. (What do you do if the user has two back-to-back spans of NON? Could that happen?)

 

[joe789]

Thanks dcx,

I just looked at the data again, and found records with 'NON' back-to-back also. So what I would have to do, I think, would be to somehow use a query or code to:

I have already created a table that display a row count for the number of lines by identifier - 1 because the very first span per identifier cannot be counted as a switch.

So I would take the above table, and subtract from it the total number of times a MCD or NON was back-to-back, by identifier.

I think that would work.

Thanks

 

[dcx693]

I guess your logic would work. You can either calculate the total spans minus back-to-back spans and subtract 1 or you could go through the recordset and just count actual switches. My way just sounds more straightfoward, but whatever works.