Queries :: Random Record Pull

Jul 31, 2014

I have two tables, one table Data has member(SUBSCRIBER_ID) data including service rep(UserName). Another table ServCoord has data including service rep and their manager. What I am trying to do is pull 5 random accounts per service rep. I can pull 5 random accounts, but not per service rep. I have currently 77 service reps. Is there any way I can pull 5 random accounts per service rep? Here is my starting point so far.

SELECT TOP 385 [Data].SUBSCRIBER_ID, Rnd(1) AS Expr1, [Data].UserName, Rnd(Len([UserName])) AS Expr2
FROM ServCoord INNER JOIN [Data] ON ServCoord.ServCoord = [Data].UserName
GROUP BY [Data].SUBSCRIBER_ID, Rnd(1), [Data].UserName, Rnd(Len([UserName])), [Data].SUBSCRIBER_ID, [Data].UserName
HAVING ((([Data].UserName)<>"NULL"))
ORDER BY [Data].UserName, [Data].SUBSCRIBER_ID;

View Replies


ADVERTISEMENT

Queries :: Pull Back 100 Random CustomerIDs On Set AttendDate

Jun 5, 2015

I have 3 fields:

CustomerID (Alpanumeric)
AttendDate (General Date)
Area (Alphanumeric)

Is is possible in a query to pull back 100 random CustomerID's on a set AttendDate. For example I have 1000's of Customers who attend each day, I want to do some Data Quality on 100 Random Customers.

View 9 Replies View Related

Queries :: Query To Pull Previous Record

Jan 20, 2014

I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.

I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.

View 14 Replies View Related

Queries :: Join Query To Pull In 1 Record Based On Threshold Reached

Dec 31, 2013

I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?

View 5 Replies View Related

Generate Random Reference No. For A Record

Feb 24, 2006

is there anyway to have the database autogenerate a random 8 digit reference number for each record.. this of course must be unique..

maybe something like

7C7CYU99, or HJUHU889... im sure you get the picture.

View 2 Replies View Related

Forms :: Button To Find Random Record

Jun 10, 2013

How to create a button on my form that would find a random record. I would imagine I will be doing this through VB...

View 14 Replies View Related

Query To Pull First & Last Record?

Jan 4, 2007

I am having trouble. I have a log table and I am trying to pull two records at once. The records im trying to pull is the most recent record update as well as the original record.

Example:

I want to pull for Toysrus the first log intry for this company when I first put the record in. And I want to pull the most recent update that I did for this company. Is there a way to pull both records at the same time? The first and the last?

Any help would be appreciated. THANK YOU SO MUCH!!!!!

View 2 Replies View Related

Can 2 Random Queries Work?

Sep 27, 2005

Hi all,

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

View 1 Replies View Related

Using A String To Pull Up A Record On A Form

Feb 28, 2005

Sorry if this has been asked before but I have searched and couldn't find an answer

I have a string that is set on startup (username) and have another form(hidden) that loads on startup this form contains information on what forms and options the user can access. Just having a little trouble using the string to pull the correct record, the information in the string would be from the primary key field on the table.

Thanks

Ryan

View 2 Replies View Related

Create Random Number To Be Automatically Generated As Default Value For Record ID

May 28, 2015

I've been building a CRM in Access that allows for creating profiles for our accounts. When we add a new account, I'd like an Account ID to automatically generate and look like this: CRMXXXXX where the X's represent a random number. I originally wanted to set the field as autonumber to just count up, but unfortunately I need to append this value to another sales database we have; you can't store the text "CRM" with an autonumber in a table.

Right now, all I've done is place an equation in the default value of a textbox I have on my 'Add Account' form. I then made the control source of the text box the Account ID field. My equation is as follows:

="CRM" & CStr(Int(Rnd(Now())*10000))

This appears to work, as it generates a value in the correct format. However, I've realized that each time I open the CRM, the random numbers start over again and I run into issues of trying to create a new record with the same ID as a previously created record. I assume I need to incorporate some sort of timestamp to it in order to change it.

View 5 Replies View Related

Queries :: Update Field With Random Value?

Jun 21, 2013

Have a table (tblDailyResults) with 4 fields (ID,TestName,Result,TestDate). It contains a snapshot of about 1,000 records. All fields have data except "Result", which is null. I also have a table (tblResults) with 3 fields (ID,TestName,Result), it contains about 100,000 Records of historical results. What I need to do is Update the "Result" field in "tblDailyResults" with a randomly select value from tblResults where the two testname match.

View 1 Replies View Related

Pull Info From One Text Box Forward To New Record

Aug 10, 2005

Looking for an automation solution
currently I have a database for client progress and prescriptions. when the file is opened it is set for data entry so that a clean note form is opened. I have a button that pulls up past notes for their review.
it has been requested that there be a way to pull the last narative field forward to the new record for editing as a number of the features of the narative remain the same.
Currently the only way is for them to open the last seen note hightlight the field and past into the new note. to high level for some of them.
Need to make this an automated feature where they would just click on a button and the process would all occur behind the scean and the note would apper in the new note narative box.
I know that I could tell the form to not be for data entry but then they write over the last note. this needs to be a new record.
Any suggestions greatly appreciated
J

View 1 Replies View Related

Reports :: Button To Pull ID Number Of Record?

Aug 28, 2013

I built a report that provides all of the open support tickets. My idea is to have a button (Which I called "WorkIssue_Button") that displays beside each record that is populated on the report... I need this button to be able to pull the ID number of the record (Called "ID"). I then plan on opening a form that is preloaded to this record. How to pull that information?

View 5 Replies View Related

