Update All Records With Random Value ?
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 ?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Update Query With Rnd (random Numbers) Question
Hello Everyone, Im trying to make a query which will allow me to run a query update so that all my listings will get a new random number generated for them, Ive got it selecting any listing with a ID of >0 (so basically thats all of the listings) What id like it to do is then assign a random number to the randsort field, (prefer a number like 0.812 or 0.342 etc etc) To start with ive tried using the Rnd feature, but its updating all the listings with exactly the same number (though granted the number changes each time the query is run) I see in some of the other comments regarding rnd that you may need to put a randomize statement somewhere, Im very new to programming in access and was just wondering am I on the right track, and if so where abouts should i declare the randomize code since its in a query? Thanks for your time and look forward to your replies Cheers Ezy
View Replies !
View Related
Update Column Valu With Random Number
hello all I am trying to set column values to a random number between 3 and 5 PMRatingTokenID: textH1: doubleYearID: textCode:UPDATE PMRating SET PMRating.H1 = (5+3-3)*Rnd()+3 This shows type mismatch error . What is the problem here.. please do help...Thank you
View Replies !
View Related
Random Duplicate Records
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!
View Replies !
View Related
Extracting Random Records
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.
View Replies !
View Related
Updating 10 Random Records In A Table.
Hi, I have following query that takes 10 random records from one table (tblaccounts) and inserts that into another table (tblrandom). SELECT TOP 10 tblaccount.*, Rnd([accnum]) AS Randnumber INTO tblRandom FROM tblaccount ORDER BY Rnd([accnum]) The above query works fine for that purpose. However, I want to just update 10 Random records in tblaccount to set the following field: SET status = "Assigned" Does anyone know how to do this using an update statement please? Thanks!
View Replies !
View Related
Query To Select 20 Random Records
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
View Replies !
View Related
Select 20 Random Records In Query
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
View Replies !
View Related
Division By Zero Problem With Random Records :(
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
View Replies !
View Related
Selecting 4 Records At Random From A Table
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*"));
View Replies !
View Related
Update Records
Erm, I know this sound a bit silly....but I woudered if there was a way of adding anew record to a table using vb code? My table is called tblTransmissions The fields are TransmissionCode, AutoNumber TransmissionDate, Date/Time TransmissionTime, Date/Time TransmissionStatus / Text TransactionCode / Number (Linked to another table by One-To-Many) When I hit a command button, I would like to update the recordset by firstly adding a new record. Then set Date & Time to Now, Then TransmissionStatus to a control on a form, same with the Transaction Code. Normally I would place the values on a form and update the values that way, but just wondered if this was possible! Thanks
View Replies !
View Related
Update Records
I am chasing a quick solution to what I am sure is a simple thing. In my database I have a table of items that have to be held for different periods of time (one item type kept for 5 years, another for 3 etc). In another table, linked to the first through the item type, I have dates entered against records which, using the first table, will calculate the date I can get rid of the item. My problem is that the period of time for some items to be held has now changed, and I want to change all the relevant records to update to the correct disposal date. I have tried just changing the period items are held for (in the form), and that will provide the correct date for future records, however I want to refresh the old records as well so they are all correct. Is there an easy way to achieve this without going through each individual record.
View Replies !
View Related
Update Records
Hello all... What I am doing is attempting to update a table through a form. I have been reading up on update queries, but I am not quite understanding how to use them. I have a table containing consumables that we constantly issue and receive. I have a form for each of these functions. Example: I order two ink cartridges from a vendor through a purchase order. When they arrive, I would like to enter the qty. I am receiving, then have it automatically add it to the table value. I don't know where to implement the arithmetic. Also, for PO's with multiple items, what's the best way to update the table with those? Thanks in advance...
View Replies !
View Related
Need To Update Exactly 800 Records
The problem is I removed my primary key (if you can even use a primary key for this) and there's no record number option on the query. I just need 800, it doesn't matter which 800. I was thinking of adding a field with a series of unique identifiers, but I don't know how to autofill sequentially.
View Replies !
View Related
Update And Add New Records
Hi all I have a table "tblEmployee" which has "name, employee_no, manager, etc" I recieve from HR a updated spreadsheet which can have new employees or just updates. ie new manager name. I can run an update query to update changes based on the employee_no which is constant. How can i add new starters from this list ?
View Replies !
View Related
Update Records
I am a new user to MS Access and i'm creating a stores account for my works, however i'm having problems with the updating of records after colleagues have been issued with kit. I have three table (employees, kitlist and issuelist) what i want is that when i issue kit to employees this updates the item in the kit list to reflect that i have issued an item, ie. take the number away from the units in stock. This is probably really easy however cannot find a solution to this problem. Thanks in advance for help
View Replies !
View Related
Update Records
Hello - I have a couple questions I hope that you can help me with. I have an asp page that is retrieving a bunch of records from an Access Database based on a field value (in my case its based on Department). They are then placed in a table format in the asp page. The user can then modify one or many records. Everything was working fine and then we upgraded our IIS Service from IIS5 to IIS6. Once we did that I started getting errors. I was able to update certain departments and not others. This told me the code was good and that the database permissions etc were good. It has to do specifically with the value or similar. I looked further and came to realize that it appears there were to many records to update. The Departments with a few records updated fine. THe ones with 500-1000 were erroring out. I went into the database and deleted the records from 1000 to 500 and everything works. Does anyone know if there is a record limit to update? Is there a function on ASP that I can use like Me.Dirty that will search first for updates and then just update the single record? Is this a result of IIS6? Here is a bit of my code: This is the area that it is bombing out on....I am not an expert and believe I might have hit an ARRAY size limit. Does that make sense? Is there an ARRAY size limit? THanks in Advance arr_ids = Split(Request.Form("Auto"),",")
View Replies !
View Related
Update Records
Hello friends, I have a table with a SSN field. Data is shown as a text with the following format:123456789. I would like to run an update query to change the format to: 123-45-6789. Any help? THanks.
View Replies !
View Related
Update Certain Records In Table.
Hi there all, I am looking for a way to update certain records in a table with an incremented value of +1 each time. For example, TBL_MAIN will have fields REF_NO and INCREMENT_VALUE. In field REF_NO, the same record may appear more than once and the INCREMENT_VALUE field needs to update by +1 each time. I have attached a simple snapshot of the table. In this example, I would like ref ABC123 have it's INCREMENT_VALUE increased by +1 each time following on from the last incremented value of 12460. I believe this may involve DMAX function, For Next loop and possible DAO recordset code but don't really know where to start! Rgds, Paul.
View Replies !
View Related
Update Subform Records
I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories. At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget. SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit FROM Budget WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1)); My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed. I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts. Thankyou
View Replies !
View Related
Relationships & Update Of Records
Hello all, I am in the early stages of building my 1st relational database (see attached). Ones I have built in the past were built on a knowledge of the sample ones provided with A97. I have since realised (from this excellent site) everything I have done in the past was completely wrong! One big table to hold all info causing hundreds of duplicate records - Doh! My Db is being designed to keep a record of all the refurbished dictation/transcription stock we hold. I have a question or two (basic stuff but i'm learning (slowly)). 1. Do my relationships look ok so far? 2. I want user to simply select a model in "Tbl_UnitDetail", and some how automatically lookup the rest of the info such as make and type from "Tbl_unit" New models will be added to "Tbl_unit" by administrators as and when they become available. How can this be done? Any advise or comments on my limited knowledge would be greatly received. Many thanks.
View Replies !
View Related
Update Records From One Table To Another
Hi This may seem a really dumb question but I just can not get my head around the best way of solving it. I have two tables orders and orders_tmp the table orders is created form the orders_tmp table. The order is created by a single field createorder (yes/no)from the order_tmp table. I run an append query to copy the records into the orders table. this woks fine. The problem I am trying to solve is how do I now go back and change the order for what ever reason. I need the order_tmp table to now also include my existing order. Help please
View Replies !
View Related
Update Field For All Records At Once
I have a table "volumes" with the following fields: product, year and one field for each month of the year. I update the data in this table monthly. From that table I calculate a total volume for each "project" through an After Update event on my "Cut In Month" field on my form Right now, when I update my volumes in the table I have to go through and trigger the After Update event on each and every record to get the calculated field to represent the updated volumes from the table. I would like to have one button on my form that does this all at once. Is this possible without binding it to the table? Thanks, Toni
View Replies !
View Related
Using The Calendar Once To Update All Records
Hello, This is probably a very basic question, but I can't think how to answer it myself. I want to use the built-in calendar on a form, click it once, and set the date for ALL the records in the table because the table contains records that are part of a batch. Do I try to use the date selected as criteria in an Update Query? Do I try to use the date selected to set the DefaultValue for the field? I know that many disagree with the viability of the built-in calendar, but my question isn't really about the calendar as much as it is about transferring the data to every record. Any help I recieve will be greatly appreciated. Gordon
View Replies !
View Related
Update 1.74 Million Records
It is not really 1.74 million records, but Access thinks it is, for some reason.Here's what happens: I get a CSV file in with 2196 lines. There are 2 date fields that are formatted poorly. Sometimes it is mddyyyy and some times it is mmddyyyy. I import the data to one table and then export everything to another table except those two fields. There are two date/time fields in the new table that are left empty at first. I then run 2 update queries to format and convert these poorly formated date fields. The query simply joins to the two tables on 3 fields and then updates the date field. When I hit the preview button on the query it takes about a second and says it will update 2196 records. Perfect.When I actually run the query, it takes about a half hour and tells me it will be updating 1.74 million records. Any ideas why this is happening? If and when the query runs there are still only 2196 records in both tables.
View Replies !
View Related
|
TRACKER
