Distinct records within week

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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
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