Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding duplicate field dates (xp)

    I need to run a query that will find everyone in a table whose ADATEF (date filled) = LAST FILL DATE (most recent dispensing). I thought I could just use the duplicate query wizard but it gives me all the records. The query the wizard came up with looks like this:

    SELECT erd_not_filled_risperdal.ADATEF, erd_not_filled_risperdal.[LAST FILL DATE], erd_not_filled_risperdal.[PATIENT LAST NAME], erd_not_filled_risperdal.[PATIENT FIRST NAME], erd_not_filled_risperdal.PRXNO, erd_not_filled_risperdal.ANODAY, erd_not_filled_risperdal.[N/R], erd_not_filled_risperdal.IDESCR, erd_not_filled_risperdal.ACGADJ, erd_not_filled_risperdal.PLAN, erd_not_filled_risperdal.PRQTY, erd_not_filled_risperdal.PALQTY, erd_not_filled_risperdal.PRICE
    FROM erd_not_filled_risperdal
    WHERE (((erd_not_filled_risperdal.ADATEF) In (SELECT [ADATEF] FROM [erd_not_filled_risperdal] As Tmp GROUP BY [ADATEF],[LAST FILL DATE] HAVING Count(*)>1 And [LAST FILL DATE] = [erd_not_filled_risperdal].[LAST FILL DATE])))
    ORDER BY erd_not_filled_risperdal.ADATEF, erd_not_filled_risperdal.[LAST FILL DATE];

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding duplicate field dates (xp)

    If I understand you correctly, you are not really looking for duplicate records, just for records meeting the condition that ADATEF = LAST FILL DATE. The SQL for this is much simpler:

    SELECT erd_not_filled_risperdal.ADATEF, erd_not_filled_risperdal.[LAST FILL DATE], erd_not_filled_risperdal.[PATIENT LAST NAME], erd_not_filled_risperdal.[PATIENT FIRST NAME], erd_not_filled_risperdal.PRXNO, erd_not_filled_risperdal.ANODAY, erd_not_filled_risperdal.[N/R], erd_not_filled_risperdal.IDESCR, erd_not_filled_risperdal.ACGADJ, erd_not_filled_risperdal.PLAN, erd_not_filled_risperdal.PRQTY, erd_not_filled_risperdal.PALQTY, erd_not_filled_risperdal.PRICE
    FROM erd_not_filled_risperdal
    WHERE (erd_not_filled_risperdal.ADATEF = [LAST FILL DATE])
    ORDER BY erd_not_filled_risperdal.ADATEF, erd_not_filled_risperdal.[LAST FILL DATE];

    If you also want to return records for which both ADATEF and LAST FILL DATE are empty (null), the WHERE part becomes

    WHERE (erd_not_filled_risperdal.ADATEF = [LAST FILL DATE]) Or (erd_not_filled_risperdal.ADATEF Is Null And erd_not_filled_risperdal.[LAST FILLED DATE] Is Null)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •