At 01:36 PM 9/01/2005 +0000, you wrote:

>I have been asked to produce a result set to answer the following,
>
>Within the period (12-24 months) count the unique patients who have
>attended the practice and show the answer grouped by
>age (at consultation) (and period of report will change)
>gender (which probably won't change)
>locality (which will probably change)
>program (which will change)
>
>only the first consultation for each patient is counted.

If you really mean "grouped by" then you are looking for a summary count
something like this?

Age in Years Gender Locality Program Count
---------------------------------------------------------------------------
0 F A ABC 5
0 F A PQR 0
0 F A XYZ 2
0 F B ABC 2
0 F B PQR 3
0 F B XYZ 1
................
0 F Z ZZZ 2
0 M A ABC 0
0 M A PQR 7
0 M A XYZ 0
0 M B ABC 3
0 M B PQR 2
0 M B XYZ 3
................
0 M Z ZZZ 0
................
................
................
99 F Z ZZZ 0
99 M Z ZZZ 1

>Do I need to use co-related subqueries so that I count each patient
>only once?

Pretty likely, but it really depends on how the data are stored. For
example, if you are going to calculate the Age_At_Consult from the patient
record then clearly the Consult_date on a Consultation record has to be
correlated to the Date_of_birth on the patient record. Is Locality a
patient attribute or a consultation attribute? Does Program belong to
Patient or Consultation? et.c etc. You want only the "first" consultation
in the period to be counted but "first" at which level of grouping? That
affects the aggregation...and so on.

>and then retrieve from that sub-query just that information
>Age_At_Consult, Locality (at time of consult) and Program (again at
>time of consult)

Can't tell.

./hb