Generating a report containing a random 10% of all records - Access ADP project.

Does this cut it?

SELECT TOP 10 Percent myTable.*, rnd(myFldDate) as AnyNo, FROM myTable ORDER BY rnd(myFldDate);

Replace myTable and myFldDate with your actual table and field names.

Sorry forgot the exclusion

SELECT TOP 10 Percent myTable.*, rnd(myFldDate) as AnyNo, FROM myTable ORDER BY rnd(myFldDate)
WHERE CSRep <> & Forms!myFormName!cboCSReps and myFldDate BETWEEN [EnterStartDate] AND [EnterEndDate];

Usually for reports I create a Stored Procedure using the Access GUI and then pass the parameters (CSR Name, Date, in this case) to the SP.

If I approached this report the same way, then where would I put this code?

Or is there a better way ??

What is your stored procedure for all the records.  BTW,I see I should have not used BETWEEN, but rather, =

SELECT TOP 10 Percent myTable.*, rnd(myFldDate) as AnyNo, FROM myTable ORDER BY rnd(myFldDate)
WHERE CSRep <> & Forms!myFormName!cboCSReps and myFldDate = [EnterDate];

I haven't created the Stored Procedure yet. Usually I create one in the Access GUI, but then you can't add addtional SQL statements like the one you wrote above.

I don't play with ADP but what if replaced a table name with the query, or query name?

Well... Hopefully, there is another way. I am limited in experience with ADP as well.

The way I outlined doing it would mean:

The report must call the Stored Procedure because it has to pass the parameters

The Stored procedure could look at the actual table, or another make-table, view or query

But I don't see how you could use your SQL statement as a separate query. Your code needs to be run against the results of the stored procedure, or as part of the stored procedure. And because the report passes the parameters to the stored procedure, you can't put another query in between.

Could I write a stored procedure entirely in SQL, and then add your code to it ?

Yes, I am a newbie.

I just realized that I can create a stored procedure in text only. When I create the sp with the wizard, it starts me off with this:

CREATE PROCEDURE "StoredProcedure1"
/*
      (
            @parameter1 datatype = default value,
            @parameter2 datatype OUTPUT
      )
*/
AS
      /* SET NOCOUNT ON */
      RETURN

Here is the SQL from a visual-based sp that accomplishes everything that I need to do EXCEPT the random 10% selection:

SELECT     RAID, RADate, RADate, CSR
FROM         dbo.RAMain
WHERE     (RADate >= @ReportBeginDate) AND (RADate <= @ReportEndDate) AND (CSR = @CSRName)

So how can I wrap up my query, with your randomization suggestion, and the Sql wizard stuff ino something I could test?

I tried this, but could not save the stored procedure because Access says:

ADO error: "rnd" is not a recognized function name

Here is the statement:

CREATE PROCEDURE "spRandomByCSR"
/*
      (
            @ReportBeginDate datatype = date,
            @ReportEndDate datatype = date
            @CSRName datatype = char
      )
*/
AS
      SELECT TOP 10 Percent RAID, RADate, RADate, CSR, rnd(dbo.RAMain) as AnyNo
      FROM dbo.RAMain
      WHERE (RADate >= @ReportBeginDate) AND (RADate <= @ReportEndDate) AND (CSR = @CSRName)


      /* SET NOCOUNT ON */
      RETURN

ADO does not recognize the Rnd() function??  I Googled - rnd() ms sql 2000 - and got several hits
Have a look at:  http://www.adopenstatic.com/faq/randomrecord.asp

Ok... Tried the code below, but still can't save the SP because Access is now giving me the error:

ADO error: Must declare the variable 'ReportBeginDate'

How do I declare this variable ?

**********************************
CREATE PROCEDURE "spRandomByCSR"
/*
(
      @ReportBeginDate datatype = date,
      @ReportEndDate datatype = date
      @CSRName datatype = char
)
*/
AS

SELECT TOP 10 Percent RAID, RADate, RADate, CSR
FROM dbo.RAMain

WHERE (RADate >= @ReportBeginDate) AND (RADate <= @ReportEndDate) AND (CSR = @CSRName)

Order by NewID()

/* SET NOCOUNT ON */
RETURN

I put this in Query analyzer and it worked perfectly:

SELECT TOP 10 Percent RAID, RADate, RADate, CSR
FROM dbo.RAMain

Order by NewID()

Now I just need to figure out what I'm doing wrong with the parameters.

This looks useful:  http://www.tek-tips.com/viewthread.cfm?qid=1306082&page=1

Not sure what you wanted me to look at on that page, maybe the different datatype idea??

Maybe its syntax. Why does SQL think @ReportBeginDate is a variable, when it is a parameter.

Can you only pass parameters in when the SP is in graphical format, and not text?

I think that something is wrong in this spot:

This is the default text in the stored procedure when you create it in Access:
/*
     (
          @parameter1 datatype = default value,
          @parameter2 datatype OUTPUT
     )
*/

This is that section of my query:

/*
(
     @ReportBeginDate datatype = date,
     @ReportEndDate datatype = date
     @CSRName datatype = char
)
*/

Well, I kind of solved the problem on my own last night. I stumbled on the soulution because I just deleted everything out of my sp text except the basic query - and it worked. Now, reading LPurvis's post, I realize that I was commenting out my declarations!!! I did not know that.

I was only using QA to test the procedure. Actually, once I deleted these...

/*
    (
    )
*/

and

/* SET NOCOUNT ON */
RETURN

...the sp turned itself into back into a graphical sp and worked fine.

LPurvis's code is just about what I ended up with, except his is a text sp, while mine is taken from Access's gui.

Here is my final SQL code that works:

SELECT     TOP 10 PERCENT RAID, RADate, CSR
FROM         dbo.RAMain
WHERE     (CSR = @CSRName) AND (RADate >= @ReportBeginDate) AND (RADate <= @ReportEndDate)
ORDER BY NEWID()

Here's my two cents for future readers of this post:

You can sort by "Top 10 Percent" or whatever other "Top" you want by right-clicking the graphical sp and choosing properties.

You can add a random sort by typing in "NEWID()" in the first column and then making that the primary sort type.

Thanks to both of you, I'll split the points bewteen.