FIRST, LAST...any other options?

[Patrick A]

All,

I have a crosstab query that gets the FIRST ClientName in a set of
records, and a crosstab query that gets the LAST ClientName of a set
of records.

I need to also get the MIDDLE ClientName from the set of records (or
the NEXT TO LAST or the NEXT TO FIRST).

Is that a possibility?

If so, would someone please tell me how?

Thanks,

Patrick

 

[Arvin Meyer [MVP]]

Which criteria would you use to determine which client name is the middle?
For most purposes, assuming that you have a sequential number as the
ClientID, you could find the average ID and use that as a criteria for the
middle client name.

 

[Patrick A]

Arvin,

Thanks for the reply.

When I try that, Access tells me I can't have an aggregate function in
a where clause. Maybe I'm putting the logic in the wrong place.

Here's my query without the "average criteria":

TRANSFORM First(D3_QRY_Proxies_C.RelAndName) AS FirstOfRelAndName
SELECT D3_QRY_Proxies_C.CMNum
FROM D3_QRY_Proxies_C
GROUP BY D3_QRY_Proxies_C.CMNum
PIVOT D3_QRY_Proxies_C.Role;

Here's my query with it, that returns the error. I inserted the blank
line here to show the WHERE statement. There are no blanks in the
actual SQL:
TRANSFORM First(D3_QRY_Proxies_C.RelAndName) AS FirstOfRelAndName
SELECT D3_QRY_Proxies_C.CMNum
FROM D3_QRY_Proxies_C

WHERE (((D3_QRY_Proxies_C.MyAddNum)=Avg([MyAddNum])))

GROUP BY D3_QRY_Proxies_C.CMNum
PIVOT D3_QRY_Proxies_C.Role;

Any further suggestion?

Thanks,

Patrick

 

[Douglas J. Steele]

Untested:

TRANSFORM First(D3_QRY_Proxies_C.RelAndName) AS FirstOfRelAndName
SELECT D3_QRY_Proxies_C.CMNum
FROM D3_QRY_Proxies_C
WHERE MyAddNum IN (SELECT Avg([MyAddNum]) FROM D3_QRY_Proxies_C)
GROUP BY D3_QRY_Proxies_C.CMNum
PIVOT D3_QRY_Proxies_C.Role;

 

[Patrick A]

Thanks Doug, I'll try that out.