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

LVL 1
jclem1Asked:
Who is Participating?
 
SharathData EngineerCommented:
try this. I assume that you dont have CURRENT STATUS starting with I or later letters.
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
WHERE tbl_Top_25_main.[CURRENT STATUS] = (SELECT MAX(t2.[CURRENT STATUS]) FROM tbl_Top_25_main t2 WHERE tbl_Top_25_main.[TKT#] = t2.[TKT#])
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

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You have to break up the query into 2 parts.

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

0
 
rockiroadsCommented:
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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SharathData EngineerCommented:
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?
0
 
jclem1Author Commented:
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.
0
 
rockiroadsCommented:
Sos was the query I posted any good?
0
 
jclem1Author Commented:
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
0
 
rockiroadsCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.