asked on

Selecting Distinct or Unique records in access

I am having difficulty obtaining distinct records in the below access query. I am trying to produce a top 25 list of oldest tickets and I am getting duplicate ticket numbers because each ticket can have more than 1 status in the current status field.

How to I rectify this problem?

I get an output like this:

TKT#      DATE REC      TIME REC      REPORT CAT      RESPONSIBLE MCO      CURRENT STATUS
AC647743      01/17/09      11:44      CR      BAPADEACCCT      NA
AC648815      01/22/09      13:14      CR      BAPADEACCCT      HDC
IC166856      01/22/09      23:01      CR      NWRKNJNWMIC      NA
PK147172      02/18/09      17:08      CR      PDECSCNTR05      NA
PK147653      02/25/09      00:35      CR      PDECSCNTR05      NA
PK147655      02/25/09      00:41      CR      PDECSCNTR05      NA
AC657391      03/04/09      11:58      CR      BAPADEACCCT      NA
PK149167      03/16/09      09:57      CR      PDECSCNTR05      NA
AC660148      03/18/09      17:57      CR      BAPADEACCCT      DM
AC660148      03/18/09      17:57      CR      BAPADEACCCT      HDC

SELECT DISTINCT TOP 25 tbl_Top_25_main.[TKT#], tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC], tbl_Top_25_main.[REPORT CAT], tbl_Top_25_main.[RESPONSIBLE MCO], tbl_Top_25_main.[CURRENT STATUS]
FROM tbl_Top_25_main
GROUP BY tbl_Top_25_main.[TKT#], tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC], tbl_Top_25_main.[REPORT CAT], tbl_Top_25_main.[RESPONSIBLE MCO], tbl_Top_25_main.[CURRENT STATUS]
HAVING (((tbl_Top_25_main.[CURRENT STATUS]) Is Not Null))
ORDER BY tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC];

Open in new window

Kyle Abrahams, PMP

You have to break up the query into 2 parts.

First get the 25 oldest tickets, then get information on those tickets.

rockiroads

So is it the case you want one row per date and from that you want the oldest entry for that date?
If so then you can try this

SELECT TOP 25 FIRST(tbl_Top_25_main.[TKT#]) AS Tickt, tbl_Top_25_main.[DATE REC], FIRST(tbl_Top_25_main.[TIME REC]) as TimeRec, FIRST(tbl_Top_25_main.[REPORT CAT]) AS ReportCat, FIRST(tbl_Top_25_main.[RESPONSIBLE MCO]) AS ResponsibleMCO, FIRST(tbl_Top_25_main.[CURRENT STATUS]) AS CurrentStatus
FROM tbl_Top_25_main
WHERE tbl_Top_25_main.[CURRENT STATUS] Is Not Null
GROUP BY tbl_Top_25_main.[DATE REC]
ORDER BY tbl_Top_25_main.[DATE REC]

Basically the fields you grouped by will determine the recs displayed, this is probably your problem

Sharath S

In your sample set,
TKT# DATE REC TIME REC REPORT CAT RESPONSIBLE MCO CURRENT STATUS
AC647743 01/17/09 11:44 CR BAPADEACCCT NA
AC648815 01/22/09 13:14 CR BAPADEACCCT HDC
IC166856 01/22/09 23:01 CR NWRKNJNWMIC NA
PK147172 02/18/09 17:08 CR PDECSCNTR05 NA
PK147653 02/25/09 00:35 CR PDECSCNTR05 NA
PK147655 02/25/09 00:41 CR PDECSCNTR05 NA
AC657391 03/04/09 11:58 CR BAPADEACCCT NA
PK149167 03/16/09 09:57 CR PDECSCNTR05 NA
AC660148 03/18/09 17:57 CR BAPADEACCCT DM
AC660148 03/18/09 17:57 CR BAPADEACCCT HDC
Both these records have same TKT# and DATE REC. which record you want and why?

jclem1

ASKER

Sharath 123

You are correct both have the same TKT# and DATE REC, I would prefer to have the entry with the "HDC" because it means the trouble ticket is out of my shop being worked, however the DM means that time is not accruing on the ticket, for the purposes of this list I would want to show "HDD" or "HDC" or any function that starts with "H". I would want the "H" functions to have priority over everything.

rockiroads

Sos was the query I posted any good?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jclem1

ASKER

rockiroads

The query you posted ran, however getting back to the question you asked, I don't want to limit output to 1 row per date, the date can repeat, I just want the top 25 oldest tickets. The ticket numbers were repeating because each ticket could have multiple status's, so I needed to limit to 1 entry per ticket.

Sharath 123

I think you solved the problem, I get the outcome I was after, takes a few seconds for the query to run but it works great.

Thanks to both of you

rockiroads

Its a pity you gave feedback much later after I posted the sql otherwise the sql I posted could of been tweaked to do what you want. Well nevermind.