Randomly select a record in a table

Hi Guys,

How would I randomly select a record from a table that has some 2500 records?

Any help is greatly assisted!!

many thanks in adavnce!!

Cheers
0
Utf
4/18/2010 11:17:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
510 Views

Similar Articles

[PageSpeed] 17

On Sun, 18 Apr 2010 16:17:01 -0700, DontKnow
<DontKnow@discussions.microsoft.com> wrote:

>Hi Guys,
>
>How would I randomly select a record from a table that has some 2500 records?
>
>Any help is greatly assisted!!
>
>many thanks in adavnce!!
>
>Cheers


SELECT TOP 1 *
FROM Yourtable
ORDER BY Rnd([fieldname])

where [fieldname] is some Number or Autonumber field which can be counted on
to contain a positive number.
-- 

             John W. Vinson [MVP]
0
John
4/18/2010 11:37:54 PM
Beautiful John!!

Many thanks!!

Cheers!!

"John W. Vinson" wrote:

> On Sun, 18 Apr 2010 16:17:01 -0700, DontKnow
> <DontKnow@discussions.microsoft.com> wrote:
> 
> >Hi Guys,
> >
> >How would I randomly select a record from a table that has some 2500 records?
> >
> >Any help is greatly assisted!!
> >
> >many thanks in adavnce!!
> >
> >Cheers
> 
> 
> SELECT TOP 1 *
> FROM Yourtable
> ORDER BY Rnd([fieldname])
> 
> where [fieldname] is some Number or Autonumber field which can be counted on
> to contain a positive number.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
4/19/2010 12:22:01 AM
Reply:

Similar Artilces:

Sql Server hang when deleting many records
I have a sql server with many dbs. I don't get why when I delete a long running query(about 1 or 2 minutes) to complete, but while this is running, my other separate db on the same server hangs on some queries. I should note that it is Sql server 2005 on windows server 2003. Should a queries on one db locks another db? I thought it should not even lock another table on the same db. Hi How many rows are you deleting? Try this approach (small batch of 2000 rows) WHILE 1 = 1 BEGIN DELETE TOP(2000) FROM Foo WHERE <predicate>; IF @@ROWCOUNT < 2000 BREAK; ...

How do I print selected pages in Publisher booklet
Publisher printing options are only "all pages", "pages from x to x", or "current page". I want to be able to print only any pages which have been corrected, eg in 16-page booklet A5 size, with correction on page 1, I want to print pages 1 and 16 only without having to print all pages again. Turn off two page spread, print current page, click no to query. How to print even and odd pages by using VBA in Publisher http://support.microsoft.com/kb/294748/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.c...

Random
How to get random numbers random(0xff) ? int RandomNumber = (rand() * 0xff) / RAND_MAX; -- Bjarne Nielsen "]GHO[" <yousefk@taux01.nsc.com> skrev i en meddelelse news:0b6a01c3a84c$5733a890$a501280a@phx.gbl... > How to get random numbers random(0xff) ? rnd()%0x100; or rnd() & 0xff; Ruben On Tue, 11 Nov 2003 04:07:13 -0800, "]GHO[" <yousefk@taux01.nsc.com> wrote: >How to get random numbers random(0xff) ? ...

Random Numbers not Random
When I create random numbers for an application with the following code, they do not appear to be truely random. Me.RandomNumber = Int(9 * Rnd + 1) ' Random Number 1-9 If I have two users setting side by side, they frequently get the same results when they start up Am I missing somthing? Any help would be appreciated -- Frank Wagner fwagner111@aol.com Frank, Have you tried a Randomize statement just before the RND? That should do it. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job tha...

Same value written on all records automatically
In anticipation of my various Access deatabases being combined someday, I would like to automatically populate a column on every record with a project name I decide upon. -MS Access 2007 -Users create records through forms created using the built-in feature to create split forms. -There are about 20 forms which create records for about 20 underlying tables. -One separate database per project at this time. (Which is what we want for now because we will not be networking the databases-- each project will be separate mdbx, sometimes copied and moved around using USB jump drive.) -...

automatic resolving of e-mail addresses to CRM records
Hi, We have a situation here that is pretty dum. When we sent an e-mail through Outlook and save it to CRM, then some contact records are not automatically resolved in the CRM e-mail window so they appear red. We have alle the CRM users, but these users are also in the contactlist. How does the resolving algoritm works? Alle the users have 2 e-mail adresses: a full one and a short one. If I give the system users all the short one and the contacts the full one, does CRM then resolves this... At this moment, one unhappy victim every week resolves all the "red" items... Thanks...

