Query for random values multiple records

I have a table of names (lots of names! Too many to write individual queries 
for...). I want three random non-repeating numbers between 1 and 7 for each 
of these names. Output example:
Name1      3
Name1      2
Name1      5
Name2      1
Name2      5
Name2      4

From reading on the boards I was able to come up with a way to generate 
output such as this:
Name1    3
Name1    2
Name1    5
Name2    3
Name2    2
Name2    5

However, that will not work for what I want the random numbers for. I need a 
different set of each random non-repeated number set for each name.
Thanks!
0
Utf
4/17/2007 5:26:01 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
887 Views

Similar Articles

[PageSpeed] 15

On Mon, 16 Apr 2007 22:26:01 -0700, Clddleopard
<Clddleopard@discussions.microsoft.com> wrote:

>However, that will not work for what I want the random numbers for. I need a 
>different set of each random non-repeated number set for each name.
>Thanks!

You can use the Top Values property of a query, with help 
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double 
Static bRnd As Boolean 
If Not bRnd Then 
  'Initialize the random number generator once only 
   bRnd = True 
   Randomize
End If 
RndNum = Rnd() 
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in 
your table - this forces Access to give a different random 
number for each record.

Sort the query by Shuffle, and set its Top Values property 
to the number of records you want to see. In your case this will need to be a
Subquery.

             John W. Vinson [MVP]
0
John
4/17/2007 3:26:50 PM
Ok, that worked well, except I get decimal values and I need whole numbers 
between 1 and 7. What modifications should I make?

"John W. Vinson" wrote:

> On Mon, 16 Apr 2007 22:26:01 -0700, Clddleopard
> <Clddleopard@discussions.microsoft.com> wrote:
> 
> >However, that will not work for what I want the random numbers for. I need a 
> >different set of each random non-repeated number set for each name.
> >Thanks!
> 
> You can use the Top Values property of a query, with help 
> from a little VBA. Put this little function into a Module:
> 
> Public Function RndNum(vIgnore As Variant) As Double 
> Static bRnd As Boolean 
> If Not bRnd Then 
>   'Initialize the random number generator once only 
>    bRnd = True 
>    Randomize
> End If 
> RndNum = Rnd() 
> End Function
> 
> Then add a calculated field to your Query by typing
> 
> Shuffle: RndNum([fieldname])
> 
> in a vacant Field cell, where [fieldname] is any field in 
> your table - this forces Access to give a different random 
> number for each record.
> 
> Sort the query by Shuffle, and set its Top Values property 
> to the number of records you want to see. In your case this will need to be a
> Subquery.
> 
>              John W. Vinson [MVP]
> 
0
Utf
4/17/2007 3:46:02 PM
On Tue, 17 Apr 2007 08:46:02 -0700, Clddleopard
<Clddleopard@discussions.microsoft.com> wrote:

>Ok, that worked well, except I get decimal values and I need whole numbers 
>between 1 and 7. What modifications should I make?

Well... that's a bit different. If you really mean RANDOM numbers, then you
can and will get duplicates!

I'm going to have to think about this and post back later...

             John W. Vinson [MVP]
0
John
4/17/2007 4:56:30 PM
Also, BTW, the numbers need to be whole numbers.

"Clddleopard" wrote:

> Yeah, the random part was pretty easy (I actually found your module on the 
> board and tried to use it before). It's the non-repeating part that's getting 
> me! I need to tell it to check the previous values and try again if it gets 
> an equal value. Thanks for working on it!
> 
0
Utf
4/17/2007 6:02:10 PM
On Tue, 17 Apr 2007 10:12:02 -0700, Clddleopard
<Clddleopard@discussions.microsoft.com> wrote:

>Yeah, the random part was pretty easy (I actually found your module on the 
>board and tried to use it before). It's the non-repeating part that's getting 
>me! I need to tell it to check the previous values and try again if it gets 
>an equal value. Thanks for working on it!

I'd suggest using a table of sequential numbers, and "shuffling" it using the
techniques in the code. How to associate the sequential numbers with your
record isn't quite clear to me though!

             John W. Vinson [MVP]
0
John
4/17/2007 7:15:50 PM
On Tue, 17 Apr 2007 12:50:03 -0700, Clddleopard
<Clddleopard@discussions.microsoft.com> wrote:

