Total Criteria when you get three results using First and Last

  • Thread starter Thread starter jrcruzr
  • Start date Start date
J

jrcruzr

2007 - Total criteria: How will I extract "Mid" after using First and Last
criteria?
example:
Group a field typ and the results showed three types: 1, 2, and 3.
Used First and Last, but I need to get type 2.

Thanks in advance,
 
Are you trying to determine the Median value (that value which lies
physically in the middle)?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
MGFoster,

Thank you so much! I tried the steps you suggested and It worked!!

Jeff Boyce - Thanks for your response too!
--
rcruz


MGFoster said:
jrcruzr said:
2007 - Total criteria: How will I extract "Mid" after using First and Last
criteria?
example:
Group a field typ and the results showed three types: 1, 2, and 3.
Used First and Last, but I need to get type 2.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

FIRST() and LAST() are rather useless. Use the TOP predicate and the
MIN(). Since you don't show your SQL I'll have to use an example from a
Microsoft example database:

qryTop2:

SELECT DISTINCT TOP 2 [Order Details].Quantity
FROM [Order Details]
ORDER BY [Order Details].Quantity DESC;

qrySecondHighest:

SELECT Min(qryTop2.Quantity) AS MinOfQuantity
FROM qryTop2;

The query qryTop2 gets the top 2 Quantity from table "Order Details"
using the TOP 2 predicate. Then the query qrySecondHighest uses the
MIN() function to get the 2nd row in the qryTop2 result set. Since
there are only 2 rows in qryTop2's result set and it is sorted
descending, the 2nd row will be the lowest quantity (the MIN value),
which gets picked up by qrySecondHighest.

You can do the same with your field types to get the 2 type.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZtGIIechKqOuFEgEQJ+4gCfVESuFBcnMBDsQjNWsDYw7DvODBoAoKKC
fZULAUldfj7BYudAtPrAMl7b
=Z1ro
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top