Prev: Limited amount of characters displayed in report.
Next: printing a report without blank pages after a record in Access 2007
From: bobh on 23 Jun 2010 11:03 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.
From: Rich P on 23 Jun 2010 14:17 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 ***
From: Access Developer on 24 Jun 2010 22:49 "bobh" <vulcaned(a)yahoo.com> wrote in message news:6a74bb16-046a-42be-8bac-083ad4b75686(a)m39g2000vbm.googlegroups.com... > 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 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
From: bobh on 25 Jun 2010 12:10 On Jun 24, 10:49 pm, "Access Developer" <accde...(a)gmail.com> wrote: > "bobh" <vulca...(a)yahoo.com> wrote in message > > news:6a74bb16-046a-42be-8bac-083ad4b75686(a)m39g2000vbm.googlegroups.com... > > > 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 > > 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 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.
From: Rich P on 28 Jun 2010 11:33
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=NameOfSqlSvrDB;" _ & "Trusted_Connection=Yes" '--for sql server security use this connection string cmd.ActiveConnection = "Provider=SQLOLEDB; " _ & "DataSource=NameOfyourSqlSvr;Database=NameOfSqlSvrDB;" _ & "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 *** |