Exclude Duplicate Records with Criteria

[Guest]

I have a table where I need to exclude certain records in a query or macro.
I only want to keep records for each PatID that have events within a day.
And yet I need to keep one record where a PatID has all Null values within a
day. Here are some examples:

Example #1: I need to keep the two records that have 'PB' and 'PB' events
and eliminate the null value. Events takes precedence for a PatID within the
same day over the null records.
PatID Date ReqNo SeqNo Event
26409 6/6/06 10:51 AM [NULL] [NULL]
26409 6/6/06 1:23 PM 6996234 1 pb
26409 6/6/06 1:23 PM 6996234 1 pl

Example #2: Since this PatID has all Null values within the day, I only
need to keep one. I could keep the max date (but it doesn't really matter).
6438375 6/6/06 7:17 PM [NULL] [NULL] [NULL]
6438375 6/6/06 7:28 PM [NULL] [NULL] [NULL]
6438375 6/6/06 7:57 PM [NULL] [NULL] [NULL]
6438375 6/6/06 9:59 PM [NULL] [NULL] [NULL]
6438375 6/6/06 9:59 PM [NULL] [NULL] [NULL]
6438375 6/6/06 10:53 PM [NULL] [NULL] [NULL]

So I need to write a query or macro that can do this for me and I am not
sure how I can accomplish that. So I need to focus on a unique PatID by each
day in my logic. Help!!

I will then use this query in a report.

 

[Guest]

Hi Vicki

I think you can try to use the following query:

SELECT DISTINCT PatID, Date, ReqNo, SeqNo, Event
FROM Table2
WHERE ReqNo IS NOT NULL AND SeqNo IS NOT NULL AND Event IS NOT NULL
UNION ALL
SELECT PatID, MAX(Date), ReqNo, SeqNo, Event
FROM Table2
WHERE PatID NOT IN (SELECT DISTINCT PatID FROM Table2 WHERE ReqNo IS NOT
NULL AND SeqNo IS NOT NULL AND Event IS NOT NULL)
GROUP BY PatID, ReqNo, SeqNo, Event;

Hopefully it works.

Lucas