I have a table (tbl_entry) of performers and the different sections they are performing in. I can pull the performers for each individual section but is it possible to then give the performers a random number that will indicate the order in which each will perform. Eg: 5 performers in section 21a
Molly
Mary
Mike
Merv
Mandy
So can I get access to randomly assign numbers 1-5
I am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
I have extraction report from calculation program and dont have access to tables. When I import it to MS Access it looks like attached Sheet1. Is there a simple SQL query or VBA code which will allow me to import the data to new Sheet2 table? Fields 1,7&13 will always be the same and I want them to be column names, Fields 4, 10 &14 contains data which will be different each time I delete the old calculation from Sheet1 and import a new one. I would like to be done in Access as I have to import few hundred calculations and store it in Sheet2 table.
View 2 Replies View RelatedI have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
I have tried the following:
SELECT top 10 percent Count(*), empID
FROM tlbCompany
Group By empID
HAVING Count(*)<25
ORDER BY rnd(RandomID);
This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
Can this query be done?
And, what am I doing wrong?
Thanks to all that help....
Enviva..
OK
I have read through I think every post with the word email in the title ! and in the subject... and tried various bits of code that were posted, but can't get anything to work... :mad:
Here is what I want to do.
I have a form (based on a qry called "QryInvoices Due") which brings up a list of people.
On this form is a field called "send email?" which is a tick box which the user can select.
I also have a command button ! which When Clicked I want it to do the following.
Send an email to all of the recipients which have "send email?" as YES
their email addr is in a field called "Contact email"
I don't want the email as an attachment, I don't need the message or even the subject included (although wld be nice as will be fairly static i think :) )
All I want is it to bring me up the Outlook New Message with all the recipients in it, I can add the subject and message then if necessary. Oh and there's more !! ;) I need the recipients to be in the bcc field as I don't want them all to see each other, the to: field can be blank or the first recipient whatever.
Anybody got any code that I can just copy and paste !! and that will miracleously work (sp?) ... Hope u can follow what I;m looking for..
Many thanks in advance
D
Hi,
On my form I have an option group with 4 radio buttons. Depending on the button selected certain fields become visible. This works all fine. Problem is though that I don't know how to store the selected value. At the moment, every time you open the form you have to select the same button to see the info stored in the fields that become visible. I guess that I'll have to somehow store the value for the selected buttons in the table. But How? Do I refer to the frame and store the value corresponding to the number of button (1, 2, 3 or 4) or do I store the balue for each button seperately?
I've played with it for too long now and cannot find the golden tip. Please advice.
Thanks,
Walt
I'm developing a database where people basically can fill in an order form and then the database can track if the order has been placed, when it came in etc.I have a form called frm_Suppliers to be Ordered that runs off a query. The tables that relate to the query are called tbl_Products, with a primary key called ID and tbl_Suppliers, with a primary key called Supplier ID.
The form called frm_Suppliers to be Ordered, shows a list of suppliers that have open orders. Each supplier may have several orders open. I want to be able to click a button after each supplier name and bring up a continuous form that relates to all the records from that supplier that have open orders.
I have tried using the code found in the thread called "open a form based on the record that is selected in current form" from 06-08-2011, however it will only work when my form lists each open order separately, not when they are grouped by supplier.
when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.
Code:
oListBox.Selected(lngRow) = True
However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).
Code:
Print oListBox.Selected(lngRow)
->True
Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.
Code:
oListBox.Requery
This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.
i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.
I have about 100 quiz questions in a MDB File. I need them to be randomized.
I've done a pseudo-randomization of the questions by a certain column by using the ascending/descending order, but it is awfully obvious!!!
Is there a way to do it so it's like an ipod shuffle. Is there a way to randomly mix all the questions up WITHOUT the need to randomize by virtue of ascending/descending order of a column?
Thanks
My query randomizes the records, but i want it to be different
each time the page loads/refreshes.
Query:
Code:"SELECT * FROM tblImages ORDER BY Rnd(ID)"
I have tried many variations but cannot find one that works,
anyone here know of one that will achieve this.
Thanks
Shem
Hello all,
I need to randomise participants in an Access 2000 db into four groups. Let's say these are 1, 2, 3 and 4. This in itself is not the problem. The problem is that I need to keep the numbers of participants in each group approximately equal, say to within a tolerance of 4.
Can anyone point me in the right direction please?
Thanks very much.
I have read a lot about the rnd function to succesfully randomize the order of records in a db.
I want to randomize 2 columns separately . These are Q's and A's so if the db is 2 fields (the second -small- letter is supposed to be under Field 2!)
Field1 Field2
A a
B b
C c
D d
etc...
I want it to look like
Field1 Field2
B c
A d
D b
C a
I have managed to construct two separate queries which obviously give 2 random lists for each field but how to put them both together to achieve the above?
I have tried applying a rank order to each list and then linking them on that but the rank number keeps changing hence no link there. All I want is to link them and keep each column in the same order as it has been randomized.
Thanks
I want to return all the records in a table in a random order. I have a query with Expr1:Rnd([RecordID]) sorted ascending as one of the fields. I have a form with this query as the data source.
This works fine, each time I open the form, I get a different record order.
I now want to use Randomize to return a different order each time I run the database.
Using advice from other threads about randomization, I have put the Randomize in the OnLoad event of the form, immediately followed by Me.Requery, but I still get the same first record each time I open the database.
I put Rnd() in a MsgBox after Randomize to see if the randomization is working, and I get a different number every time, it just seems that the Requery is being ignored.
Any ideas?
The queries go like this at present :
SELECT TOP 1 [Table].[QuestionText], [Table].[Answer] AS CorrectAnswer
FROM [Table]
GROUP BY [Table].[QuestionText], [Table].[Answer], rnd([IDQuestion])
ORDER BY rnd([IDQuestion]);
SELECT TOP 3 Table.Answer AS Correct, qQuestionTextAndAnswer.QuestionText, qQuestionTextAndAnswer.CorrectAnswer
FROM [Table], qQuestionTextAndAnswer
WHERE (((Table.Answer)<>[qQuestionTextAndAnswer].[CorrectAnswer]))
ORDER BY Rnd([IDQuestion]);
These queries are displayed now in an Access form "frmQuestions" which is applied to "Table" that has three columns id, text , and answer. The result is one question and four suggested answer with one only being correct.The arrangement of the answers is randomized--
but the choice of the question is not realy random....it always starts with the same question as it relies only on rnd.
can I improve the queries and randomize and rnd in the same ...or else can I switch these queries to be used from vb6 code and achieve that result.
any help would be appreciated.
By the way I am really a beginner at this who is seeking help from the experts.
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
View 11 Replies View RelatedI'm building a Bradford Scoring DB, everything is running smoothly apart from one niggle I would really like to get around and automate.Essentially, I'm hoping to run a difference sum query which is fine when it's on the same row, but the issue I'm having is that I'm trying to calculate this from another row, linked by name. This inturn will workout the difference between the two absence episodes i.e. it's been 80 days since their last absence end.
I did find a method which would view the previous AutoID, but this worked well for the whole table, but not within a query limited to name.The obvious workaround is to allow the team leaders to enter this manually, but this could be a/ open to small errors b/ mildly time consumingI, like most, work with people who want info NOW and with little effort.
How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.
At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:
Code:
UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));
I have a table with 4 columns: ID (1,2,3,4,...),Cases, Hours, RIR. In the RIR column I would like to use the following formula.I would like to use a query to Sum the last 12 rows (after index row 12 of course )
View 14 Replies View RelatedI have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:
Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000
There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:
Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget
Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?
I'm running a query that has a field where I need two criteria show up (regular collections and special collections). Another field I have selects for the date (10/1/2014). The last field is the sum of collections. My problem is that the query is generating regular collections and special collections with each date and a sum of collections. I need the total sum of both collections for the date which would be 4.
AGENCY_CODELINE_ITEMPCA_DATESumOfCOLLECTIONS/ACCOUNTSxxxRegular Collections10/1/20142xxxxspecial collections10/1/20142
I have been trying endlessly to get this working but to no avail. I have looked and used sample codes posted on the net.
I am trying to get top 1 [route_list_code] per [works_order_number]
I have attached three images:
- Query as isQL code
- Query with top n in criteria (pulling wrong information and its just by coincidence that the two records shown are split by group)
The code appears to just pull top # record (not per group)
as is.JPG
SQL code.JPG
query with top n in criteria.JPG
Hi,
Is it possible to update multiple rows in a Table at one time using Update Query?
Ta
Kasey
How to insert x number of empty rows at once in a query? All fields allow null.
Say, if I wanted to "resize" the table to 3000 records...
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
I have a table which shows when a product is available in the stores.
For example.
Product Store Available
A NS Jan, 2013
A BC Marc, 2013
How can I combine the available/store field which just shows
Product Available:
A - Jan, 2013 in NS AND Mar, 2013 in BC.
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105
I am trying to achieve below:
Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104