Distinct records within week

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Office User - 09 Jun 2005 18:17 GMT

I'm using DISTINCT to retreive unique records from a table with call data
(caller, date, support technician, stated problem). Each week the data will
be imported to Access and we're going to randomly pick callers to survey
about our service.

My first step is to make sure we don't select someone that has called more
than once in that week. Here's my SQL:

SELECT DISTINCT [May week1 test data].[User Name], DateValue([Logged Date])
AS [Date]
FROM [May week1 test data]
ORDER BY [May week1 test data].[User Name];

The problem is it doesn't see 2 entries as unique if the date is different.
Example
User Name    Date
ARDELL SEEDS LTD.    5/2/05
ARDUSER SEEDS    5/2/05
BOLLMEYER INC    5/3/05
BROWN; MIKE R    5/5/05
BROWNS    5/2/05
BUSCH; TOM    5/3/05
BUSCH; TOM    5/5/05
CARDEN SEED COMPANY    5/6/05
D & D SEED SALES    5/2/05
D & D SEED SALES    5/6/05

After this, then I'll create another query (hopefully) to pull the ticket#,
call type, technician and then randomly select about 40-50 records to send
the survey to. Since I need to pull the rest of the record info, I'm pretty
sure I need to use DISTINCT and not GROUP.

Thanks for any help,
Marcia

Reply to this Message

Gerald Stanley - 09 Jun 2005 18:42 GMT

If you want the list of users who have only called once, try something along
the lines of

SELECT [User Name]
FROM [May week1 test data]
GROUP BY [User Name]
HAVING COUNT(*) = 1;

Hope This Helps
Gerald Stanley MCSD

> I'm using DISTINCT to retreive unique records from a table with call data
> (caller, date, support technician, stated problem). Each week the data will
[quoted text clipped - 30 lines]
> Thanks for any help,
> Marcia

Reply to this Message

Office User - 09 Jun 2005 21:55 GMT

It worked to give my unique records. Now I'll see if my 2nd query can pull
all the info needed and randomize it.

Thanks for your help,
Marcia

> If you want the list of users who have only called once, try something along
> the lines of
[quoted text clipped - 41 lines]
> > Thanks for any help,
> > Marcia

Reply to this Message