Query using Head of Household

Options
Robin Kagimoto
Robin Kagimoto ✭
Tenth Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
I am trying to create a query to group all of my parents at our school. If the parents are married, I want to count them as one record using head of household. We also have single parents with no spouse, so they do not have the head of household option. I need to include them in the query too. We have constituents of Parent primary, and Parent Secondary. They need to have criteria of constituent "date to" equals blank, so I will get only current parents. How do I build this query to get all my current parents with some being head of households and some single parents?
Tagged:

Comments

  • Karen Stuhlfeier
    Karen Stuhlfeier ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Why don't you use an export to make a list by household?
  • Gina Gerhard
    Gina Gerhard ✭
    Ancient Membership Facilitator 3 Photogenic
    Robin - I think some of this comes down to a more clear definition of what you do in query and what you do in export.
    • Sounds like you have a clear way to pull in all the parents to your school.
      • In query, you are successfully creating a group of ALL the parents.
    • In RE, Head of household is really considered an EXPORT function, not a query function.  
      • Out of all the parents, I now only want to export information out about the head of household (for those that have joint parents) as well as the single parents.
      • In your export using the HOH processing, you would then output the 'subset' of parents you want to consider - the HOH parents only and the single parents. 
    Anyone else have a different take on this?
  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    I second Karen's suggestion.  Query is a grouping tool.  You use it to group the parents.  Then go to Export function (not just export the query) and set your HOH processing options and you should get a nice clean list.
  • Wayne Pozzar
    Wayne Pozzar ✭
    Ancient Membership Facilitator 1 Photogenic
    Agreed about using Export "Only include HoH" for this case but I wanted to add a couple of comments.



    1)  The export HoH only isn't 100% because it will actually include non-hoh records if the hoh was not also in the query results.  I have been burned by that before!



    2)  I guess technically HoH is not for grouping but we use it quite often since Query results are so useful and quick.  Especially when dealing with soft credits it can be very handy to have an easy way of excluding non-hoh records in a query.  The way we do it here is to have a constituent code called "Not HoH" that gets automatically added to records that are not the primary spouse.  It comes in handy quite often but it does require some automation or at lease a reqular global change.
  • Gina Gerhard
    Gina Gerhard ✭
    Ancient Membership Facilitator 3 Photogenic
    Wayne - One comment on the 1) item you mention in your post.



    In the way HOH functionality is defined, it only pertains to the situation when BOTH spouses are in the query.  
    • If both the HOH and non-HOH record are in the query and you choose to use HOH processing in export, it will only look at the record which is marked as HOH. 
    • If only one of them is in the query, then HOH processing does not apply.  
    • If only the non-HOH record is in the query, then yes it will pick it.
    I do believe it is working 100% correctly because that complies with the definition of HOH functionality.
  • Wayne Pozzar
    Wayne Pozzar ✭
    Ancient Membership Facilitator 1 Photogenic
    Technically it is working correctly but I think most people assume that the word "only" means that it will specifically exclude non-hoh records even if the hoh is not in the query.  I know I assumed that so I just wanted to make it known in case others did as well.  I guess I'm saying that it's not readily apparent that if only one of the records is in the query that the HOH processing doesn't apply.
  • Robin Kagimoto
    Robin Kagimoto ✭
    Tenth Anniversary First Reply
    Thank you everyone for your feedback. I am trying to run the canned Statistical report. I am trying to find out our parent participation. So I need to include a query of all my parents. That's why I am trying to figure what to put in my query. I have tried multiple ways, but am still not able to come up with the group I want, whcih is Head of Household first, then single parents.
  • Gina Gerhard
    Gina Gerhard ✭
    Ancient Membership Facilitator 3 Photogenic
    Robin -



    I think you can use your Export to get the HOH's and single parents -- and then create an Output query from your Export.

    Then you can feed the output query into your report.
  • Wayne Pozzar
    Wayne Pozzar ✭
    Ancient Membership Facilitator 1 Photogenic
    Gina, how do you create an output query from an export?



    Robin, going back to your original question, what are "Parent Primary" and "Parent Secondary"?



    Just in case it helps, if you want to find everyone who is either HoH or single you can use the following criteria (notice the parentheses)



    (Spouse->Relationships->Individuals->This Individual Is The Head of Household: equals "Yes"

    OR Spouse->Spouse Information->Constituent ID: blank)

    AND <other criteria like constituent codes etc.>

     
  • Gina Gerhard
    Gina Gerhard ✭
    Ancient Membership Facilitator 3 Photogenic
    Sorry - I mispoke (or mistyped!) - you have to do it in Mail, not Export.

    This is a bit of a 'trick' of the system - we're going to export out in Mail but all we really care about is the output query.
    • In Mail, pick Quick Letters as an example.
    • On the General tab
      • Pull in your query (with the Include button).
      • There's also a Head of Household Processing section - make your selection here. 
    • Check the Filters, Attributes and Segment tabs to make sure you don't have anything weird selected.
      • Don't worry about the address tabs - because we're only going to create a query here, we're not worried about the output.
    • Go back to the General tab and in the lower left corner there's a checkbox for 'Create output query'.
    • Choose Export and name the export file and choose to save it somewhere (like your desktop) - you're going to get rid of it anyway.
    • Then you'll get a box that says 'Save Static Query' - name it and save it somewhere in your query folders.
      • THIS IS YOUR OUTPUT QUERY that will have the HOH processing that you defined.  So it should have pared down your records somewhat and eliminated the HOH duplicates (if there were any in your original query).
      • You can then use this static query in another export or report. 
      • Just remember it's static - so you have to pay heed to the precautions about refreshing static queries.
    Blackbaud may have some more elegant instructions in their User manuals -- but this can be VERY HANDY in some weird scenarios (like stated in this thread above) where you want to use a Blackbaud process (like HOH) to adjust your data, and then use it elsewhere.



    Mail has some other of these 'special' functions that are basically hidden gems -- and they're only available in Mail, not Export.

    There are some IdeaBank suggestions out there for voting - please support with your votes:




     
  • Robin Kagimoto
    Robin Kagimoto ✭
    Tenth Anniversary First Reply
    Thnak you everyone! 

    Wayne- We use the Parent Secondary constituent code if the Parent is a Step-parent, or if they don't have a valid address. That group is small. Most are Parent Primary, my issue was making sure I pulled Head of Household first and not the Spouse's record, and then if they were single parents, I needed them as well. I ended up talking with someone at Blackbaud, and we figured out how to do it. Just in case anyone wants to know, I ended up doing a merged query.

    My first query was going to group all married current parents, and pull only the Head of Household record.

    It looked like this:

    Constituency Code one of 1) Parent Primary 2) Parent Secondary

    AND Constituency Date to blank

    AND This Individual Is the Spouse equals YES

    AND This Individual Is the Head of Household equals NO



    Second Query: (To pull all my single parents)

    Constituency Code one of 1) Parent Primary 2) Parent Secondary

    AND Constituency Date to blank

    AND Spouse Name is blank



    Then Merge the two queries using the OR Factor.



    Thank you!

     
  • Wayne Pozzar
    Wayne Pozzar ✭
    Ancient Membership Facilitator 1 Photogenic
    You could do it with the criteria I suggested in one query if you wanted.



    Just add the constituency code criteria afterwards like:



    (Spouse->Relationships->Individuals->This Individual Is The Head of Household: equals: "Yes"

    OR Spouse->Spouse Information->Constituent ID: blank)

    AND     Constituency Code: One of: Parent Primary, Parent Secondary

    AND Constituency Code->Date To: blank
  • Angela May
    Angela May ✭
    Ancient Membership Facilitator 1 Photogenic
    Hi Robin, while I agree with the others who posted about export, I DO have a way to do it in query, because I rely heavily on query outputs for many things. This returns exactly what I need (including the households with only one parent) with no duplicates.



    Constituent Query Criteria: (in my database, every parent has the same code: Current Parent, date to blank)

    Constituent Code equals Current Parent

    AND Date To blank

    AND Spouse Name not blank

    AND Spouse This individual is the Head of Household equals YES

    OR Constituent Code equals Current Parent

    AND Date To blank

    AND Spouse Name blank

     
  • Robin Kagimoto
    Robin Kagimoto ✭
    Tenth Anniversary First Reply
    Hi Emily,



    Thanks for your suggestion! I tried using the query this way, but I found out it was missing my parents that had a spouse, and the Spouse was not a constituent. Some of our parents each have their own record and are each constituents, and some parent records have spouse's that are not constituents. The merged query that Blackbaud gave me seems to be working. I am still going through our parents and trying to clean up that constituency code.



    Thank you,

    Robin
  • Wayne Pozzar
    Wayne Pozzar ✭
    Ancient Membership Facilitator 1 Photogenic
    Robin, if you use my query it should include parents with non-constituent spouses as well.
  • Aldera Chisholm
    Aldera Chisholm ✭
    Ancient Membership Facilitator 2 Photogenic
    Rob, your query makes a lot of sense to me, the issue I would have was running a list of current donors, and if I excluded by HOH, I'd lose the people who were 'single' and only get people who were part of a household. So this would solve that. When you then go into export, on the first page where it has HOH processing, would you select Export Only HOH, Export First Const Found, or Export Both Separately?



     (Spouse->Relationships->Individuals->This Individual Is The Head of Household: equals: "Yes"

    OR Spouse->Spouse Information->Constituent ID: blank)



     
  • Robin Kagimoto
    Robin Kagimoto ✭
    Tenth Anniversary First Reply
    Hi Aldera,

    When I export my merged query, I check the box "Export Only HOH", but I still get all my single people. When your run the query, and you see how many people are in your group, you will get that same amount when you export using "Export only HOH". Hope that helps!:)



    Thanks!

    Robin

Categories