As 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
I 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
I need to select 200 random customers from my table, how can I do that?
Table: tblCustomers
PK: CustID
I 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?
Good 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
I'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());
Hi 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
How 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.
Hi 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!
This 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 ?
If 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.
Hi 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
Hi 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*"));
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average
1234 .72
1234 .86
1234 .94
etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
Thanks, Tim
Hello,
I want to make a query that retrieves me all the duplicated records from a table. It´s possible?
Thankx
I have two tables, bad design, not my choice of the way it's set up.
Table a has Fields 1-2, dups allowed in all fields. No pk was originally assigned.
Table b also has the same fields 1-6, but also has field 6.
Table a has all of the records in question, table b only has some of the records from table a but does not have any records that are not in a.
Something like this.
Table a
Field1 Field 2 Field 3 Field 4 Field 5
John Shirt blue denim button
Mark pants green straight
John Shirt blue silk button
Table b
Field1 Field 2 Field 3 Field 4 Field 5 Field6
John Shirt blue denim button new
Mark pants green straight used
If I query the two tables, a left join to b, then I get the correct number of total records. If I select any records from b, then the total records goes higher than there are, even for the two combined, listing the same record multiple time.
Any suggestions are appreciated. I am a blood bank lab person, not an IT person, or DBA, so simple responses are greatly appreciated.
This is killing me,
I have a form with a unique ID, I have a query that pulls all data regardless of the ID on the form.
How can I get the Query to pull only the data for the ID that is currently in view on the form?
Please help..
Thanks,
Fen How
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
I am developing an Event Management DB for business training courses. I have an attendees table with a link to CompanyNames. I wish to be able to produce an attendees report based on the CompanyName ie all attendees from the selected company. I have done this by using a dropdown of companynames on a Criteria selection form. This works well.
What I would also like to do is print the same list without the companyName criteria. I am aware I could make another query but was hoping that maybe there is a way of setting up the criteria in the companyName field so that it could accept either a company name or ALL company names.
Hope someone can help.
Thanks
Peter
I have a Sales Table with below fields, i might have not set it up in the best way possible.
Consumer, Consumer_ID, SaleDate,Prod_Sl#,Prod_Type,Sale Amount
1. I need to update the sales price for each item sold based on category of Product Type, as we are tracking the product with Serial Number.
For an instance if 2 items of Category1 with Prod_Sl# as Sl1 and Sl2 and
2 items of Category 2 with Prod_Sl# as Sl3 and Sl4 are sold.
I need to update the sales price amount for these.
2. I want to accomplish this using a query.
I have a Union query as follows;
SELECT ALL *
FROM 1st_Lives
UNION SELECT ALL *
FROM 2nd_Lives;
1st Lives has 465,414 records and 2nd Lives has 151,852 records.
When I run the query I only get 604,976 records instead of 617,266 (I basically just want to add the two data items together).
I'm having a bit of a situation with a select query that I set up.
I have two tables, both with a list of serial ID's.
One is table field is formatted like this:
"B0340 13 453423 X"
The other is formatted like this:
"=B03401345342300"
I wanted a select query that could show me the records in a universal serial like this:
"034013453423"
This leaves out the "spaces", the "B" and the " X". (Couldn't find a way to make Access generate a new field with the serial ID correctly without a query).
So I used the "Mid" expression in a select query and it worked, it showed me the first table in the correct format.I wrote the same "Mid" expression for the other table and it worked too.But, when I ask for the results for both tables in the same query, it shows me much more records than I have(all duplicates). I figured out that when in table one I have 20 records, in table two I have 5 then the query shows 120 results (20 x 5).
This is the code in SQL view:
Code:
SELECT MainDatabase.[Transfusie Eenheid + Specification], (Mid([MainDatabase].[Transfusie Eenheid + Specification], 2, 4) & [MainDatabase].[Transfusie Eenheid + Specification], 7, 2) & [MainDatabase].[Transfusie Eenheid + Specification], 10, 6)) AS [BloedeenheidID MD], ScannedForms.BloedEenheidID, Mid[ScannedForms].[BloedeenheidID], 3, 12) AS [BloedEenheidID SF]
FROM MainDatabase, ScannedForms;
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies View RelatedI have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved
FROM tbl_Orders
WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0))
ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
I 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.
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
Using Access 2010