Random Query Excluding Selected Group
Sep 24, 2005
I 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..
View Replies
ADVERTISEMENT
Jul 21, 2015
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?
View 3 Replies
View Related
Aug 3, 2005
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
View 1 Replies
View Related
Aug 25, 2005
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
View 5 Replies
View Related
May 9, 2013
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
View 1 Replies
View Related
Aug 8, 2013
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.
View 2 Replies
View Related
Jul 30, 2006
Could someone please help me? I am trying to run a query in which I would like to exclude a particular person's name from the query. For example, I am entering productivity information for each employee and manager each day, however, I only want the employee information to appear in the reports each day. Is there anyway of excluding a name from a query?
Thanks:)
View 2 Replies
View Related
Mar 26, 2006
I have designed an invoice for a project that shows the amount of money needed to be paid, some of this has already been paid and some hasnt, how am i able to make it so that the values that have been paid and therefore have been checked are not include in the query. please help.
View 1 Replies
View Related
Mar 1, 2007
I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?
In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).
View 4 Replies
View Related
Aug 9, 2006
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View 1 Replies
View Related
Jan 16, 2015
Trying to build a CRM system for the office but am getting stuck with the below...
Each company (tblCompany) in the database has at least 1 enquiry (tblEnquiries) linked to it and normally at least 1 (sometimes 0) people (tblPeople).
I have a form which loads company specific information e.g. notes/quotes/orders/people/enquiries. All data is loaded based on the company unique id (c_id).
The attached image shows Company 1 (c_id = 1). It has 6 enquiries. However the subform only displays 5 of these. It does not display any which do not have a person (or p_id) linked to it - this is consistent throughout the database. I have included the table relationships and the enquiry as well in case they are needed!
Searching the net seems to suggest 2 possibilities:
1 - the relationship join type needs to be set to include all from tblEnquiries and only those from tblPeople where joined fields are equal, however changing the join properties does not appear to have an effect.
2 - table field types do not match (all _ID fields are set to number, unique numbers only).
[edit]: am using Access 2010!
I have re-designed the query to pull through tblEnquiries and tblPeople data based on the c_id field on the open form, which is now showing all records, including those not assigned to a person.
However, in the form I can select one from the query datasheet and open to see additional info. Those without a blank p_id number do not open, I receive the error:
"Run-time error '2113': The value you entered isn't valid for this field.
All _id fields are set to numeric so am not sure how to correct this?
View 2 Replies
View Related
Sep 27, 2005
Table1 gives Case Nu.
Table2 gives Case Nu. and EventType.
Table3 gives EventType and Cleared (yes/no field)
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
View 2 Replies
View Related
Jul 23, 2015
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
View 3 Replies
View Related
Oct 11, 2012
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
View 8 Replies
View Related
Aug 9, 2007
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 1 Replies
View Related
Mar 12, 2008
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 3 Replies
View Related
May 14, 2006
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.
View 14 Replies
View Related
Nov 30, 2006
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 6 Replies
View Related
Oct 6, 2006
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
View 5 Replies
View Related
Apr 29, 2015
I am trying to create a database to link to an online auction site (not ebay) and I want to create a query that randomly generates auction end time and auction length for all listings. I have tried int(rnd()) and various versions but can only generate the same number for all fields in the query.
View 1 Replies
View Related
Jul 16, 2015
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());
View 2 Replies
View Related
Jan 2, 2015
I'm trying to create a query that generates random numbers for each record, sorts them by that field, then selects the top record. This should randomize the record being selected.
I can use the Rnd([ID]) function which does appear to generate a random number. Problem is that each time I exit the program and come back in, it always selects the same record. When I remove the Top = 1, to show all the records, every row does have a different random number but it does not appear to be sorting by this field.
If I run the query, here is the number I get: 0.98609316349029
Exit the program, restart, and run the query again: 0.98609316349029
If I refresh the query, the second and third time does appear to be random but the first result is always the same. how to generate truly random numbers?
View 7 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Sep 20, 2005
hello,
I want a query to run and output values on a certain date. I have created a form and inserted an activex component 'Microsoft Date & time picker'.
But im stuck on how to get this component to be recognised by the query! Can anyone please help me out or suggest a different way?
thanks
iso
View 1 Replies
View Related
