[dyednwool]

Hi everyone -

I've been working (for months) on the Addresses section of our Procedures manual and have learned way more than I ever wanted to know about addresses, linking and what you can/cannot do!

Anyway, I'd love anyone's comments on addresses that appear on constituent relationship records (when there's an associated constituent record not just the relationship).

From what I've been able to figure out (after much testing and questions to BB) is that the relationship address does NOT necessarily pull the preferred address from the constituent record. When you create the relationship, it's typically linked at the time to the constituent's preferred address but if you ever change the preferred address (and don't use the Copy Preferred to Alternate) then the relationship address will not pick up the change to the address on the constituent's record.

So OK - that stinks -- BUT my question for you is: Is there a way you can create a query that will find all the records that have relationships where the relationship address indicator is NOT preferred? I'm trying to find those relationships that are out of sync with the Preferred address. In query, I'm not seeing the ability to get the Indicator field (just the address type).

Not sure what I'd do with this query once I got it anyway -- is there a global way to unlink/relink addresses to refresh them???

Thanks ---

[SLewis]

I haven't found a way to do that but if you do I would love to know how.

[mwittman]

If you're ready for some good times with Excel, what about exporting (via Query or Export) based on 2 queries:

1) Individual Constituents Query, output Constituent ID, Preferred Address fields

2) Relationship Query, output Constituent ID, Individual Constituent's Preferred address fields.

Now with Excel, do some sorting and formula comparison magic using the Constituent ID as the key and compare one or more Preferred Address fields.

Something like this:

Make sure you sort by Constituent ID, then Address fields before propagating
the formula column.

CONSTID   ADDRESSLINE1        CITY           MISMATCH TEST RESULT
22555     77 Westside Ave     Anytown        FALSE
22555     77 Westside Ave     Anytown        FALSE
22555     77 Westside Ave     Anytown        FALSE
22555     77 Westside Ave     Anytown        FALSE
22555     77 Westside Ave     Anytown        FALSE
68516     100 Main St         Nowheresville  MISMATCH ADDRESS
68516     9933 Eastern Cres   Crosstown      FALSE
87112     8585 Southland Rd   Farside        FALSE
87112     8585 Southland Rd   Farside        FALSE

Staring with the first data row, the formula I used in Column D, "MISMATCH TEST RESULT" IS:

=IF(A2=A3,IF(B2<>B3,"MISMATCH ADDRESS",FALSE))

Proprogate that down so next row is:
      =IF(A3=A4,IF(B3<>B4,"MISMATCH ADDRESS",FALSE)) 
and so on

More robust checking can start to get easier in MS Access or a similar tool, but Excel can often quick and dirty get things done.

This is a concept, because I haven't thoroughly tested what happens with Pref Address data on Relationship queries, but there you go.

[dyednwool]

Micah - Thanks for this solution. I never remember that there are relationship queries -- and this is a way to get the data out of relationships that I can't get out of a constituent query. I'll give it a try right now......

[mwittman]

Quote:

Originally Posted by dyednwool

Micah - Thanks for this solution. I never remember that there are relationship queries -- and this is a way to get the data out of relationships that I can't get out of a constituent query. I'll give it a try right now......

You're most welcome!