Prev: Using checkbox to edit criteria
Next: crosstab query with datea parameters
From: Rebeca on 8 Apr 2010 17:42
I've created a random record query that works. It pulls 50% of
records
for a particular timeframe. The problem is that I don't want random
records, I actually just want it to pull 50% of records for the
timeframe based on another criteria ([prac name]). The reason I want
to pull the same records each time is it will take more than one day
to perform the audits.

Any suggestions???

Here is my SQL.
SELECT TOP 50 PERCENT EncountersToAudit.ID, EncountersToAudit.[Prac
Name], EncountersToAudit.[Created By], Staff.[last name]+", "+[first
name] AS FullName, EncountersToAudit.[Enc Dt],
EncountersToAudit.Encounter, Rnd([encounter]) AS RandomValue,
EncountersToAudit.[Pat Name], EncountersToAudit.[Chkin Dt],
EncountersToAudit.[Crt Dt], JobDescription.[Job Title]
FROM JobDescription LEFT JOIN ([Staff Query] LEFT JOIN
EncountersToAudit ON [Staff Query].FullName = EncountersToAudit.
[Created By]) ON JobDescription.JID = [Staff Query].[Job Title]
GROUP BY EncountersToAudit.ID, EncountersToAudit.[Prac Name],
EncountersToAudit.[Created By], Staff.[last name]+", "+[first name],
EncountersToAudit.[Enc Dt], EncountersToAudit.Encounter,
Rnd([encounter]), EncountersToAudit.[Pat Name], EncountersToAudit.
[Chkin Dt], EncountersToAudit.[Crt Dt], JobDescription.[Job Title],
Year([Enc Dt])*53+DatePart("ww",[Enc Dt])
HAVING (((JobDescription.[Job Title])="front office" Or
(JobDescription.[Job Title])="float staff") AND ((Year([Enc
Dt])*53+DatePart("ww",[Enc
Dt]))=Year(Date())*53+DatePart("ww",Date())-1))
ORDER BY Rnd([encounter]);
From: John W. Vinson on 8 Apr 2010 23:26
On Thu, 8 Apr 2010 14:42:49 -0700 (PDT), Rebeca <rwfrhs(a)gmail.com> wrote:

>The problem is that I don't want random
>records, I actually just want it to pull 50% of records for the
>timeframe based on another criteria ([prac name]).

You'll need to sort by some other field, then - perhaps an Autonumber. It'll
need to be some field with a value that's static from day to day.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Using checkbox to edit criteria
Next: crosstab query with datea parameters