Prev: Record Selection
Next: Using checkbox to edit criteria
From: Rebeca on 8 Apr 2010 17:40
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]). 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: KARL DEWEY on 8 Apr 2010 19:05
>>The problem is that I don't want random records,
Get rid of --- ORDER BY Rnd([encounter])

>>timeframe based on another criteria ([prac name])
Try this --
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]
WHERE (((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)) AND EncountersToAudit.[Prac
Name] = [Enter required Prac Name];


--
Build a little, test a little.


"Rebeca" wrote:

> 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]). 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]);
> .
>
 | 
Pages: 1
Prev: Record Selection
Next: Using checkbox to edit criteria