Pivot table, calculating a new field derived from other field, e.g
I want to show a field called "Collection Rate" that is based on two other fields, namely, "Charges" and "Payments". It is not possible to do this in the database because you can't add up or average percentages--the result will not be properly weighted for different levels of summarization. Therefore, it needs to be derived from the values that are shown in the pivot table itself. How can I do this? You can create a formula field in the pivot table. Use the pivot table toolbar and select formulas from there. You'll need to construct the formula ...

pivot table #4
hi is there anybody can explain me about pivot table. if anyone can give me email id, so i can send my file . regards anil There are pivot table instructions and links here: http://www.contextures.com/xlPivot01.html hitesh wrote: > is there anybody can explain me about pivot table. > if anyone can give me email id, so i can send my file . -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html ...

UserForm selection transferred 2 spreadsheet???
Hi Is it possible in a userform, to have a combo box with data (lets sa different years, 2003,2004 to 2008) and after the user presses a butto on that, for it to takes the user to a worksheet and puts a word in th appropriate cell (e.g. Yes). So.........there's a column of years and then, depending on what yea the user selected, the word Yes is entered alongside the year. How do get this to work? Shud I use option buttons/check boxes instead?? Thx for any replies Chri -- Message posted from http://www.ExcelForum.com With the combobox, you can use the data on the sheet as the RowS...

CRM 3.0 bulk import - contacts
Hi, I am trying to import large number of contacts into the crm. I have csv file which contains all those records. While importing those records through bulk import tool in CRM, it looks to me that crm only imports first 15 records in one go !! If I then remove those first 15 records from csv file & try again, the it imports next 15 !! Is this a limitation or is there any setting available for this number ? I have crm 3.0 for SBS. Regards, Atul We found importing from .csv a total pain. If some of the records are in the wrong [i.e. unexpected] format it seems to just miss those on...

Multiple Selecting Problem
Hi, I'm trying to select multiple accounts to send direct mail to...normally ctrl + click multiple accounts would do this. When I ctrl + click it opens a new browser window instead of selecting another account. This seems to only be problem on my computer. How do I fix this? Check your mouse settings to make sure you haven't changed the functions of the buttons on it -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Dan B" wrote: > Hi, > I'm trying to select multiple accounts to send direct mail to...normally > ctrl + click multiple accoun...

To have selected the year on combo box
Hello, The following crosstab query can produce the name of our church offices sideway ( year to year comparison), but how can we make it to seledt the year on combo, and then the report will produce it for the last 5 years: since my landscape report can only accomodate for 5 years while my data is already 10 years These is my query: TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM Pel...

How to load String from one of the string tables
Hi: I'm using VC7.1. I have 5 string tables each represent a different lauguage, but the same mesaage has the same ID. For example, ID=100 represent the same message "User Guide" and this ID is in all 5 string tables. My question is, how to load string based on the user's local so that the correct string be loaded from the correct string table? For example, if the user OS is Chinese, the string will be loaded from the string table stroing messages in Chinese. Thanks for your help! Polaris Polaris wrote: > I'm using VC7.1. I have 5 string tables each repres...

Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql server) and it paste the data on 4 sheets in excel. Occasionally the reports are too big for excel. I'd like to develop a similar database that saves the results of the 4 stored procs as tables in a new Access database. right now I have it returning the results as a query. How do I save the results in another database? Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 Hi Billy The following SQL statement should do it for you: SELECT * INTO [TableName] I...

find text and copy selected rows from text and loop
Hi I'm need your expert help. In a worksheet I need to find the word "Filename" in column B, then cut rows until the next "Filename" word. I need this looping to start from the 2nd "Filename" word. Upon highlighting rows I need to cut selected rows and add this to a new worksheet. This needs to be done until all "Filename" words have been searched. I'm hoping this makes senses and that someone out there can help me. Many thanks Frederic Hi, Put this in a 'General' module and run it. It assumes the source data ...

Table of Contents
Hello there, I'm writing the newsletter for our company, and every issue is a bit different.. but I have an 'Inside this issue' table of contents on the front page (as you do). The thing is, there's a lot of chopping and changing inside the newsletter, and I tend to forget to update the T.o.C. sometimes. I managed to find the wizard, but that only changes the appearance of the table. Is there any way I can link the headings inside to automatically update in the T.o.C? Thanks in advanc Aitch Aitch wrote: > Hello there, > > I'm writing the newsletter for our comp...

Linking Tables 01-14-08
I need some advice on basic table set-up / linking I have a student record table with a unique identifier (student ID) Contains student name and address I have an enrollment record table with the same unique identifier (student ID) Contains term and year attended Now I would like to create a third table to record transcript orders. The unique identifier would be student ID. However, I would also like the student name to appear in this table (it is stored in the student record table). How do I get the name to show up in this new table without duplicating the information? Create a q...