>One thought I had was to write some sort of code to run the following query 
>multiple times, replacing the Where information inside the quotes with a 
>different name each time, just going down the row of the table.

You're thinking procedurally. Queries AREN'T procedural - the work on sets! 

Just use a JOIN to join the two tables:

INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4 INNER JOIN  [Animal ID information]
ON [Animal ID information].[Animal Name] = Query4.[Animal Name];

assuming that the animal name exists in Query4 (which I cannot see).

             John W. Vinson [MVP]
0
John
4/17/2007 8:37:34 PM
Reply:

Similar Artilces:

COPY MULTIPLE TRANSACTIONS
Anyone aware of a simple way to select a range of transactions and move them to another accout? Thanks, JoeM wrote: > Anyone aware of a simple way to select a range of transactions and > move them to another accout? One at a time is the only way, as far as I know. -- Steve Bell New Life Home Improvement Arlington, TX In microsoft.public.money, JoeM wrote: >Anyone aware of a simple way to select a range of transactions and move >them to another accout? No, sorry. Actually using the advanced search/replace may work for what you are trying to do. > This is a ...

Sorting multiple colums ???
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01CA1F33.51D7A2C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a 5 column worksheet. I want to sort the first column A to Z Then, without changing the first column, I want to sort the second = column A to Z. I have read the Help page but I can not seem to "select" the columns. Help ...... TIA Drew ------=_NextPart_000_0006_01CA1F33.51D7A2C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOC...

SQL Filter Query for Time
Is there a way to write an SQL Filter Query on time, not date? I want to see all emails in my inbox that I received between 10 AM and 11 AM, regardless of what day. -Laurens de Jong, from Detroit, MI. ...

Is there a keyboard shortcut for "paste values"?
or can I define one ? I'm really using a lot of paste values, and it's pain in the *** to use right click menu every time Regards, Sirritys Ctrl+V -- Arvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154506998.485777.116140@s13g2000cwa.googlegroups.com... > or can I define one ? > > I'm really using a lot of paste values, and it's pain in the *** to use > right click menu every time > > Regards, > Sirritys > It is quite easy to define a...

AutoFilter list of values
When I activate the auto filter, a drop down lists the unique values within the column. How could I get this list of unique values? Hi RJH, As far as I know this can only be done with advanced filter. Select Data-Filter-Advanced Filter and check the Unique records only box. Cheers, JF. Another way to extract the list of unique values Suppose the data is in col A, A1 down Put in C1: =IF(COUNTIF($A$1:A1,A1)>1,"",ROW()) Put in B1: =IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1 :A1)),C:C,0))) Select B1:C1 and copy down till the last ro...

Display percent and value on stacked column chart
I'm displaying cumulative $ totals from 3 sales groups on a 100% stacked column chart. We've recently exceeded 100% of our total $ goal so the chart isn't able to display the amount above 100%. If I switch to a standard stacked column chart, I can't choose the display percentage option. I downloaded the Rob Bovey's Chart Labeler but I can't get it to display what I want. Perfect world would be displying the dollar value of each sales group in the column segments and showing percent of total goal on the y-axis. I would even be okay with showing the dollar valu...

Recording house sale
I have an asset account with a house I just sold. I cannot figure out how to record the sale. thanks In microsoft.public.money, JIM wrote: >I have an asset account with a house I just sold. I cannot figure out how to >record the sale. thanks See http://umpmfaq.info/faq.html#Q110 ...

Changing Of Column Values ?
Hello, Using Excel 2007 and Windows7 This sure seems "funny," and was hoping someone might explain it for me: In B1 I have the value 154.22 In B2 I have the value 33.74 In E1 I have 154000000 In E2 I have 33740000 I copy from Column E, and do a Paste Special in Column B Column B looks correct, as it has in: B1 the value 154220000 B2 the value 33740000 But- E1 has changed to 1.5422E+14 E2 has changed to 3.374E+13 In the Paste Special dialog box that came up I selected for Paste: Values, and Operation: None As the values in B look correct, I'...

