Hello all,
How can I ramdomly select a list of records from an existing table?
For example: 500,000 names divided by 20,000 samples = 25. The N is
25, meaning samples would be sent to every 25th name on the list.
Any help will be appreciated?
TIA
ja 4 5441
ja <jo******@sbcgl obal.net> wrote in
news:2v******** *************** *********@4ax.c om: Hello all,
How can I ramdomly select a list of records from an existing table?
For example: 500,000 names divided by 20,000 samples = 25. The N is 25, meaning samples would be sent to every 25th name on the list.
Any help will be appreciated?
TIA
ja
If you have a numeric key in the table, use the Modulo operator
SELECT * from mytable where ([NumField] mod 25) = 25;
You'll get every 25th record if NumField contains consecutive
numbers.
--
Bob Quintal
PA is y I've altered my email address.
Bob Quintal wrote: ja <jo******@sbcgl obal.net> wrote in news:2v******** *************** *********@4ax.c om:
Hello all,
How can I ramdomly select a list of records from an existing table?
For example: 500,000 names divided by 20,000 samples = 25. The N is 25, meaning samples would be sent to every 25th name on the list.
Any help will be appreciated?
TIA
ja
If you have a numeric key in the table, use the Modulo operator
SELECT * from mytable where ([NumField] mod 25) = 25;
Shouldn't that be [field] mod 25 = 0 ?
You'll get every 25th record if NumField contains consecutive numbers.
Or create a row counter in a query so it is guaranteed to be sequential....
SELECT A1.Personnel_no , A1.Name, (SELECT COUNT(*) FROM tblEmployee A2 WHERE
A2.NAME <= A1.NAME) AS RowNumber
FROM tblEmployee AS A1
WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) Mod
25)=0))
ORDER BY A1.Name;
--
regards,
Br@dley
"Br@dley" <no@mail.com.au > wrote in message
news:dt******** **@news-02.connect.com. au... Bob Quintal wrote: ja <jo******@sbcgl obal.net> wrote in news:2v******** *************** *********@4ax.c om:
Hello all,
How can I ramdomly select a list of records from an existing table?
For example: 500,000 names divided by 20,000 samples = 25. The N is 25, meaning samples would be sent to every 25th name on the list.
Any help will be appreciated?
TIA
ja
If you have a numeric key in the table, use the Modulo operator
SELECT * from mytable where ([NumField] mod 25) = 25;
Shouldn't that be [field] mod 25 = 0 ?
You'll get every 25th record if NumField contains consecutive numbers.
Or create a row counter in a query so it is guaranteed to be sequential....
SELECT A1.Personnel_no , A1.Name, (SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) AS RowNumber FROM tblEmployee AS A1 WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) Mod 25)=0)) ORDER BY A1.Name; -- regards,
Br@dley
Two potential problems here are that with 500,000 names (as the OP says)
this is not going to be fast - I could imagine it would pretty much grind to
a halt. Secondly, if you are ordering the people alphabetically and
selecting in any kind of structured way, then the results cannot be said to
be random.
Now I'm not a mathematician, and don't want to enter a huge debate on what
is random, but wouldn't this be better?
SELECT TOP 20000 * FROM People ORDER BY Rnd([ID])
Anthony England wrote: "Br@dley" <no@mail.com.au > wrote in message news:dt******** **@news-02.connect.com. au... Bob Quintal wrote: ja <jo******@sbcgl obal.net> wrote in news:2v******** *************** *********@4ax.c om:
Hello all,
How can I ramdomly select a list of records from an existing table?
For example: 500,000 names divided by 20,000 samples = 25. The N is 25, meaning samples would be sent to every 25th name on the list.
Any help will be appreciated?
TIA
ja
If you have a numeric key in the table, use the Modulo operator
SELECT * from mytable where ([NumField] mod 25) = 25; Shouldn't that be [field] mod 25 = 0 ?
You'll get every 25th record if NumField contains consecutive numbers.
Or create a row counter in a query so it is guaranteed to be sequential....
SELECT A1.Personnel_no , A1.Name, (SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) AS RowNumber FROM tblEmployee AS A1 WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) Mod 25)=0)) ORDER BY A1.Name; -- regards,
Br@dley
Two potential problems here are that with 500,000 names (as the OP says) this is not going to be fast - I could imagine it would pretty much grind to a halt.
Sure. There are lots of ways to skin a cat. The most elegant/complex of SQL
statements may just be too slow realistically.
Secondly, if you are ordering the people alphabetically and selecting in any kind of structured way, then the results cannot be said to be random.
Ah, I missed the random part and only saw the second part (and the subject)
where he stated he wanted every 25th record...
Now I'm not a mathematician, and don't want to enter a huge debate on what is random, but wouldn't this be better? SELECT TOP 20000 * FROM People ORDER BY Rnd([ID])
Yep, that'd be the easiest way to get a random sample.
(I like doing things dynamically so I'd probably pass a parameter such as
the percentage or number of records I wanted returned).
Thanks for pointing that out.
--
regards,
Br@dley This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: (Pete Cresswell) |
last post by:
I've got a .BAT file that I use for executing various MS Access apps that I
wrote way back in the days of 2.0.
It's evolved over time, but it still contains a number of possible paths to
MSACCESS.EXE, tries them all, takes the first one it finds, and dies if none is
found.
Seems to me like at some time or another I was able to clone this .BAT file and
implement it with no path. Something about MSACCESS being a registered
application.
|
by: Juggernath |
last post by:
Hi,
I spent all morning trying to find a way to select a datetime field from a
table (I work with ASP)
My SQL is "SELECT * FROM Mytbl WHERE myDate = #2.12.2003# "
(I live in Croatia, so that's the setting for a time format I use in
MSAccess, also, I use a Short Date format to put into table only a date, not
time, the application will probably always work on local server - intranet)
So, in my table there are several fields 2.12.2003. but...
|
by: Bruce |
last post by:
Is there a way to create the "old style" Excel pivot table form in
MSAccess 2002/2003?
I've seen several postings "dancing around this issue", but no
definite answer.
Is there a way to clone the Access 2000 Pivot Table Wizard over to
2003 to provide this functionality?
Rgds,
Bruce
|
by: Chris |
last post by:
I recieve unrecognized database format when trying to open a MSAccess
2003 database with MSAccess 2000. I only need it to open as
read-only.
Why won't it open my database?
| |
by: Annick Van Hoof |
last post by:
Hi,
I'm having my ASP.NET application hosted at a Windows 2003 server (IIS6).
This works almost all the time (I have uptimes of 100% on most days), but
then all of a sudden a few days in a row I have the following phenomenon:
..aspx still get processed, but as soon as any of them try to read/write to a
MsAccess database (using DSNLess OleDBConnection) it's timing out.
This happens for a period of about 40 minutes and then all of a...
|
by: Yelena Varshal via AccessMonster.com |
last post by:
Hello,
I have a problem with one of msaccess.exe API calls that work on my desctop
but does not work on the laptop from within MS ACCESS. There is a lot of
differences between 2 computers including one running Office 2000 and another
running Office 2003. This code was created by a previous developer. I need to
find the description and parameters of the API calls. I found the article
...
|
by: reidarT |
last post by:
I am making an installation program for an Access application. The
application is run on both Office 2000, 2002, 2003 and soon 2007 versions of
Office,
When I install the application I need a shortcut from the users desktop and
I need
C:\programfiles\office\office9\msaccess path\application.mdb /wrkgrp
path\lockfile.mdw
C:\programfiles\office\office10\msaccess path\application.mdb /wrkgrp
path\lockfile.mdw...
|
by: jdaelhousen |
last post by:
I have a bit of a problem I'm hoping someone can shed some light on...
I have a VB.Net console application written in VS 2003 that produces a .exe file that now sits on a Windows 2000 server directory. This exe does the following 3 things:
1.) Using the VB Interaction SaveSetting() method, it programmatically updates the system registry under the HKEY_CURRENT_USER\SOFTWARE key and saves a directory location used by a PDF driver (so that...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |