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 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 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.
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());
I'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.
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
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?
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.
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?
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.
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
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?
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 have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales). It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
I am building a database to evaluate football team performance. I have a table with results (tblResults) where a team will have records in either the Home Team or Away Team fields depending on the fixture.
I want to include a "Team Form" calculation that selects the last 6 games for each team (both home & away) and allocates points to calculate a recent form stat for comparison with the opposition.
I have tried using the TOP VALUES option in a query, but I can't seem to get it to extract the top 6 in descending date order for every team.
I created a personal database for keeping track of my meeting schedules. My table has Date field, Time field and Subject field. I have made several entries. On a given day, I have several meetings at different times.
I created a combo box in my form to choose the Date and see that day’s meetings. The meetings list of the date chosen by combo box should appear in my form. But here I think I made a mistake. Each record has date, time and subject. When I click the combo box, the drop down list shows the same date many times (as I have entered the same date but different time and subject for each record). How do I make the combo box display the date only once and not same date several times? Do I have to change the table set up? Please guide me.
I have a database that allows the user to order clothing items. Each size of each item has a different stock number.
I have [StockNo] - [StaffNo] - [Qty] i need to generate [OrderNo].
The problem i have is that [OrderNo] is incremented after every five five items (ie[StockNo]). This gives me my first problem of selecting just the first five records of a table in order to allocate an order number.
The second problem i have is that in order to allocate an order number, i have to summarise the [Qty] by [StockNo]. This gives me the opportunity to select the order number but i lose the individual [StaffNo] information. This information, together with the [OrderNo] is needed for a master order table.
I have created a crosstab query which summarised the [StockNo]but how do i retrieve the [StaffNo] information from the column headings.
I am new to Access and would be grateful for any help or comments as to whether i am going about solving this problem in the best way.
ok so I want a form that has a combo box with [item_id, item_name] on each row that i select. when i select one it displays the relevant quantity and allows me to edit the quantity.
I'm trying to write an app that will allow the user to search for records based on database fields, then select a subset of those records to be manipulated by other functions. The VBA book I'm reading led me to believe that a RecordSet would be the best way to store this subset of records, but searching around on the web has pointed me toward using a DAO.QueryDef. I can't seem to get either method to work!
Here's a quick overview of the app: everything is placed on one form. I have two tab controls that make a sort of upper and lower set of pages. One of the lower pages has the search functions. The user enters his search criteria into one or more textboxes (correlating to database fields, i.e. Last Name, SSN, etc), then hits the Search button. This should query the database, then populate a listbox with the search results. The user can then select one or more records from the listbox, hit another button, and the selected records are copied to another listbox on a page on the upper half of the form, where they can be further manipulated.
Can someone advise me on the best objects to use to accomplish this? A short code sample would be awesome.
By the way, does VBA have some sort of online API reference (like Java)? I know that MS Access has the object browser, but it doesn't give descriptions of the objects, nor does it list methods that can be invoked on them. Could someone also point me towards some good programmer's resources?
I am using an unbound box on the top of a form where I would like to select a clients name and have the form populate with their info. My problem is that the box will pull up the record by last name, but only pull up the first record with that last name in alphabetical order.
Example, If I have the following names in my database
Ben Smith John Smith Tim Smith
If I select "john smith" from the drop down "ben smith" will show up, if I select "tim smith" "ben smith" will still show up. If I select "ben smith" the correct record would show up.
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !
I have a problem I should be able to solve, but I can't think my way around it.
I need to automate a series of tasks to perform the following:
1. The user clicks a button on a menu form and arrive at a select records form. 2. The user makes choices regarding the contents of certain fields (blank, not blank, equals a text string, etc.) and presses OK. 3. On the OK click, the fields are applied to a query, the results of which are exported in an excel spreadsheet.
I've been doing step 2 and 3 by hand in that I alter the selections in the design view of the query and save it. I then run a macro that deletes the data in the current table, runs the append query with the new criteria, and creates an excel spreadsheet of the new data.
However, I now have to make this functionality available to a non-tech person, thus the need to create a GUI menu that can launch a form on which the user can select options and with a single click, launch the macro.
I've tried adapting select forms that I've used with forms and reports using the doCmd.openquery, but they will only work for select or crosstabs, not appends. I considered having the form create the query and then qrite the append query off that, but I can't seem to get that to work.
Can anyone give me some direction on where I can take this?