Printing from Multiple WSs
I've just moved up to XL2002 In previous versions it was possible to print, in one go, various sections of various worksheets via the *REPORT* method but I do not see an equivalent to this in Vn 2002. I'm sure that there must be something similar so what am I missing? TIA Richard Hi you have to install the report manager addin (you should find it on the MS site) -- Regards Frank Kabel Frankfurt, Germany "Richard" <walcott_r@intnet.co.uk> schrieb im Newsbeitrag news:cnlauc$ig8$1@ctb-nnrp2.saix.net... > I've just moved up to XL2002 > In previous versions i...

Query Box...
I have a Report named Drilling Invoice. I want to have a query box that comes up when I click on the report and has a place for "BillTo:" , "WellName:" , and "Invoice#". Now I have the paramform box and works fine but I want it to be on one popup box... Does this make sense? Thanks Stephen -- Message posted via http://www.accessmonster.com "stephendeloach via AccessMonster.com" <u32891@uwe> wrote in message news:738b89ce9012f@uwe... >I have a Report named Drilling Invoice. I want to have a query box that >comes > up when I click...

multiple email output to recipient
All of a sudden, whenever I send a email to a recipient they will receive multiple copies, as many as 7 at one time. I have not made any changes in Outlook, but recently have been getting a "Server timout" from my ISP. Is anyone having this problem or do you have a solution? ...

Record Ordering on Mail Merge
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am performing an email merge letter. I do not want to send out all of the records at once, so I am using the Custom Option on the Complete Merge step. I just have one question. If I have 200 records with names and email addresses, what determines the order of the records as in what dictates which record is number 1 or number 7 or number 8 or number 200? <br><br>When I attempted to send first the records 1-60, then 61-120, I got a bunch of duplicates. I also got a few records that did not belong to...

Multiple Files, Duplicate Entries
How can I combine multiple Excel files and get rid of duplicate entries? Hi PMSunshine77 You don't give much information, but start here http://www.rondebruin.nl/copy3.htm Then see See Chip Pearson's site http://www.cpearson.com/excel/duplicat.htm My Add-in have a duplicate option also http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "PMSunshine77" <PMSunshine77@discussions.microsoft.com> wrote in message news:F0098DA7-4C21-46A9-8FBF-63D5CC85BDF2@microsoft.com... > How can I combine multiple Excel files and get rid...

multiple check box change variable
Hi, i found an earlier post containing the following macro below that should allow me to create multiple checkboxes in 1 column going down from row 5-60. However for each one i want the cell it references to increase by 1 and match the row for the checkbox. The only problem is that i dont know what to do with this. I want to create multiple checkboxes going down from D5 to D55 and referencing E5 to E55. Where do i input this macro ? Do right click on the worksheet name and paste it in the 'view code' ? Or should i create a macro and assign it to the 1st checkbox and then just co...

Protecting value in cells to change
Hello Excellers, I have the next problem, when i have a value on Sheet1!C3, Sheet2!cells C3:C10 may not bechanged. When sheet1!C3 is empty, Sheet2!cellsC3:C10 can be changed. How can i accomplish this action ? Greetz, Reijer You can do this with data validation but there are limits as to how much "protection" this will give you. You can try this out and see if it meets your needs. Create this defined name: Goto Insert>Name>Define Name: SH1C3 Refers to: =Sheet1!$C$3 OK Select the range Sheet2 C3:C10 Goto Data>Validation Alow: Custom Formula: =SH1C3="" U...

Total the Value of Orders in a given month
Hi, Thanks in advance for any help you can give... I am running Exce 2002.... I need to calculate the total sales for a given month from a sheet Each order is inputed on a separate row. Column E is the date of eac order... Column L is the $ amount... The sheet spans about 3 years... I need to return the sum of sales for each month to a cell.. -- Message posted from http://www.ExcelForum.com Hi, If we use June, 2004, as an example of the month whose total sales we want, try: =SUMPRODUCT((MONTH(E2:E1000)=6)*(YEAR(E2:E1000)=2004)*(L2:L1000)) Adjust the ranges accordingly. Hope this hel...

Page Setup for multiple worksheets macro problem
Hi, I've recorded a macro doing the following: group all worksheets, select file|pagesetup, and then select my formatting, etc. The formatting was successfully applied to all worksheets at this stage. However when I try to run the macro, the formatting is only applied to the first worksheet. What gives? Thanks. Allan Are you Twirly? Can you use a macro? Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets With wks .PageSetup.PrintArea = "A1:X99" End With Next wks End Sub You have to grou...

