I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
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*"));
I pulled a report from this website my company uses and it has around 7,000 orders and a corresponding employee to each record. Since each employee has around 20-50 orders, I was wondering if there was a VBA code or different Access tool to randomly select only 2 orders from each employee, thus reducing the data set from 7,000 to a more manageable number?
random image selection from a form on a buton click event.To be more explicit: i have 7 images on form, when i push one buton i want to see how each image take focus (when take focus .BorderColor =vbRed after lose focus .BorderColor =vbWhite) and after lets say 20s focus stop random on one image and keep that image with .BorderColor =vbRed.
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Porbably a stupid queston for most of you, but I am new to the game. Trying to get the selection of one combo box to filter the selections of a second combo box. Have one table called Sections with the colums SectionID and SectionName, a Second Table called SubSection with Colums SubSectionID, SubSectionName, and SectionID. Third Table called Points which has a column for Section, and SubSection, need to have the section combobox selection filter the SubSection combobox selections. Any help would be appreciated!
I have a small unbound form which has a txt box with code something like =Rnd((20-1+1)*Rnd()+1)) to generate random numbers. When the db and form are first opened the field always contains the same number. Using F9 to refresh will generate another number but it is always the same too.
If the form is closed then reopened (db is not closed) the field will happily generate a random number each time the form is opened or if F9 is used.
I have tried Recalc in numerous events but each time it failed to work. It's late at night here so I'm hoping the morning will bring a clearer outlook???
Was playing around, and when using the Code:Me.txtNum1.Value = Int((51 - 1 + 1) * Rnd + 1) format, I noticed that, with 6 differnet text boxes, that after opening the database, the "random" numbers repeat in the same order. That is, with 6 randomly generated numbers, the first set is always the same and the second the same and so on.
Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.
Hi, I have a DB that is basically a big electronic form. Each table is a section of the form and the main central table stores the ID's from the other tables. Most of the relationships are one to one but without referential integrity because sometimes sections need to be skipped.
It's been growing fairly rapidly lately as bit's keep getting added but now a weird deletion problem has started appearing. Every once in a while when a user goes to complete the form (right at the end) it suddenly dissapears. The records in the related "section" tables are still there but the main table storing all the ID's loses the record.
There are NO cascade update/delete's on any of the relationships, also no-error message appears it just goes. I think there is a problem in actually saving the record because I moved a DoCmd.RunCommand acSaveRec command from the complete button to a slightly earlier process and the point at which the record dissapeared also moved.
Does anyone know what may be causing it? It only happens to about 1 in 30 forms and there is no obvious link between the problem and a) the machine it happens on b) the person using it c) the data entered into the form
If it happens the same user can input exactly the same data in exactly the same way and it saves it fine.
It really is random!
People are starting to get really frustrated. I was tring to "sell" the idea to a new department as well and had 15 people sitting in front of a projector when it happened to me. I was mortified :eek:
this was working fine but now i get the following error message:
Run-time error '40036' Method 'Item' of object 'Forms' failed
when i debug, it highlights the line Forms![Payment Numbers]![VerificationID].Value = ANumber
the purpose is to get the LastOfVerificationID value from form 1 and then open Payment Numbers, create a new payment number, set verificationID to the new value. this was working fine and without even woring on the forms code, this error has arisen.
With Access 2003 I have a tbl_Orders with the field OrderID set as Autonumber>Long Integer>Random. This produces long strings of numbers both positive and minus. Is it possible to set a certain format such as #### and no less than 0 but no more than 9,999?
i have a query which returns n number of rows. my problem is that i need to display the values in text box which is created automatically according to the number of rows returned in the same page(form).
for example if my query returns 1 row then 1 text box must be automatically created and then the value must be displayed and if my query returns 3 rows then 3 text boxes must be created and then the corresponding three values must be displayed.(on the same page).
Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)
Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.
If someone could please help me, it would be greatly appreciated! Thanks in advance.
First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.
Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.
Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").
When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.
Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID. How do I go about this?
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?
In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?
this is my first post, so i hope i'm in the right bullettin.
I need help with this problem:
I have to work on this mdb that has been imported from Access 2000 to Access 2003. The guys did it some months ago, and of course they deleted the old .mdb and there are no backups. However, the db still works, except that the ID of some tables are now "random id" than "incremental id", and since they used it for some months, now they are full of new records with random IDs. Since they use THAT id for internal use, you understand that is not very easy to communicate stuff like "please send me document # -3189175187" and so they asked me to get it back to incremental, i tried from the table menu to change the dropdown, as i thought it was that easy. Bad luck, access now complains that the data aren't now contiguous (might be different error, it's a localized version, so i don't know how it is the exact error message, sorry), and i'm not really sure on what to do to avoid problems.
I've explained the best way i could, so if you need more info just ask, thanks everyone in advance!
I am not real sure whether or not this can be done with a query, but here goes. I have a query randomly selecting the top 6 from a selected group.
SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name] FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0) ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);
What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.
I would greatly appreciate any thoughts or help… Thanks Enviva