looking for MS Access VBA code to do the following: I am trying to assign incrementing numbers ( ie - 1-10) to records in an existing table by date and pull a 10% random sample from that table based on the numbers assigned. Any help is appreciated. Existing Example Table:
number date person
45001 1/19/2011 Ray
21020 1/19/2011 Steve
49009 1/19/2011 Bob
72005 1/19/2011 Ray
62203 1/19/2011 Bob
83406 1/19/2011 Ray
88700 1/19/2011 Tom
99174 1/19/2011 Tom
11726 1/19/2011 Tom
90999 1/19/2011 Ray
81119 1/18/2011 Ray
12226 1/18/2011 Steve
23334 1/18/2011 Bob
14448 1/18/2011 Ray
65550 1/18/2011 Bob
96663 1/18/2011 Ray
77771 1/18/2011 Tom
58882 1/18/2011 Tom
39994 1/18/2011 Tom
80996 1/18/2011 Ray
i want it to add the new field (No) to the existing table assigning numbers incrementing from 1 to x where x is the count of records for each varying date, like such
No number date person
1 45001 1/19/2011 Ray
2 21020 1/19/2011 Steve
3 49009 1/19/2011 Bob
4 72005 1/19/2011 Ray
5 62203 1/19/2011 Bob
6 83406 1/19/2011 Ray
7 88700 1/19/2011 Tom
8 99174 1/19/2011 Tom
9 11726 1/19/2011 Tom
10 90999 1/19/2011 Ray
1 81119 1/18/2011 Ray
2 12226 1/18/2011 Steve
3 23334 1/18/2011 Bob
4 14448 1/18/2011 Ray
5 65550 1/18/2011 Bob
6 96663 1/18/2011 Ray
7 77771 1/18/2011 Tom
8 58882 1/18/2011 Tom
9 39994 1/18/2011 Tom
10 80996 1/18/2011 Ray
then i want it to create/return in a new table the random sample (10%) for each date change - example output would be a new table like such:
No number date person
5 62203 1/19/2011 Bob
2 12226 1/18/2011 Steve
if i changed the % to 20% random sample- I want a table like such:
No number date person
9 11726 1/19/2011 Tom
7 88700 1/19/2011 Tom
10 80996 1/18/2011 Ray
2 12226 1/18/2011 Steve
and so on - thanks for helping
2 2379 Rabbit 12,516
Recognized Expert Moderator MVP
You could use ranking subquery to get the 1-10, if it's necessary. If the ranking is only for the purpose of selecting a random sample, then you don't need it. If it's needed though for a different purpose, then there's plenty of threads on these forums about ranking records.
As for the random sample, you could use an update query that sets a field using Rnd() seeded with a unique ID and time. Then you could use a query that will select from those records using a subquery. The subquery would return the top # of IDs sorted by the randomized column.
ADezii 8,834
Recognized Expert Expert
You can easily create a Query that will generate a Random Number of Records, as a Percentage of Total Records, for a given Date. - Create a Calculated Field called [Random]. This Field will call a Public Function, and pass to it the [Number] Field. You will also sort on this Calculated Field (Ascending). The [Number] Field will not be used within the Function, but is required.
- Function Definition:
- Public Function fGenerateRndNum(sngDummy As Long) As Single
-
'The Parameter sngDummy is required but not used
-
Randomize
-
-
fGenerateRndNum = Rnd()
-
End Function
- Create a Query consisting of the [Number], [Date], [Person], and [Random] Fields. Hide the Random Field since it has already done its job, and is no longer needed.
- Set the Top Values Property of the Query to 20%.
- Set the Date Criteria to #1/19/2011#.
- Query Definition:
- SELECT TOP 20 PERCENT tblTest.Number, tblTest.Date, tblTest.Person
-
FROM tblTest
-
WHERE tblTest.Date=#1/19/2011#
-
ORDER BY fGenerateRndNum([Number]);
- Execute the Query.
- Sample results from three Trial Runs.
- Number Date Person
-
99174 1 /19/2011 Tom
-
62003 1 /19/2011 Bob
- Number Date Person
-
72005 1 /19/2011 Ray
-
99174 1 /19/2011 Tom
- Number Date Person
-
49009 1 /19/2011 Bob
-
72005 1 /19/2011 Ray
- Youo can now convert the Query to a Make Table Query, and execute it once for each Unique Date.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Rahul Anand |
last post by:
Hi all,
I am trying to fetch 5 records, randomly picked and in random order
from a MySQL table (MySQL version > 3.23).
I wrote my SQL Query as
SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT
5
|
by: Bart Nessux |
last post by:
I am using method 'a' below to pick 25 names from a pool of 225. A
co-worker is using method 'b' by running it 25 times and throwing out
the winning name (names are associated with numbers) after each run and
then re-counting the list and doing it all over again.
My boss thinks that 'b' is somehow less fair than 'a', but the only
thing I see wrong with it is that it is really inefficient and ugly as
it's doing manually what 'a' does...
|
by: amywolfie |
last post by:
Hi All:
I know this is simple, but I just can't seem to get there:
I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.
Every time a new record is added, the all SEQUENCE #'s must be reset.
The re-numbering will be triggered by an EXPORT button.
|
by: BerkshireGuy |
last post by:
Hello everyone,
I had a query that took 3,000 random records (which are addresses)from
a table.
This recordset is to send off surveys. I've noticed by looking at the
data, that I have some people who live at the same address. For
instance Bob Smith and Betty Smith. They both live at 120 Elm Street
in Albany New York.
|
by: jordi |
last post by:
I need the random.sample functionality where the population grows up to
long int items. Do you know how could I get this same functionality in
another way? thanks in advance.
Jordi
| |
by: noob92 |
last post by:
I'm using python to write a program that, when given a list of random numbers, sorts them from lowest to highest then it asks the user for a number and it checks that number's place in the sorted list if the number is present, but i keep getting a syntax error on an "if" statement line near the end. is it because i'm not allowed to look up a number in the sorted list? here's my program with a list of example numbers (highlighted in bold is where...
|
by: lilsn0opy04 |
last post by:
I am pretty new to Python. I wanted to create a program so that if a user entered a number, then that amount of random numbers will appear.
I wanted my random.randrange(100)+1
"How many numbers do you want to generate?" 4
19
2
45
35
|
by: liscete |
last post by:
I need to create a simple random sample of 20,000 out of 4,000,000. what is the statement?
I was told select top 20K would do this but i believe the select top XXX is based on a first ordered database???
Please help!
Thanks
|
by: twomikey |
last post by:
I am confused and not even sure this can work. I have a database that needs to assign licenses that we bought for IP addresses. We have purchased 1000 of these and they are in numeric order. How do I set up an autonumber when the first number would be 14.255.2533.26 for example. Is this possible?
|
by: Geog |
last post by:
Hello,
I'd like to know if it is possible to make a random sample of a number/percentage of rows with the same value, i.e. to select randomly 6000 registers of field "x" where x=0
Thank you
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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.
| |