Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Selecting Distinct or Unique records in access

Posted on 2009-05-01
8
Medium Priority
?
377 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:jclem1
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 24279155
You have to break up the query into 2 parts.

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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24279669
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24280238
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:jclem1
ID: 24281638
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24281682
Sos was the query I posted any good?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24281686
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
 
LVL 1

Author Comment

by:jclem1
ID: 24283077
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24283216
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question