How To Select X Random Records
Jun 20, 2005I need to select 200 random customers from my table, how can I do that?
Table: tblCustomers
PK: CustID
ADVERTISEMENT
Query To Select 20 Random Records
Aug 9, 2007As part of my job, each month I have to select 20 problem report resolutions and grade them on quality. Typically we have about 100 problem report resolutions per month. Is there a way I can use a query to return a random selection of 20?
Thanks,
Jim
Select 20 Random Records In Query
Mar 12, 2008I have to review 20 reports each month for quality check. I have a query that lists the reports completed within the past 30 days. Is there a way to filter this query to show only 20 random records?
If there is no way to do this, can you suggest some way of doing this so that it's impartial? For example I don't want to select the first 20 of the month because everyone will catch on and wait late in the month to post their report. Normally about 70 reports are completed in a month.
Thanks,
Jim
Queries :: Select Random Records From Unrelated Tables
Aug 9, 2014I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:
Desired Result:
Animal Color State
cat red Ohio
dog blue Texas
fox green Iowa
I have been able to create three individual queries that will pull X number of random records by using:
SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());
Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?
Select Random Numbers With Limit 50
Sep 8, 2007Hi all,
I am having one table with id from 1 to 50. Now I want to return random records from that table using rnd.The point is that is there any funntion like
Select [id] from tblids where id not in (values) with rnd
Thanks in Advance
Dana
Select Equal Type - Based On Random Query
Oct 6, 2006Good Afternoon Everybody,
Apologies for imposing on you all but I was wondering whether or not you could help me out??
Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field “SectionHeadings” such that out of the 30 “random” questions that it currently generates it will provides me on completion with the following:
5 From Set1
5 From Set2
5 From Set 3 …..etc etc
It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.
All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I’ve already done and show me what needs to be done in order to achieve my aim.
I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.
Best Regards
CarolW
Random Duplicate Records
Feb 20, 2006Hi Guys,
I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:
Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = Me.txtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("Submission #") = Me.SubNum
rs.Fields("Sample Type") = "Blasthole"
rs.Fields("XRF") = "True"
rs.Fields("LOI") = "True"
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?
Thanks in advance for your help!
Update All Records With Random Value ?
Jul 25, 2006This is my first post. Please help me.
Here's my DB :
Status : Number (Long Integer)
Random : Number (Double)
If Random value is around 0-0.9, the status value is 1
If Random value is around 0.9-1, the status value is 0
Here's my Query :
UPDATE DB Set Random = RND()
Rnd() give random value, but each records have the same value,
let's say 0.71212154
How can I update all records with random value,
with a single query ?
Extracting Random Records
Nov 3, 2006If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.
e.g.
Table -> 1000 records
records which belong to categoryA -> 200
extract 20 random records from the table which have a value of categoryA inthe cboCategory field.
Thanks for any suggestion.
Division By Zero Problem With Random Records :(
Apr 19, 2008Hi all
I've been using the following code to show Random records from an Access Database, the only problem with it is that every now and again it throws a "division by 0 error" any ideas how i can fix?
Code:strsql_videos = "select top 5 * from items ORDER BY RND(id)*(id*1000) MOD datePart('s', NOW()), id;"set rs_videos = server.createobject("ADODB.recordset")rs_videos.open strsql_videos, conn_videos, 3, 1, adCmdText
Iam hoping its possible with a few lines of code rather than some elaborate fix :P
Selecting 4 Records At Random From A Table
Apr 2, 2006Hi All,
Any ideas on how I do the following.
I have a table with 1500 records in it.
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] )
SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2
FROM Schedule
WHERE (((Schedule.Departure) Like "man*"));
Queries :: Adding New Records To Table - Random Number No Duplicates
Jul 31, 2013I have a form which allows the user to add new records to a table. After the user had entered all the information into the form, they click a command button to add the record. In addition to adding the new record, my command button runs an query which is supposed to generate a random number between 1 & 1,000,000,000 and update the record ID field with that number.
Here is the formula I have been using in the "update To" now of my query: Int((1000000000-1+1)*Rnd()+1)
My problem is that I keep getting duplicates. You would think that the chances of getting a duplicate number would be pretty small with this large of a range, but I get a duplicate almost every time.
I have tried indexing (No duplicates) the field in the table, but that did not work. When my query generated a duplicate number, the record was just not added to the table.
I also tried a two step approach:
1-Make a table of all in use record ID numbers from my table (tblIdNo)
2-Update new record with a random number that is not in tblIdNo
This was a no-go too
How to build an update query that will update each new record added to the table with a random number between 1 & 1,000,000,000 without any duplicates? This seems like it should be so simple, and I am starting to get really frustrated.
I would prefer to accomplish this through a query/queries (if possible) rather than with 100 lines of code. This database is not for me, it's for another group, and the individuals in this group are totally freaked out by code.
Queries :: Query For Inserting Random Records Into Temporary Table
Jul 16, 2015I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited
FROM dbo_Billing AS b
WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1))
ORDER BY Rnd(-(1000*b.indx)*Time());
Modules & VBA :: Random Test Generator Which Pulls Records Based On Category From Table
Oct 3, 2013I'm having trouble with my VBA module.I have a random test generator which pulls records based on a category from my table into a temp table using a make table query. I use the following code:
Private Sub Command2_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "1", acViewNormal, acEdit
DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal
Reports!WrittenExam.lblTitle.Caption = "Exam Name"
DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal
Reports!WrittenExamAnswerSheets.lblTitle.Caption = "Exam Name - Answer Sheet"
End Sub
My querry makes the table, and then generates two reports (my exam, and the answer sheet). I'm getting an error 3211, saying the temp table is already in use by another process when trying to generate both reports. I used a Macro before, but I have a need for custom report headings, so I'm using VBA.
Modules & VBA :: Possible To Export Select Records And Fields In Those Records To A Specific Location?
Jun 15, 2013In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?
Jun 16, 2014How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
Select Top 2 Records
Jun 12, 2007Hi,
My question 'sounds' easy, but i can't think of a way to do this. Please help!
Ok, i have an 'employee' history table with 200K records. Each employee could have multiple records with different 'effective' dates(please see below for an example of two employees' records). What I want to do is to run a query where it will pick up ONLY the top TWO records of each emplyee. How would I go about doing this?
Thank you.
Joe
Employee History Table:
SSN--------------------EFFV_Date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
123-45-6789---------------08/22/2000
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
222-33-4444---------------06/30/1990
Query result should be somthing like this:
SSN-----------------------EFFV_date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
Select Last 3 Records
Dec 20, 2004How can I restrict a continuous form to select only the last three records
any help would be appreciated
Jabez
Select Range Of Records
Sep 1, 2005Is there a statement that will allow me to select a range of records in the
middle of a selection of records? e.g. Rather than TOP 10, can I get like
records 10-20, or 50-65 etc?
Prodigy.
Select First X Amount Of Records
Feb 27, 2008Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
Calender To Select Records
Oct 24, 2006I was wondering if anyone has seen a easy way to use a calender to select records rather than having to type in a "between such and such a date".
Because what i have now is a qry that pulls data from a table between two dates. And i would like to make it more GUI rather than typing to prevent user imput error if you understand what i mean.
I tried searching but didnt know what to search for.
Thanks for anyone that has any input!
Regards,
Jon
Using A Form To Select Records
Jan 8, 2005Ok I am probably goining nuts by now. I could have swore that I had a referance book that showed how to set up a form with multiple combo boxes that were used to filter forms/reports for desired records. Now I can't find that referance anywhere! Does anyone know how to do this or can they point me to a referance with this information? Please help me, this is one of the final touches I need for a project I have been working on for months.
View 6 Replies View RelatedListBox To Select Records... Please Help
Aug 5, 2005Hello All...
I currently have a ListBox which I am using on a form as a "jump To..." record selector. It has to show 4 values to the users, and so far it has worked quite well. However, I recently added some queries to the footer of the form to help narrow down the data for searches, or if the user wants to see all open data of a certain topic. I'd like to have the ListBox "refresh" and only display the results of the query, but I cannot for the life of me figure out how to do it. The root of the problem seems to be that the initial SQL for the ListBox is selecting values from the under-lying table, and when the filter is applied, it simply doesn't care. When you click on entries that aren't in the filtered dataset, it doesn't error out or anything, and when you click ones that ARE in the filtered dataset, it does work properly... I'm close... How do I filter the listbox as well?
Thanks,
Group And Select First Records
Feb 9, 2015how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
TomJones 1978 .465 56
TomJones 1979 .165 85
Select And Write Keys From Two Records
Jun 7, 2007Hello All,
If I want to select a row from subform A and a row from subform b and click a button and have the keys stored in another table what would be the best way to do that?
Thanks.
How To Select Records Starting From Number?
Dec 2, 2005Hi!
Can anyone help me how to create query selecting only the records starting with the numbers?
Thanks a lot in Advance