Creating a rank order

[Supermansteel]

I am trying to run an append query and running a RND function to pull 150 branches out of about 2500. However, I am trying to assign it a numerical value from 1 thru 150. How can I assign each of these 150 results a Rank from 1 to 150?

[Stewart Ross]

Hi Supermansteel. In an Access query, Rnd won't help you here, because it is evaluated just once in each query run. So although Rnd does indeed generate different pseudo-random values, when used in a query it will be the same value for every row of the query involved until the next time the query is run, when another random value will be chosen but repeated down all rows.

Perhaps the simplest way to generate a table of 150 randomly-chosen values is to use Excel to do so, generating a table of random numbers in the range 1 to 2500. It's the way I do such one-off selections myself (when doing random samples of a student population for instance). The beauty of using Excel is that the numbers will be different each time you open the worksheet (and indeed you can change them again in the open worksheet if you force recalculation by pressing F9, or make any other change that will recalculate cells).

-Stewart

[Stewart Ross]

...but then there are ways and means to force Access to evaluate random for every row, not just once. Lightbulb came on!

The following public function does so, if you evaluate the expression and include the name of any field at all from your underlying query:

Public Function ReturnRandom(anyfield) As Double
  ReturnRandom = Rnd()
End Function

Use:

SELECT int(ReturnRandom([name of a field]) * 2500) AS randomvalue, ...

You could use the revised random query to make a temporary table, then after this use the static data in the temporary table for selecting the ranked rows. You will otherwise not be able to sort the randomly-generated numbers into rank order, as trying to sort in the generating query will simply regenerate different random numbers.

-Stewart

[ADezii]

Public Function fReturnRandom([<any field>]) As Integer
  fReturnRandom = Int(Rnd * 2500) + 1
End Function

Select fReturnRandom([<any field]) As MyRandom

[Supermansteel]

Public Function fReturnRandom([<any field>]) As Integer
  fReturnRandom = Int(Rnd * 2500) + 1
End Function

Select fReturnRandom([<any field]) As MyRandom

I think maybe I am retarted sometimes especially when it comes to VB. I tried to run this code in the system, but maybe I didn't do it correctly because I couldn't get it to work. Also the 2500 branches is always changing as we are always opening new branches. I did end up playing with it for at least an hour or so and numerous times coming back to it for one simple thing, but I did end up finding an alternative. I created in my Append query that Rank column as "" for a blank field. Then I pulled up the table and went to where it started being blank and typed a 1 there and it automatically shifted the next block to 2,3,4... just by hitting the down arrow. So, I created 1-150 in 3 seconds that way. I definately think this is pretty manual when it could be more automated but I guess this works. I am pretty new with Access and I don't know if you guys have a better suggestion about the following: This is why I was creating a rank order in the first place but thought it was a good idea. My area is currently pulling 150 random samples for Paidout, Rejects, Originations, simple right? I run the Initial Queries to get them the Data and someone else runs the samples, farely easy. However, starting in September they want to switch the sampling methodology to pulling to 2 Samples for each Paidout, Reject, and Origination out of 150 Branches (or 900 total Samples) and it has to be branches that haven't been used for this same year. So What I then created is an unmatched Query and pulled 150 Random Branches out of list of all 2500 Branches in the Network and matched it up to ones not already done for the year. The Reason I was trying to create a rank column because i was going to create about 450 Append Queries (150 for each Paidout, Reject, and Origination) pulling two samples out of each of the 150 branches and using the rank column to match up to what row in my table it would have to match up to pull from that random state and branch. Again, I haven't been working with Access for that long and this is the only way I could think of utilizing this the best way and once I create the first query it would be easy to create the other 150 of them just changing the rank # for each of them. Although putting the Macro together might take me a little while longer. Is this the most efficient way of handling these 150 branches and pulling samples out of them?

[Stewart Ross]

