How to use a subquery

  • Thread starter Thread starter Danny
  • Start date Start date
D

Danny

Howdy.

I want a list of donors, with their 3 most recent
donations. I am very confused as to how to get the top
three donations records for each of the donors. Does this
require a subquery? How do I construct such a subquery?

Thanks for your help.

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

Something like this:

SELECT D.DonorID, D.Donation
FROM Donations As D
WHERE DonationID IN (SELECT TOP 3 DonationID
FROM Donations
WHERE DonorID = D.DonorID
ORDER BY Donation DESC)
ORDER BY D.DonorID, D.Donation

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQSOt5IechKqOuFEgEQKULgCeKJHG5mfafbgtL+IB6TqkjSBahIkAn0TQ
leTJS8CNNLXlfA7na5RvZRqY
=bmvl
-----END PGP SIGNATURE-----
 
Yes but note that the copy of the Table Donations used in
the main Query has been aliased to "D".

HTH
Van T. Dinh
MVP (Access)
 
Gotcha - thanks.
-----Original Message-----
Yes but note that the copy of the Table Donations used in
the main Query has been aliased to "D".

HTH
Van T. Dinh
MVP (Access)




.
Click to expand...
 
Thank you for the help. I used this SQL statement, with
some modifications of course, and it DID bring up the data
I was after. It took a REALLY LONG TIME to run, though (I'm
using Access 2003 GUI with MySQL backend). Is that usual?
 
This is what we call correlated SubQuery because the SubQuery is executed
for each Record / Row in the main Query, i.e. for each Record in your Table.
Thus it will take a bit of time, even if the Back-End is JET rather than
MySQL.

You may be better off using Pass-Through Query where the processing is done
on the MySQL server and only the selected rows are returned to your
work-station. The only problem is that you have to translate the SQL String
to MySQL SQL syntax.

Check Access Help on the Pass-Through Query.
 
Back
Top