Posted on 2013-12-04
A table in my database, just the columns that matter for this example.
Field Name Field Type
Some date might be:
PropertyID DefaultAddr Active Owner
1111111 -1 -1 John Smith
1111111 0 -1 Bob Smith
2222222 0 0 John Jones
2222222 0 -1 Larry Jones
2222222 -1 0 Joe Glary
2222222 0 0 Andy Tows
-1 = Y in a YN field, 0 = No in a YN field
The datebase rules are supposed to be 'one and only one address on a property is the default'. Unfortunately I have no control over the integrity of the data I am working with and I've found the rule has not been enforced.
Every property does have at least one address, this has been enforced.
What I want to do:
Create a query returning one and only one address for a property.
If any address is a default I want that to be the one presented.
If none are selected as a default I want one of the 'Active' addresses selected.
If none are default or active it really doesn't matter which one is presented.
In the example above I would want my result query to contain:
1111111 John Smith -1 -1
2222222 Joe Glary -1 0
I've tried several query variations to get hwta I want but none return the correct result.
I did a query grouping PropertyID with Min on Default, Active and Name
I figured this would pull the any default to the top, if no default was found the active would be pulled to the top and the name would go along with it.
That's not how it worked out. The Min function just selected the lowest value in each column for each property. If there were any defaults for a property that column contained -1, if there were any actives that column contained a -1 and the name which sorted to the top alphabetically was always selected.
In the above example I'd end up with
1111111 -1 -1 Bob Smith
2222222 -1 -1 Andy Tows
Now I see why that didn't work but I'm stumped as to how I can get it to work the way I want.
I really don't see a way to do it in a query. Is it possible?