Hi Supermansteel. The code is just a user-defined function, in which Adezii and I have used some placeholders for things you would need to specify or replace yourself - for example the 'dummy parameter' listed as ([<any field>] in Adezii's post. You would place the function in a public code module - one which is visible from the Modules tab of Access, or you would create a new module if you don't have one so far.

The Select bit you have quoted from Adezii's post is the start of an SQL statements showing how the function is called. Again, you need to expand on this somewhat - it is not complete as it stands, it is a pointer to you showing how to use it.

On your current approach of using consecutive numbers, if this works for you then fine - but it is not a random sample. If your data is ordered in some way - by branch number, for instance, or by branch name - using consecutive numbers to select a sample will mean you are selecting an ordered subset of the data, not a random sample at all. This is not a way forward so far as I can see.

The random function provided in post 3 left the value of the random number unchanged as a double-precision value between 0.0 and 1.0. Adezii's refinement was to take this and return a value as an integer between 1 and 2500 to rank all branches, based on the data you provided about number of branches.

It remains the case that to select a random sample you will need to generate a set of random numbers.

I will attach a sample database (Access 2003 format) which shows how a random sample of 150 can be selected from a set of branches (2500 in the test table). The random function has been modified slightly in the test database to allow any value of multiplier and not just 2500. The macro in the database runs a maketable query to create a static table (one whose random rankings do not change every time you open the query), and the macro then runs the selection queries on the test data to select the sample of 150 branches. Feel free to check out how the queries work, and how the random function is called.

Note that the random number assigned is often duplicated (it is not a ranking as such but a randomised value between 1 and 2500), but as you will see from the test data the sample itself is randomly chosen regardless of what the random values actually are.

-Stewart

[Supermansteel]

Hi Supermansteel. The code is just a user-defined function, in which Adezii and I have used some placeholders for things you would need to specify or replace yourself - for example the 'dummy parameter' listed as ([<any field>] in Adezii's post. You would place the function in a public code module - one which is visible from the Modules tab of Access, or you would create a new module if you don't have one so far.

The Select bit you have quoted from Adezii's post is the start of an SQL statements showing how the function is called. Again, you need to expand on this somewhat - it is not complete as it stands, it is a pointer to you showing how to use it.

On your current approach of using consecutive numbers, if this works for you then fine - but it is not a random sample. If your data is ordered in some way - by branch number, for instance, or by branch name - using consecutive numbers to select a sample will mean you are selecting an ordered subset of the data, not a random sample at all. This is not a way forward so far as I can see.

The random function provided in post 3 left the value of the random number unchanged as a double-precision value between 0.0 and 1.0. Adezii's refinement was to take this and return a value as an integer between 1 and 2500 to rank all branches, based on the data you provided about number of branches.

It remains the case that to select a random sample you will need to generate a set of random numbers.

I will attach a sample database (Access 2003 format) which shows how a random sample of 150 can be selected from a set of branches (2500 in the test table). The random function has been modified slightly in the test database to allow any value of multiplier and not just 2500. The macro in the database runs a maketable query to create a static table (one whose random rankings do not change every time you open the query), and the macro then runs the selection queries on the test data to select the sample of 150 branches. Feel free to check out how the queries work, and how the random function is called.

Note that the random number assigned is often duplicated (it is not a ranking as such but a randomised value between 1 and 2500), but as you will see from the test data the sample itself is randomly chosen regardless of what the random values actually are.

-Stewart

Wow, that was easier than I thought to create the Random Branches and Random Ranks. I have gotten a pretty big headache trying to figure it out all day. Thank you very much, that works well....


Supermansteel

[Supermansteel]

Hi Supermansteel. The code is just a user-defined function, in which Adezii and I have used some placeholders for things you would need to specify or replace yourself - for example the 'dummy parameter' listed as ([<any field>] in Adezii's post. You would place the function in a public code module - one which is visible from the Modules tab of Access, or you would create a new module if you don't have one so far.

The Select bit you have quoted from Adezii's post is the start of an SQL statements showing how the function is called. Again, you need to expand on this somewhat - it is not complete as it stands, it is a pointer to you showing how to use it.

On your current approach of using consecutive numbers, if this works for you then fine - but it is not a random sample. If your data is ordered in some way - by branch number, for instance, or by branch name - using consecutive numbers to select a sample will mean you are selecting an ordered subset of the data, not a random sample at all. This is not a way forward so far as I can see.

The random function provided in post 3 left the value of the random number unchanged as a double-precision value between 0.0 and 1.0. Adezii's refinement was to take this and return a value as an integer between 1 and 2500 to rank all branches, based on the data you provided about number of branches.

It remains the case that to select a random sample you will need to generate a set of random numbers.

I will attach a sample database (Access 2003 format) which shows how a random sample of 150 can be selected from a set of branches (2500 in the test table). The random function has been modified slightly in the test database to allow any value of multiplier and not just 2500. The macro in the database runs a maketable query to create a static table (one whose random rankings do not change every time you open the query), and the macro then runs the selection queries on the test data to select the sample of 150 branches. Feel free to check out how the queries work, and how the random function is called.

Note that the random number assigned is often duplicated (it is not a ranking as such but a randomised value between 1 and 2500), but as you will see from the test data the sample itself is randomly chosen regardless of what the random values actually are.

-Stewart

I guess now the follow up question is the Second part of what I am trying to accomplish here. It worked well to pull out the 150 Random branches, but now i have to pull 2 Samples out of everyone one of these branches. Because this is the new Sampling method the business wants to take it, so they are hitting at least 50% of all branches throughout the year. The way I was trying to get it to work was create a Union all query to combine the first 10 branches and i had created 15 of them total. But the problem with this way, the Random sample out of each branch wasn't working. Throughout the Union all query there can only be 1 Order by RND([App]), which that 1st union would pull a Random Sample but all the other 9 kept pulling the same accounts every time and not pulling it as random. And I kept getting an error message everytime I tried to have more than 1 Order by statement. Is there no better way of doing this than doing it the really long and hard way of creating 150 Append Queries and then creating the Macro for all 150....That seems to be taking forever at the rate I am going.

[Supermansteel]

Hi Supermansteel. The code is just a user-defined function, in which Adezii and I have used some placeholders for things you would need to specify or replace yourself - for example the 'dummy parameter' listed as ([<any field>] in Adezii's post. You would place the function in a public code module - one which is visible from the Modules tab of Access, or you would create a new module if you don't have one so far.

The Select bit you have quoted from Adezii's post is the start of an SQL statements showing how the function is called. Again, you need to expand on this somewhat - it is not complete as it stands, it is a pointer to you showing how to use it.

On your current approach of using consecutive numbers, if this works for you then fine - but it is not a random sample. If your data is ordered in some way - by branch number, for instance, or by branch name - using consecutive numbers to select a sample will mean you are selecting an ordered subset of the data, not a random sample at all. This is not a way forward so far as I can see.

The random function provided in post 3 left the value of the random number unchanged as a double-precision value between 0.0 and 1.0. Adezii's refinement was to take this and return a value as an integer between 1 and 2500 to rank all branches, based on the data you provided about number of branches.

It remains the case that to select a random sample you will need to generate a set of random numbers.

I will attach a sample database (Access 2003 format) which shows how a random sample of 150 can be selected from a set of branches (2500 in the test table). The random function has been modified slightly in the test database to allow any value of multiplier and not just 2500. The macro in the database runs a maketable query to create a static table (one whose random rankings do not change every time you open the query), and the macro then runs the selection queries on the test data to select the sample of 150 branches. Feel free to check out how the queries work, and how the random function is called.

Note that the random number assigned is often duplicated (it is not a ranking as such but a randomised value between 1 and 2500), but as you will see from the test data the sample itself is randomly chosen regardless of what the random values actually are.

-Stewart

Actually I figured out a slightly better way of doing this. I have created the Random query for the Branches, but instead of making it 150 Branches its now pulls only 1 Branch and then I linked that up to my append query pulling 2 Samples. So i only need those 2 Queries and then what I could do it set up those 2 queries 150xs in my macro to run. so it won't be as bad as I originally thought of creating 150 append queries for all 150 Branches. Any other Ideas?

[Stewart Ross]

Sorry, Supermansteel, you've lost me with this one. I do not know any longer how many samples you need of what and for what purpose.

The select query which selects 150 branches in the sample database works using the 'SELECT TOP' SQL qualifier (SELECT TOP 150 in this case). The 150 can be changed to anything you like - 2, 5, 150, whatever.

If I understand what you have said about making sure that branches are not sampled again in any one year you really need a 'branch last sampled' table to record the date on which a branch was last sampled. There will be a 1-m relationship between branch and branch last sampled.

I am not at all clear why you need to run something 150 times (or any number of times) in a loop. I think you may be mistaking what a random sample is; there is no more (and no less) randomness in one sample of size 150 than in picking 150 samples of size one. What I am getting at is that picking 150 random samples one at a time in a loop is no different to selecting 150 branches in one operation at random.

Coming at this from another direction, in the sample database the macro runs two exemplar select queries - one of which is the SELECT TOP 150, and the other of which is the list on which the SELECT 150 operates which lists all 2500 branches sorted in order of their randomised number. If you need to ensure that all 2500 are sampled randomly in a year you actually have one basis for selecting all branches at random right there in that one query! You could simply take the 2500 randomly-ordered branches and assign sample dates there and then, knowing there is no geographic or other bias in the ordering.

Another thing is that each time you rerun the maketable query in the example (which the macro does every time it is run) the branch selections change - because the random values change. The subsequent table made is static (at least until you rerun the macro) to allow ordering and selection from the randomised set.

If you can help me to understand what you really need to achieve I will do my best to assist you, but I must admit I am a bit lost with this one...

-Stewart

[Supermansteel]

Sorry, Supermansteel, you've lost me with this one. I do not know any longer how many samples you need of what and for what purpose.

The select query which selects 150 branches in the sample database works using the 'SELECT TOP' SQL qualifier (SELECT TOP 150 in this case). The 150 can be changed to anything you like - 2, 5, 150, whatever.

If I understand what you have said about making sure that branches are not sampled again in any one year you really need a 'branch last sampled' table to record the date on which a branch was last sampled. There will be a 1-m relationship between branch and branch last sampled.

I am not at all clear why you need to run something 150 times (or any number of times) in a loop. I think you may be mistaking what a random sample is; there is no more (and no less) randomness in one sample of size 150 than in picking 150 samples of size one. What I am getting at is that picking 150 random samples one at a time in a loop is no different to selecting 150 branches in one operation at random.

Coming at this from another direction, in the sample database the macro runs two exemplar select queries - one of which is the SELECT TOP 150, and the other of which is the list on which the SELECT 150 operates which lists all 2500 branches sorted in order of their randomised number. If you need to ensure that all 2500 are sampled randomly in a year you actually have one basis for selecting all branches at random right there in that one query! You could simply take the 2500 randomly-ordered branches and assign sample dates there and then, knowing there is no geographic or other bias in the ordering.

Another thing is that each time you rerun the maketable query in the example (which the macro does every time it is run) the branch selections change - because the random values change. The subsequent table made is static (at least until you rerun the macro) to allow ordering and selection from the randomised set.

If you can help me to understand what you really need to achieve I will do my best to assist you, but I must admit I am a bit lost with this one...

-Stewart

Sorry about that. I have spent most of week last week and so far the begining of this week working on this and it has slowly been coming easier and easier, but quite a headache. Right now the way Samples are run is that I run a Append Query pulling out 150 totally Random Samples (using the RND Desc function) for there Group and then i have created the full Database for them to work on the forms and reports and all of that. The way my business is wanting to move forward with the sampling methodology is that they want to be able to take random Samples from specific branches. So, that they hit about 50% of all the branches during the year from January through December and not doing the same branch within a given year. There group pulls accounts and does an audit review on each of the branches selected. Given thee amount of people in there group they want to start this by pulling about 150 branches/month and pull 2 Reject Samples, 2 Paidout Samples and 2 Originations from each of 150 branches or 900 total samples/month. However the Rejects, Paidouts, and Originations are different tables and I know Rejects Table is about 30 columns long and 500k rows and Paidouts table is about 150k rows. That breaks down where they are going, I hope that makes sense. Now I will try to explain what i have done: I currently run a query each month that stores a recent list of all Branches (2500 or so) that have a date of say 7/1/2008, 6/1/2008, etc. I always set everything up on the 1st of every month. I then ran a unmatched query,
and what I was originally doing is a top 150 to run my 150 and appending those results to a table storing just those 150 branches and the dates. Then I was taking those branch results setting up another append query and matching those 150 branches with say the Rejects Table and pulling 2 Results out for that branch based on rank. So Rank 1 was the first branch i appended in this table and i took rank 1 and matched that up to my rejects table and ran 2 random samples based on that branch. I essentially was in the process of creating literally 150 append queries just changing the rank # for each of them. Stupid right? I then came up with the idea this morning instead of creating 150 append queries to have the unmatched query only pull 1 branch and then match it up with the append query which is pulling the 2 results for rejects out. (I have only gotten to the rejects queries and pulling samples out for them then i was going to work on the other 2.) And that is where I am last creating a Macro with 150 openquery running the same query 150xs. I would think that I could run this in VB just running a loop of it 150xs, but I don't know how to write that script. I hope this clearfies things a little, I am not the best at explaining myself. After I have all of this created I am still going to have to create a delete query for the samples that they end up throwing out because they could use those results or the branch closed since we lag a month behind with our data. Also, be able to create it where they could pull a new sample from another branch. But I don't think that aspect will be that difficult, just getting 150 branches and pulling 2 samples out of my reject, paidout, and originations based on those 150 random branches and not the same 150 branches already done this year was the big headache.

here is the code I am running with the unmatched query to pull out the random 1 branch if this helps a little. i still don't know if i have it written correctly. Which I still feel like there is something incorrect in it and the way it is written for what I am looking for. I did run it 5 times to get an idea and then ran a duplicate query to see if it pulled new branches each time and it appeared to work, but i don't know.

SELECT TOP 1 a.Date_ID, a.Country_ID, a.ST, a.BR, "" AS Rank
FROM Current_Alignment AS a LEFT JOIN Sample_Branches AS b ON (a.ST = b.ST) AND (a.BR = b.BR)
WHERE (((b.ST) Is Null) AND ((b.BR) Is Null) AND ((Year(Now()))=Year([a].[Date_ID])))
GROUP BY a.Date_ID, a.Country_ID, a.ST, a.BR, "", Rnd(a.BR)
HAVING (((a.Date_ID)=DateSerial(Year(Now()),Month(Now())-1,1)) AND ((a.Country_ID)=1))
ORDER BY a.Date_ID DESC , Rnd(a.BR) DESC;

Matt