Queries :: Subquery Top X Of Random Rows Not Working

Jun 2, 2013

I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):

SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY RND(Dupe.GTIN) DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;

The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:

SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY Dupe.GTIN DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;

View 2 Replies View Related

Queries :: Random Sample With Multiple Criteria

May 5, 2014

I have table with [EmployeeName] and [DateReceived] column what I am hoping to accomplish is to get 5 samples of each Employee for each Date received.

View 5 Replies View Related

Last Name Input Form Doesn't Pull Pertinent Record

Mar 24, 2005

I'm a complete newbie at Access. A friend (she's the Secretary of our small town's Service District Board of Trustees) asked me to develop a database for keeping records of property owners here, to facilitate such things as sending the yearly fee letter, tracking mailing addresses and property addresses, whether the fee is paid, etc.

I figured out most of it by "taking apart" a database she uses for another organization. It wasn't easy, but it's all working EXCEPT the thing she uses most: a last name input form (a combo box entry field with a command button) which is supposed to bring up the appropriate "Edit Existing Owner" form by the last name entered (or give an error message if there's no owner by that name, of course). The form works - but simply brings up the last-entered record.

I've looked at the setup in the other database, I see that when I create this form relationship, the VB code is nowhere close to what's in the other database. Trying to use the code from the other database is ineffective, even though the relationship is exactly the same. I'd be happy to have someone look at the forms etc. but the database compressed to a .rar file is 818k which is pretty big - and I'm not sure how to provide just the parts which aren't working.

I can upload the database to webspace and direct someone there if they'd be willing to take a look....

Thanks for any help!

View 14 Replies View Related

Tables :: End User Input To Pull A Specific Record

Dec 5, 2012

I want to use a Form or Report to have the end user enter say a Customer # or the Customer Last Name and then have Access pull and display that record so that the end user can than print all the saved information from that record.

View 2 Replies View Related

Queries :: Generating Random Time From List In Query

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

Queries :: Select Random Records From Unrelated Tables

Aug 9, 2014

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?

View 14 Replies View Related

Queries :: Pull Text From String?

Apr 2, 2015

I have a text field in a Table and on a Query called "Notes" In that field that has data like below:

[04/02/2015:BD] Project is to be assessed by Solutions Planning
[03/27/2015:BD] Project prioritized
[03/14/15:BR] Entered to system

Im trying to find a way to pull just the most recent line of text, in this case

[04/02/2015:BD] Project is to be assessed by Solutions Planning

into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.

View 10 Replies View Related

Queries :: Pull From A Second Column If The First Is Null

Aug 6, 2013

I have 1 query that connects 4 of my tables through a tracking number. I am trying to make it so the value (DStatus) from DTable will populate where a (DStatus) is provided, but where there is no (DStatus) the column is filled in with a (SStatus) from STable. There is always a SStatus for each tracking number, but not always a DStatus, where there is a DStatus, it is more accurate.

Can I do this all in 1 query, whether I have to make a new column within my query for this to work, whether I need to have my query output into a table first.

Is there something I could type into the Criteria field of my query.

I have tried using Nz([DStatus],[SStatus]), an Iif Statement, the IsNull function, etc with no avail.

View 3 Replies View Related

Need Single Report To Pull From 2 Queries

Mar 14, 2012

I have 2 reports that use a separate query to pull their data. One Query is called "Outdoor Lighting by Model Number Full Query".It is very simple, pulling just 4 pieces of data, SQL code is as follows:

SELECT DISTINCT [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity]
FROM [Call Log]
WHERE ((([Call Log].Date) Between [Forms]![Date Range Entry]![Start Date] And [Forms]![Date Range Entry]![End Date]) AND (([Call Log].[Product Type])=4))
GROUP BY [Call Log].[Model #]
ORDER BY Sum([Call Log].Quantity) DESC;

I run a report named "Outdoor Lighting by Model Full Report" which takes the above query. The detail portion of the report only has 3 sections:

Model #, Quantity, Percentage. Quantity I have showing "Sum Of Quantity" and Percentage is showing "=[Sum of Quantity]/Sum([Sum of Quantity])" Therefore, for a particular model # it gives me the number and then calculates what percentage that number is of the total. Works fine.

My second report is laid out exactly the same, except it pulls data from a different query that only returns the top five results. The code for that query is as follows:

SELECT TOP 5 [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity]
FROM [Call Log]
WHERE ((([Call Log].Date) Between Forms![Date Range Entry]![Start Date] And Forms![Date Range Entry]![End Date]) And (([Call Log].[Product Type])=4))
GROUP BY [Call Log].[Model #]
ORDER BY Sum([Call Log].Quantity) DESC;

The second report, appropriately named "Outdoor Lighting by Model Number Top 5 Report" only shows the top 5 and then gives a quantity and calculates a percentage. My problem is that the percentage is calculated based off of the total of just the top 5 query. I need it to calculate based off of the total of the full query.

I currently have percentage doing this "=[Sum of Quantity(Top 5 Query)]/Sum([Sum of Quantity(Top 5 Query)])".What I want it to do is this "=[Sum of Quantity(Top 5 Query]/Sum([Sum of Quantity(Full Report Query)])

View 2 Replies View Related

Queries :: Adding New Records To Table - Random Number No Duplicates

Jul 31, 2013

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.

View 3 Replies View Related

Queries :: Query For Inserting Random Records Into Temporary Table

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

Queries :: Create And Order By Random Number In Query Field

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

Queries :: How To Pull All Records If Distinct Count Is Less Than 1

Mar 18, 2014

I have a table with the following values.

EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION

Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved