Hi All,

In AccessXP I have a need to select a random sampling from a large set
of records giving the process some guidelines as in,

from a 1000 records randomly select
50 each LOB = Auto, BOP, WC
10 each Tranaction Type = R, N, RW, C, H within each LOB

This will be done daily and the # each LOB and # each Transaction Type
would change weekly. Any ideas on how I would do that, again the key
word is 'randomly'.
thanks
bobh.

If you have a numeric key field you could do something like this:

select top 10 * from yourTbl
where LOB In ('Auto','BOP','WC')
and Tranaction_Type In ('R', 'N', 'RW', 'C', 'H')
Order By Rnd(pkID)

Note: it is not a good practice to have spaces in field names, but if
you do have a field name containing a space -- you need to enclose that
field name in sqare brackets in your sql statement.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

"bobh" <vulcaned (AT) yahoo (DOT) com> wrote

Psuedo-random numbers are the best you can do in Access.

My understanding of your question is that you want 10 of each of the 5
different transaction types for each LOB to make up the 50 total records for
the LOB. In a sample of 1,000 records, you may not have a distribution that
allows you to satisfy these criteria. What do you want to do if you have
fewer than 10 of some transactions? Do you want to fill out the 50 (if you
can) with other transaction types, or proceed with fewer than 50 for that
LOB? Does each record have a unique key?

Without trying to give you a specific answer without knowing the specific
questions, the basic approach is likely going to require several queries,
the results of which you may want to combine with a UNION or UNION ALL
query, or which you may want to process separately.

Could you explain what it is that you are trying to accomplish? Perhaps that
would help us understand what you want, or allow someone to suggest a
different approach.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

On Jun 24, 10:49*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
What I'm trying to accomplish is to get a random sampling of
transactions to audit and compile a list of results that could show
common mistakes, etc.... so transaction processing processes can be
modified to reduce said mistakes. It needs to be as random as possible
so no one feels picked on.

the numbers I used are just for example purposes, the source that I'll
be selecting from is a sqlserver table that contains 100's of
thousands of records and I've been assured that there are enough
transaction type within each lob to satisfy my small sampling. In
actuality I'll be randomly selecting at least 50 of each transaction
type for each lob. So 5 transaction types for 3 lob's will be only 750
randomly selected records and Yes the source table will have a unique
key.

I was also thinking along the lines of a union query where each union
selected one lob but I'm stuck on how to randomly select 50 of each
transaction type within the lob. I just can't seem to see what that
query or vba code looks like.
bobh.

Hi Bob,

Here is a sample Tsql statement you could write directly against the sql
server table -- you would invoke this query ADO against the sql server

This requires a reference to the

"Microsoft ActiveX Data Object 2.5 (or higher) Library"

from a code module in Access/Tools/References

------------------------------------------------------
Private Sub GetRandomRecordsFromSqlServer()

Dim cmd As New ADODB.Command
Dim RSado As New ADODB.Recordset, RSdao As DAO.Recordset
Dim strSql As String, i As Integer

strSql = "select top 50 * from theSqlTblOnTheServer " _
& "where LOB In ('Auto','BOP','WC') " _
& "and Tranaction_Type In ('R', 'N', 'RW', 'C', 'H') " _
& "order by NewID()"

'--If using Windows integrated security agains sql server
'--use this connection string
'cmd.ActiveConnection = "Provider=SQLOLEDB; " _
& "DataSource=NameOfyourSqlSvr;Database=NameOfSqlSvr DB;" _
& "Trusted_Connection=Yes"

'--for sql server security use this connection string
cmd.ActiveConnection = "Provider=SQLOLEDB; " _
& "DataSource=NameOfyourSqlSvr;Database=NameOfSqlSvr DB;" _
& "UID=Steve;password=tiger;"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText = strSql

Set RSdao = CurrentDB.OpenRecordset("AccessTbl")
Set RSado = cmd.Execute
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
Loop
cmd.ActiveConnection.Close
RSado.Close
RSdao.Close

End Sub
------------------------------------------------

--note: the sql statement here is a Transact Sql Statement (sql server
sql statement) not an Access Jet sql Statement. Jet sql does not have a
NewID() function.

This routine transfers 50 randomly selected records that meet the
prescribed criteria from the table on the sql server to a table in
Access that has the exact same number of fields and data types as the
sql server table.


Rich

*** Sent via Developersdex http://www.developersdex.com ***