Difficult Sort

B

Bob Barnes

I have a Club membership DB, where a RespMem (Responsible Member) has other
Members (w/ different MemberIDs) assigned to the RespMem.

The RespMem has the HomePhone and Email in his record. Members who are NOT
RespMems have BLANK HomePhone and BLANK Email.

I'm able to get all appropriate Members w/ their RespMem, but have to sort
on the RespMem ID...

RespMem TheMember HomePhone Email
125 Wills, Scott 859-555-1234 [email protected]
125 Wills, Grant
125 Smith, Patty
126 Wise, Joan 859-555-2345 [email protected]
127 Press, Tony 859-555-3456 [email protected]
127 Press , Deb
128 Roberts, Joe 859-555-4567 [email protected]
128 Roberts, Sandy
128 Roberts, Tara
128 Roberts, YT

The Client wants to keep members w/ their RespMem BUT sort alphabetical by
the RespMem. Solution ..somehow conatenating and sorting?? Ideas please.
TIA - Bob..desired result..

RespMem TheMember HomePhone Email
127 Press, Tony 859-555-3456 [email protected]
127 Press , Deb
128 Roberts, Joe 859-555-4567 [email protected]
128 Roberts, Sandy
128 Roberts, Tara
128 Roberts, YT
125 Wills, Scott 859-555-1234 [email protected]
125 Wills, Grant
125 Smith, Patty
126 Wise, Joan 859-555-2345 [email protected]
 
J

John Spencer

So you need to sort by those individuals who do have a phone number

If the list is in order by RespNum then you should be able to use the
following order by

ORDER BY RespNum, HomePhone Desc, TheMember

It is a bit more difficult if your are sorting by the member name.

It can be done depending on your table structure.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
B

Bob Barnes

John - I agree this will "force" all the RespMembers to the top, but I've
thinking is there a way to join the non-RespMembers to sort directly below
each RespMember?

Thank you - Bob
 
J

John Spencer

I need to know more about your table structure. Using the fields you
have posted, I might try

QueryA: Identify primary member
SELECT RespMem, TheMember, HomePhone
FROM Members
WHERE HomePhone is Not Null

QueryB:
SELECT MB.*
FROM QueryA as MA INNER JOIN Members as MB
ON MA.RespMem = MB.RespMem
ORDER BY MA.TheMember, MA.RespMem, MA.HomePhone, MB.TheMember

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
B

Bob Barnes

John - I'm not sure the best way to do this.

I do know that the Query Result I had sorting on RespMem was beautiful,
until the Client said it had to be alphabetical like a Phone book...only
problem, that NOT everybody in the "Doe, John family" has the same LastName.

One of my other Clients has a guy who's great at sorting Excel files. I had
him work on it after I automated it to Excel..he said he'll explain the logic
to possibly get the same result thru Access Queries..another thing I have on
my List to develop other "tools" for my Access Toolbag.

Thank you for helping think more on this - Bob
 
J

John Spencer

So. Are you saying that my solution did not work or that you did not
understand how to implement it?

I think that it should work IF I understood your table structure and the
values stored in the fields.

Does the first query return only those records where the individual is
the Primary member (Responsible Member)?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
B

Bob Barnes

John - Yes, I understand what you sent. Query A was fine, and part of the
2nd Query was fine. The "Corporate Memberships" presented a problem.

Your assistance helped me finetune what I had done on Friday. Even w/ the
help of my friend who sorts Excel so well, I still had issues...mostly
related to Data-entry "issues" where the Client didn't have me set better
Traps.

They wanted essentially an alphabetical "PhoneBook" list, and they have that
now based on the LastName of the RespMember, even though other members
assigned to that RespMember can have different Lastnames.

Thank you again for your help - Bob
 
Top