I am trying to complete a query in Access.
Let's suppose the table looks like this:
ID First Name Last Name Prefix Name Type
123 Susan Jones '89 Ms. 18
123 Susan and John Jones '89 Mrs. and Mr. 23
234 Sandy Berger '90 Ms. 18
567 Alan Johnson Mr. 15
567 Alan and Amy Johnson Mr. and Mrs. 37 Note that not every ID number has every name type, but every ID will have at least one name type. Only one name type needs to be returned. The order of preference for the name types is as follows:
If name type 23 exists, return this. If name type 23 does not exist, look for name type 37 and return type 37. If name type 37 does not exist, look for name type 18 and return type 18. If type 18 does not exist, look for type 15 and return this.
Can someone more experienced than I with sql help with a query that would accomplish this?
TYIA,
Susan