Select Record Query

[Rebeca]

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]);

 

[KARL DEWEY]

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.

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]);
.