Complicated report generate problem

[Pid]

Ok I'm fairly new to Access, so this may sound really easy to some, or perhaps not. So basically this is what I want to do. Let me give you some background info first.

I have a database with 2 tables. One contains records of items and the other is records of catagories. Each item is assigned to a specific catagory.

I have a form. The user uses this form to select 1 or more catagories, and types in a number in a box and clicks a button. This then produces a Report.

Say, for example, the user has selected Catagory B and Catagory C and Catagory F and chosen a quanitity of 5... The report will list Catagories B, C and F and list 5 Item records under each of those catagories.

So it would be

Catagory B
Item 1
Item 2
Item 3
Item 4
Item 5

Catagory C
Item 43
Item 44
Item 45
Item 46
Item 47

Catagory F
Item 32
Item 33
Item 34
Item 35
Item 36


All this works completely fine. So here's the hard bit which I'm asking all your help for... When the user clicks the generate report button, I want all the record results to be random. So under each catagory they list, there are X number of randomly-selected records but obviously only for that catagory. Is this actually possible?

Any help would be a life-saver.

Thanks!!!

 

[grnzbra]

OK, here goes nothing.
First of all, check Help for rnd()
Then check Help for info about modifying a query during runtime (actually, it's just a matter of changing the query's SQL at runtime.

Create a Select Distinct query that selects only the category field from the table in which the customer checks the item's categories. This will give you a list of checked categories.

Create a query that selects all items of a particular category and include a calculated field equal to a random number using the rnd() function. (Note: I believe that the rnd() function is really a pseudo-random function in that it will always give the same sequence of "random" numbers. Check the rnd() Help info for ways to create a truly random number. I believe it will involve multiplying the generated number by something that is truely random such as now()) and sort on this number. Then make it a Top 5 query.

Then create an Append query to append the output of this query to an output table.

Using VBA,

Delete all records from the output table.

Start looping through the records in the first table (List of categories selected)

Inside the loop, set a string variable (let's call it strSQL) equal to the SQL statement for the second query you created but without the WHERE clause.
Using the list of selected categories, set the WHERE clause string variable (let's call it whc) equal to the first category on the list.

Concatinate the strSQL and the whc variables to create a complete SQL statement and set the SQL for the second query equal to that SQL statement.

Run the third query (the append query)

Move to the next record in the list of categories selected and loop through the Categories Selected list until you reach the end of the list.

Then run your report from the output table.

That's an off the top of my head idea of a possible way to solve your problem, but I expect you'll have to play with it a little.