Select a worksheet
I'm using the below formula to determine the specific type of equipment from a list named: database =IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$2,"Desktop",IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$5,"Notebook","Error!!!!!!!")) value of X2 = DSK and value of X5 = NBK What I need help with is a way of using the value returned to automatically open the relevant worksheet, ie: If "Desktop" is returned the Desktop worksheet needs to open and if "Notebook" is returned the notebook worksheet needs to open automatically. All wo...

Exchange server randomly asking random users to Authenticate randomly...
Has anyone had a problem like this where a user is in their Exchange mailbox using Outlook 2003 and the server prompts them for their credentials? If they hit cancel and exit out of Outlook and run it again it lets them into their mailbox. Thank you, Benjamin Pls make sure the outlook 2003 Client does not using Cache mode If is ,pls clearn this option -- Jammy "Benjamin Chan" <bchan[pleasenospam]@controlproductsinc.com> ���g��l��s�D :#BxdizrtEHA.3972@TK2MSFTNGP15.phx.gbl... > Has anyone had a problem like this where a user is in their Exchange mailbox > using Outlo...

Select sentence using a variable
Hi, In Excel 2003 I'm trying to write a query sentence to get data from a dbf table where table.date_mov is newer than a value (a cell in the worksheet which I named as date_min ) How can I get it? TIA ramonvi Done! no problem, I needed to sleep for a while! ramonvi "ramonvi" wrote: > Hi, > > In Excel 2003 I'm trying to write a query sentence to get data from a dbf > table where table.date_mov is newer than a value (a cell in the worksheet > which I named as date_min ) > > How can I get it? > > TIA > ramonvi ...

Multiple Date Records
Hi, I need help, but don’t we all. What I need is. How can I use a query to find multiple dates from a table? My start date will be 08/05/2009, the next date will be 7 days before original date of 08/05/2009 (07/29/2009) , then 7 days before 07/29/2009 and continue till end of the gmDate column or I enter a stop date. Thanks, Bill Bill, Given this data in tblTest... TestDate 1/1/2009 1/2/2009 1/3/2009 1/4/2009 1/5/2009 1/6/2009 1/7/2009 1/8/2009 9/1/2009 10/1/2009 Given this query... SELECT tblTest.TestDate, Weekday([TestDate]) AS WkDay FROM tblTest WHER...

Generating Correlated Random Values in Excel
Hi Mike, Thanks so much for your advice. I think that I have it figured out, but can you confirm this for me? I can't afford to screw this up. If mean(inflation) = .031 stdev(inflation) = .047 mean(stock return) = .106 stdev(stock return) = .204 Column Headers: A B C D 1 Z1 Z2 Inflation Stock Return Data Generation Formulas A2 =NORMINV(RAND(),0,1) B2 =NORMINV(RAND(),0,1) C2 =0.031+0.047*A2 D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5) Are these formulas correct to generate the random data? Particularly cell D2. Does this give me a complete value that t...

Can you create a table within Notes?
Is it possible to created a bordered table within the Notes pane? I am using PowerPoint 2007 on Windows XP. No. You can add tables to the notes page, but not to the notes pane. The tables on the notes page do not show up in the notes pane, since this is not the notes placeholder shape. Bill Dilworth "Nina" <Nina@discussions.microsoft.com> wrote in message news:C634DCE1-7E77-47D6-8571-C84D1DCB55C3@microsoft.com... > Is it possible to created a bordered table within the Notes pane? > I am using PowerPoint 2007 on Windows XP. > ...

Update back-end tables, fields, relationships etc...
I am looking for some examples of code or links to code examples to update a back-end database appropriately so it works with the next version of the front-end. Both dbs are Access2007, Example: I have a table tblPreference in the back-end that has a versionNumber field and is set to "1.0". If my latest "2.0" front-end is opened I want it to detect the OLD version 1.0 back-end and run the code to update the back-end to "2.0" and then change the version number. Things I need done include: - adding fields to existing tables - deleting fields from ...

random pages
I have developed pages that randomly define a set of normal lung function data based on gender, age and height. I have applied formulas to this data simulate 5 classic lung function abnormalities (normal, restrictive, obstructive, mixed and early obstructive.) Currently the sheets that define the abnormal are named by one of the above lung abnormalities. I would like to present data from random pages in a work book to students and rather than have the pages named by the abnormality be named a simulated patient name. My questions. Can I have the name of a sheet set to equal data from to ...