Random lists PRINT identical items daily

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a problem with a replicated database.
We were attempting to generate a report containing 10 random line items from
a table. Apparently, 5 different users on different terminals print the same
list of items in the same sequence everyday. When the form is generated 5
times on a terminal today, the same 5 reports are generated in the same order
tomorrow.

A query was created with the methods identical to those described in the
common Access user resource pages. A report was created to pull the data
from the query, so that each time the report was ran, a new query would
supply new data.

The report shown on the screen is not the same that is printed. The screen
report appears to be working properly but the actual report printed is not.

Why is this and how is this corrected?
Thanks,
John
 
I have a problem with a replicated database.
We were attempting to generate a report containing 10 random line items from
a table. Apparently, 5 different users on different terminals print the same
list of items in the same sequence everyday. When the form is generated 5
times on a terminal today, the same 5 reports are generated in the same order
tomorrow.

A query was created with the methods identical to those described in the
common Access user resource pages. A report was created to pull the data
from the query, so that each time the report was ran, a new query would
supply new data.

The report shown on the screen is not the same that is printed. The screen
report appears to be working properly but the actual report printed is not.

Why is this and how is this corrected?
Thanks,
John
Click to expand...

Please post your code. It sounds like the report Preview and the report
printed are using different queries. Are you calling Randomize() to rescramble
the random number algorithm?
 
The code is pasted below:

--
SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line = StandardWorkAudits.Line
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
HAVING (((Line.AuditGroup)="Vinyl"))
ORDER BY Rnd([StdWrkID]);
--
 
The code is pasted below:

--
SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line = StandardWorkAudits.Line
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
HAVING (((Line.AuditGroup)="Vinyl"))
ORDER BY Rnd([StdWrkID]);
Click to expand...

This will always give the same series of random numbers for a given StdWrkID,
since you're using the same random number seed. Try using this function:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Rather than using Rnd([StdWrkID]) use RndNum([StdWrkID]) and see if that gives
you better randomization.
 
Passing the same value to Rnd means the same sequence of pseudo-random
numbers will be generated each time.

Take a look at http://www.mvps.org/access/queries/qry0011.htm at "The Access
Web" for how to randomize the calls.

Incidentlly, your HAVING clause should really be a WHERE clause. WHERE
clauses get evaluated prior to grouping taking place, so less data is
involved. HAVING clauses should be reserved for times when your selecting is
based on the results of the grouping. (Yes, I know Access does this
wrong...)

SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line =
StandardWorkAudits.Line
WHERE Line.AuditGroup="Vinyl"
AND Randomizer() = 0
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
ORDER BY Rnd(IsNull[StdWrkID]) * 0 + 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John said:
The code is pasted below:

--
SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS
Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line =
StandardWorkAudits.Line
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
HAVING (((Line.AuditGroup)="Vinyl"))
ORDER BY Rnd([StdWrkID]);
--

John W. Vinson said:
Please post your code. It sounds like the report Preview and the report
printed are using different queries. Are you calling Randomize() to
rescramble
the random number algorithm?
Click to expand...
Click to expand...
 
"Randomizer()" is not a recognized function.
Nor is "RndNum()".

The WHERE clause did not make a difference. The query does generate random
numbers and created new lists each time but the REPORT that pulls the
information from the query prints the same lists during each session of
Access. What is on the screen is not what prints. What is on the screen
seems to be correct.
 
You need to copy the definition of Randomizer from the webpage I cited.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John said:
"Randomizer()" is not a recognized function.
Nor is "RndNum()".

The WHERE clause did not make a difference. The query does generate
random
numbers and created new lists each time but the REPORT that pulls the
information from the query prints the same lists during each session of
Access. What is on the screen is not what prints. What is on the screen
seems to be correct.

Douglas J. Steele said:
Passing the same value to Rnd means the same sequence of pseudo-random
numbers will be generated each time.

Take a look at http://www.mvps.org/access/queries/qry0011.htm at "The
Access
Web" for how to randomize the calls.

Incidentlly, your HAVING clause should really be a WHERE clause. WHERE
clauses get evaluated prior to grouping taking place, so less data is
involved. HAVING clauses should be reserved for times when your selecting
is
based on the results of the grouping. (Yes, I know Access does this
wrong...)

SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS
Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line =
StandardWorkAudits.Line
WHERE Line.AuditGroup="Vinyl"
AND Randomizer() = 0
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
ORDER BY Rnd(IsNull[StdWrkID]) * 0 + 1)
Click to expand...
Click to expand...
 
The code is pasted below:

--
SELECT TOP 10 StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]) AS Decider,
Line.AuditGroup
FROM Line INNER JOIN StandardWorkAudits ON Line.Line = StandardWorkAudits.Line
GROUP BY StandardWorkAudits.StdWrkID, StandardWorkAudits.Line,
StandardWorkAudits.Operation, Line.ValueStream, Rnd([StdWrkID]),
Line.AuditGroup
HAVING (((Line.AuditGroup)="Vinyl"))
ORDER BY Rnd([StdWrkID]);
Click to expand...

That's not the code. That's the SQL of your query.

You have some code, or a macro, or something that is launching the report. It
appears that it's doing it one way to Print, and a different way to Preview,
which is why you're seeing different results.

And please reread my other post. You're correct: there is no builtin function
named Rndnum. That's why I GAVE you a function named RndNum and instructions
for how to implement it.
 
Back
Top