[AccessD] Query problem

you could try sql

select tb1.*
from tablex as tb1
where (tb1.patient_id, tb1.DOt) IN (select tb2.patient_id, min(tb2.dot) from
tablex as tb2  group by tb2.patient_id)

or create 2 access queries
the first named qrygetfirstentry would be 

select patient_id, min(dot) as firstdt from tablex  group by patient_id

which would then be used in the second query name qrysavedemog

select tablex.*
from tablex join qrygetfirstentry on tablex.patient_id =
qrygetfirstentry.patient_id and tablex.dot = qrygetfirstentry.firstdt


HTH
Patti



> -----Original Message-----
> From: Bridget Doran [mailto:bridgetd at biostat.umn.edu]
> Sent: Thursday, April 08, 2004 01:58 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Query problem
> 
> 
> Hi all -
> 
> Working in Access2K.
> 
> I am querying non-normalized data to get records where there are some
> duplicate ID's. Each of these duplicate records will have a date field
> called DOT. I want only the record for the earlier date in 
> the cases where
> there are duplicate ID's. But I also want all the records 
> where there are no
> duplicates. How would I query for this?
> 
> The problem I am addressing is data that has both static and changing
> fields. A patient has basic demographic info as well as 
> multiple transplants
> (2 at most). What the original database did here was just 
> added another
> record with the same Patient ID and a second transplant date 
> with new info
> for many of the fields but not the static/demographic fields. 
> The first
> transplant date has the static fields.
> 
> I have only querying rights to this database and we are 
> building a research
> database that is dependent on it. So, I am attempting to build some
> normalization into our research database with queries.
> 
> Thanks for any help!
> 
> Bridget Doran
> Biostatistics Consulting Lab
> Division of Public Health
> University of Minnesota
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>