Re: Query to Select Maximum Age

Try using a Group By query instead of using DISTINCT. Group By ptnum,
sex, address, MAX of age. That should give you one record per patient,
unless your data is screwy.

johnds wrote:
Another basic question from newbie. I have a 10 million record database
of patient contact with their physician. The original file contained
all thier clinical information and personal characteristics. Each
patient record was repeated for each visit. I only want a table of
unique records for each patient with their characteristics, simple
enough with select distinct, but I found that I obtained duplicate
records if the patient visited their doctor between birthdays. I have
decided to take the record with their max age as more current. The
following however did not work, any suggestions?

SELECT DISTINCT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

.

Relevant Pages

  • Re: Query to Select Maximum Age
    ... GROUP BY ptnum, age, sex, address ... That should give you one record per patient, ...
    (comp.databases.ms-access)
  • Re: Query to Select Maximum Age
    ... SELECT ptnum, age, sex, address ... GROUP BY ptnum, age, sex, address ... That should give you one record per patient, ...
    (comp.databases.ms-access)
  • Query to Select Maximum Age
    ... patient record was repeated for each visit. ... decided to take the record with their max age as more current. ... SELECT DISTINCT ptnum, age, sex, address ...
    (comp.databases.ms-access)
  • Ping Charlie
    ... age and sex of the patient. ...
    (sci.med.dentistry)
  • Re: Time to failure problem
    ... With some risk of sounding pigheaded, I've read this several times this ... and with a starting age ~Nand simulate for each patient, given ... I am further assuming that each patient lives to 100." ... His prob of living until age 67 is ...
    (sci.stat.math)
Loading