How can I randomize a report?

[chubby]

I was wondering is there a way to randomize a report so each time the end-user opens the report it's different. I have an "Zone Inspection Database" containing 500 rooms broken down into 16 zones. Each week, one zone is picked for inspection. How can I make my report randomize between zones each time the report is ran? Right now the report uses a parameter query and the end user manually selects a zone.

Example:
Zone 1 Zone 2 zone 3
bath room "A" bath room "C" bath room "D"
bath room "1" bath room "2" bath room "3"
bath room "4" bath room "5" bath room "6"

Run report Monday 08, 2004
Zone 3
bath room "D"
bath room "3"
bath room "6"
Run report Monday 15, 2004
Zone 1
bath room "c"
bath room "2"
bath room "5"

 

[dhookom]

Do you actually have Zone 1, Zone 2, ... as field names? The zone should be a data value in a field named "Zone". Can you normalize your data?

Otherwise, you could try something like:
SELECT tblInspections.ID,
Choose(Int(Rnd()*3)+1,[Zone1],[Zone2],[Zone3]) AS TheZone
FROM tblInspections;

You would have to change the 3 to the number of zones and of course add more zones in the Choose().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.