Isolating attributes on a query

Dian Myers · September 2017

I'm not sure if the subject explains accurately what I'm trying to do.

We have alumni who leave before completing a program. We document those incompletes under the education attributes. Attributes are given each time they attend, so some may have 2-3 completed programs, an incomplete discharge, and then another completed program after the incomplete. I need those alumni who have ONLY incompletes to NOT be included in my query. Does anyone have any idea how I might accomplish this?

Jen Claudy · September 2017

My first instinct is to use a Merge Query. Write a Query of those with the Attribute. Then write a query of records with only Incompletes. Then use a Merge Query and SUB out the second query from the first. (There's a text link at the bottom of the [Merge Queries] window that gives good explanations of the different Merge Operators.)

Joanne Felci 2 · September 2017

I'd probably do it is a merged query

First query would be all records with that attribute equaling incompletes

Second query would be all records with that attribute having a value of anything but incompletes

Then merge having the primary query being the first query (incompletes), secondary query as the other query and use the operator of XOR

I believe that will then weed out people that have the attribute on their record with other values then just incomplete

I'm sure the query gurus will correct me

I've used merge queries for AND operator but I'm assuming XOR is the one you would want to use

Joanne Felci 2 · September 2017

Jen Claudy:

My first instinct is to use a Merge Query. Write a Query of those with the Attribute. Then write a query of records with only Incompletes. Then use a Merge Query and SUB out the second query from the first. (There's a text link at the bottom of the [Merge Queries] window that gives good explanations of the different Merge Operators.)

OK - what Jen said

So, Jen what does XOR operator do?!

Melissa S Graves · September 2017

Merge operator explanations: https://kb.blackbaud.com/articles/Article/39180

Jen Claudy · September 2017

Essentially (the RE Help page includes graphics, which are helpful), this is what the options do:

AND = records that are in both queries (order of the queries does not matter)

OR = records that are in either query (order doesn't matter)

XOR = records that are in either query, but not both queries (order doesn't matter)

SUB = records in the first query that are not in the second query (the order of the queries matters in this one!)

Joanne Felci 2 · September 2017

ok I clicked the link to find out about the different operators and obviously XOR is not the right one to use - though I still don't understand why you would ever use that

Honestly I don't trust queries for something that is more complicated like this - I'd try a query then do an export to make sure that the results are what I thought they should be

That might be the easiest way actually

Do one query with everyone with the attribute = incomplete

do an export (from the export tab, not an export from the query) including that attribute with the value of incomplete and a duplicate column of that attribute with values that are not incomplete then sort on the second attribute column and delete all those rows

Dian Myers · September 2017

Dian Myers:
I'm not sure if the subject explains accurately what I'm trying to do.

We have alumni who leave before completing a program. We document those incompletes under the education attributes. Attributes are given each time they attend, so some may have 2-3 completed programs, an incomplete discharge, and then another completed program after the incomplete. I need those alumni who have ONLY incompletes to NOT be included in my query. Does anyone have any idea how I might accomplish this?

Thank you all for your input, I appreciate the ideas. After spending the evening mulling it over in my head and looking at your ideas this morning, I think we're going to have to do something different to "tag" the incomplete only, so we can easily separate them from the mixed complete and incomplete. There doesn't seem to be any easy and efficient way to make this happen. I think the ideas are good, but too burdensome for the rest of the staff to bother working through, and I'm not always available to pull a query for them. Again, thank you all for your time and thoughts on this matter!

Faith Murray · September 2017

This may be a silly question, but in the Education record, does it not have a "Graduated" date or a "Status" field? All of our graduates have a graduated date and a "Graduated" Status so to find those with no completed course, we would simply query on "Graduated date = blank" and/or "Status does not equal Graduated". Never used Attributes for this.

Dian Myers · September 2017

Faith Murray:

This may be a silly question, but in the Education record, does it not have a "Graduated" date or a "Status" field? All of our graduates have a graduated date and a "Graduated" Status so to find those with no completed course, we would simply query on "Graduated date = blank" and/or "Status does not equal Graduated". Never used Attributes for this.

Hmm, interesting. I've gone into admin and added Status because that actually makes more sense than using relation codes in the attributes. I still don't see how we can pull the incompletes only. Our alumni aren't actually students, they're patients. We're a treatment center, and they often have a few incomplete attempts before (or after) finishing a program. Once they've completed an entire program, they're considered full alumni, even though they may have prior (or future) incomplete attempts. So we're trying to keep those who've never come back to complete a program from being pulled on a query, while keeping those who have completed at least one program ON the query. If that makes sense. I like your idea of using the status though - it really makes more sense overall. Thank you!

Alan French · September 2017

I've just been off for a week so there's a real possibility that my brain could have gone to sleep here, but if you want to exclude people who have only "incompletes" on their record, is that not the same as looking for people who have at least one "complete", which could be done without using merge queries?

Heather MacKenzie · September 2017

I wonder if solicit codes might be the way to go? You could then query for everyone, and subtract (merging queries, using a query list, etc.) the solicit code for the incompletes? Just my 2 cents.

Isolating attributes on a query

Comments

Categories