From:
Steve Goodhall [SGoodhall@home.com]
Sent: Tuesday, October 31, 2000 10:06 PM
To: accessd@mtgroup.com
Subject: Thread about top 20 by country
I verified that this can be done with a correlated subquery.
I built a test DB containing 2 tables, as follows:
County table (complete): (I remembered "county" where you had "country" but the principle is the same)
CountyID
CountyName
100
County Name 100
123
County Name 123
333
County Name 333
345
County Name 345
444
County Name 444
678
County Name 678
985
County Name 985
Patient table (partial):
PatientID
PatientName
100000001 Patient
Name 100000000
100000002 Patient
Name 100000002
100000003 Patient
Name 100000003
100000004 Patient
Name 100000004
100000005 Patient
Name 100000005
100000006 Patient
Name 100000006
100000007 Patient
Name 100000007
100000008 Patient
Name 100000008
100000009 Patient
Name 100000009
100000010 Patient
Name 100000010
100000011 Patient
Name 100000011
100000012 Patient
Name 100000012
100000013 Patient
Name 100000013
For starters, I found it a problem to have the CountyID embedded in the PatientID, so I built an intermediate query, qsPatientWithCounty , as follows:
SELECT Left([Patient]![PatientID],3) AS CountyID, Patient.PatientID, Patient.PatientName FROM Patient;
The (partial) output of this query is:
CountyID
PatientID PatientName
100
100000001 Patient
Name 100000001
100
100000002
Patient Name 100000002
100
100000003
Patient Name 100000003
100
100000004
Patient Name 100000004
100
100000005
Patient Name 100000005
100
100000006
Patient Name 100000006
100
100000007
Patient Name 100000007
100
100000008
Patient Name 100000008
100
100000009
Patient Name 100000009
This makes the next steps easier:
To actually perform the selection you asked for, I built the following query, qsPatientTop5CountyT1, based on the query above.
SELECT
Patient.PatientID FROM Patient, County AS C
WHERE (((Patient.PatientID)
IN (SELECT TOP 5 qsPatientWithCounty.PatientID
FROM qsPatientWithCounty
WHERE (((qsPatientWithCounty.CountyID)=[C].[CountyID]))
ORDER BY qsPatientWithCounty.PatientID DESC)))
ORDER BY Patient.PatientID;
Obviously, with some extra work, these could be combined into a single statement. To keep it small, I only selected the TOP 5 instead of the TOP 20. You can mess with the ORDER by clauses to get what you actually need, and you can select additional columns in the outer SELECT clause.
The (partial) output of this query is:
PatientID
100000025
100000026
100000027
100000028
100000029
123000025
123000026
123000027
123000028
123000029
333000025
333000026
333000027
Incidentally, even with the small set of test data, this query is notably slow.
I will post my test database to my web site within the next hour or so.
Steve
Goodhall
mailto:SGoodhall@home.com
http:/members.home.net/sgoodhall/
Sooner or later, all this planning has to degenerate into work. - Peter Drucker
Hi folks
I have two tables.. 'Countries' and 'Patients'.
Countries
CountryID Description
-----------------------------------------
320
Belgium
100
USA
170
Canada
Patients
PatientID Name
-----------------------------------------
320012
Patient 1
320278 Patient
2
100186 Patient
3
100429 Patient
4
100863 Patient
5
170009 Patient
6
The
prime key for 'Countries' is CountryID
The prime key for 'Patients' is PatientID
The first 3 numbers of the PatientID is a CountryID
Is it possible with ONE query to have the top20 of patients FOR EACH countryID
Any help will be gladly appreciated.
Frank
Jorissen
Leuven Coordinating Centre
Department of Cardiologie
Database Administrator
Herestraat 49
3000 Leuven
frank.jorissen@uz.kuleuven.ac.be