Ranking non-numerical query results

  • Thread starter Thread starter TLuebke
  • Start date Start date
T

TLuebke

Emails are stored using a student ID, a code of either P for personal or T
for institutional and the corresponding email address. Sometimes they have
one or the other or often both. I would like to query out just one email
address per student ID with the T addresses having precedence over the P
addresses.

How do I go about this?
 
Need more information such as table and field names with datatype.
Post SQL of a query you would use to extract the e-mail information.
Do you have a field for e-mail type that stores a 'P' or 'T'?
 
Perhaps

SELECT StudentID, EmailAddress
FROM SomeTable
WHERE
Code: 
 in
(SELECT Min([Code]) FROM
SomeTable as Temp
WHERE Temp.StudentID = SomeTable.StudentID)

IF that is too slow and your field and table names consist of ONLY
letters, numbers, and underscore characters (no spaces), then you could
use a sub-query in the FROM clause.  This type of query cannot be built
in design view and must use the SQL view.

SELECT StudentID, EmailAddress
FROM SomeTable as A INNER JOIN
(SELECT StudentID, Min([Code]) as TheCode
FROM SomeTable
GROUP BY StudentID) as B
ON A.StudentID = B.StudentID
AND A.[Code] = B.TheCode

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
SELECT tblSumBalanceDue.CUSTOMER_ID, INTERNET_ADDR.INTERNET_ADDR_TYPE,
INTERNET_ADDR.INTERNET_ADDR
FROM tblSumBalanceDue LEFT JOIN INTERNET_ADDR ON
tblSumBalanceDue.CUSTOMER_ID = INTERNET_ADDR.Customer_ID;

Thats the basic SQL. All the fields are stored as text. I'd like to get
records where INTERNET_ADDR_TYPE ="P" have precedence over INTERNET_ADDR_TYPE
="T"
in the case where the Customer_ID has both types.
 
One post you said "...with the T addresses having precedence over the P"..."
and another "...where INTERNET_ADDR_TYPE ="P" have precedence over
INTERNET_ADDR_TYPE ="T"..."

This has "P" as primary. There is more than one way to do it, this uses
several queries --

P_INTERNET_ADDR ---
SELECT INTERNET_ADDR.Customer_ID, INTERNET_ADDR.INTERNET_ADDR
FROM INTERNET_ADDR
WHERE INTERNET_ADDR.INTERNET_ADDR_TYPE = "P";

T_INTERNET_ADDR ---
SELECT INTERNET_ADDR.Customer_ID, INTERNET_ADDR.INTERNET_ADDR
FROM INTERNET_ADDR LEFT JOIN P_INTERNET_ADDR ON P_INTERNET_ADDR
..CUSTOMER_ID = INTERNET_ADDR.Customer_ID
WHERE ((P_INTERNET_ADDR .CUSTOMER_ID) Is Null);

P_T_INTERNET_ADDR
SELECT INTERNET_ADDR.Customer_ID, INTERNET_ADDR.INTERNET_ADDR
FROM P_INTERNET_ADDR
UNION
SELECT INTERNET_ADDR.Customer_ID, INTERNET_ADDR.INTERNET_ADDR
T_INTERNET_ADDR;

SELECT tblSumBalanceDue.CUSTOMER_ID, INTERNET_ADDR.INTERNET_ADDR_TYPE,
INTERNET_ADDR.INTERNET_ADDR
FROM tblSumBalanceDue LEFT JOIN [P_T_INTERNET_ADDR] ON
tblSumBalanceDue.CUSTOMER_ID = [P_T_INTERNET_ADDR].Customer_ID;
 
Back
Top