Random hroup of items on sale for a specified period
As an offshoot to the other topic about wizard-based time-specific price changes.......... I use tried to use the wizard for a specific group of items and found that I could only use it for entire departments. I wound up using the Pricing tab in the Item Properies window to set the "Thius Item On Sale" flags for the start and stop dates for each item This worked perfectly (items came off-sale as scheduled) But....... it was very time consuming Is there an easier way to do it when the items are 'random'.? -- WineGuy Not really. You can select items by department/...

Summing same sheet $A$1 in multiple workbooks
H E L P .... I have 26 different files that each have a 9 sheet workbook. I need to "summarize" all of the files (workbooks) into a consolidated workbook with the 9 worksheets. I know if you wanted to sum $A$1 in all of the worksheets, I can place all of the worksheets between two blank worksheets called A and one called Z and write the cell to sum(A:Z!$A$1) Is it possible to do the same with files??????? Thank Jerry Dear Jerry It is possible to reference external workbooks in your formula Using a sum function as an example your formula would look something like this... =...

How can I duplicate records?
Hi. I have Table1 and Table2. Table1 and Table2 have a relationship. One record in Table1 has many records in Table2. I have the need to duplicate all records from Table2 according to a criteria and of course I also have to duplicate the record in Table1. How can I do it? regards, Marco Use a union query. -- KARL DEWEY Build a little - Test a little "Marco" wrote: > Hi. I have Table1 and Table2. > > Table1 and Table2 have a relationship. One record in Table1 has many records > in Table2. > > I have the need to duplicate all records from Table2 accordin...

Multiple values in a crosstab
I have database where each emloyee enters date and hours worked, I then calculate hours by rate to give value. I need a crosstab to show by emloyee(column) and by date (row) the total hours and total value. What would be the most elegant way of accomplishing this. Thank You -- Alex Paszkiewicz I've done this a couple of times by creating a pivot table in excel but basing the data on my sql statement. During creation of the pivot choose external data source and use the SQL statement. It gives the enduser a dashboard display where they can manipulate some of the variables. "...

help with a scatter graph of conditioned values
I'm a big newbie in matter of charting and vba...I hope somenone can help me....here is my problem I need to plot a XY scatter graph. X and Y data values are mutually dependents one each other and they change under an extra condition that I want to represent qith a macro control. Hmmmm...let's try to give you an example: first of all I need to define EXTRA CONDITIONS as follows name S A 1 B 1,15 C 1,20 D 1,25 E 1,35 ..... ..... My goal is to have a control in cell C1 that traduces "if 'name'=A then 'S'=1, if &...

sumproduct 2 criterias, multiple tabs
hi guys, i need sumproduct to validate two things for it to produce a sum 1. needs to look at all tabs (001-013) and see if the month actually has a value and if it matches the value in D8 2. needs to again look at all tabs and look at range $C$24:$C$37 has a value and match it to the range in B55:B78 3. if date & code match, produce the sum so TABS 01-013 Month(H:6) = Date C24:C37 = Code match J24:J37 = Total Value of that code Where the formual will sit (range C55:N78) B55:B78 = Code the tabs will validate against C8:N8 = Month(Date) of which the tabs will validate against i have t...

Queries to Views?
I posted this question in a SQL Svr newsgroup and was basically slapped down for doing so. I was told this is an "Access" issue and I should try an "Access" group (man, those guys can be sensitive!). So, here we go... I've tried a few different approaches but, each time I try to convert an access query to a view in SQL Svr 2000, the result is a table not a view. Does anyone know how to take the dozens of queries I've created and move them into SQL server as views without copying and pasting the SQL statements into query analyzer one at a time? I've ...

Selecting Random Recipients in Mail Merge = Word 2008 & Excel
Version: 2008 Processor: Intel I have a data source created in Excel. I want to do Mail Merge in Word and need to select only 20 of the 67 families in the data document. On PC is a simple checkbox but this is not happening on the Mac. You're right, Mac Word does not have the checkbox method of hand-picking recipients for a merge. If there's no other common data shared by the records you want to include, the most common workaround is to add an additional field to the record source. Simply call it 'Include' or something similar then put an 'x' or some other ch...