Random Bill 20.09.2006 18:51:01 Trying to create a macro that will open a table and then go to a RANDOM record. After selecting the random record, append it's selection to a different table and delete it from the original. Can anyone tell me what the Access Offset expression is to go to a random record and how to set up the macro to do what I need it to do? Thanks so much!

Re: Random "tina" <nospam[ at ]address.com> 21.09.2006 02:22:24 well, i've no idea what you mean by Access Offset expression. but here's one solution you might try: paste the following function into a public module, as Public Function isRandomNum(ByVal x As String) As Single ' the x variable is included to force a call to this function on every ' record in the query. it is NOT used within the function itself. Randomize isRandomNum = Rnd End Function create a SELECT query based on your table, as SELECT TOP 1 PKFieldName FROM TableName ORDER BY isRandomNum([PKFieldName]); replace TableName and PKFieldName with the correct names of your table and it's primary key field. this SELECT query returns a random record from your table. you can change the query to an Append query, and use it to append the random record into another table. if you include the primary key field(s) in the append, you can identify the record that exists in both tables, and delete it from the first table. hth "Bill" <Bill[ at ]discussions.microsoft.com> wrote in message news:694CD5E4-1543-42A8-A3A5-3C95470FB722[ at ]microsoft.com... [Quoted Text] > Trying to create a macro that will open a table and then go to a RANDOM > record. After selecting the random record, append it's selection to a > different table and delete it from the original. > > Can anyone tell me what the Access Offset expression is to go to a random > record and how to set up the macro to do what I need it to do? > > Thanks so much!

Re: Random Bill 21.09.2006 11:56:01 "tina" wrote: [Quoted Text] > well, i've no idea what you mean by Access Offset expression. but here's one > solution you might try: > > paste the following function into a public module, as > > Public Function isRandomNum(ByVal x As String) As Single > > ' the x variable is included to force a call to this function on every > ' record in the query. it is NOT used within the function itself. > > Randomize > isRandomNum = Rnd > > End Function > > create a SELECT query based on your table, as > > SELECT TOP 1 PKFieldName > FROM TableName > ORDER BY isRandomNum([PKFieldName]); > > replace TableName and PKFieldName with the correct names of your table and > it's primary key field. this SELECT query returns a random record from your > table. you can change the query to an Append query, and use it to append the > random record into another table. if you include the primary key field(s) in > the append, you can identify the record that exists in both tables, and > delete it from the first table. > > hth > > > "Bill" <Bill[ at ]discussions.microsoft.com> wrote in message > news:694CD5E4-1543-42A8-A3A5-3C95470FB722[ at ]microsoft.com... > > Trying to create a macro that will open a table and then go to a RANDOM > > record. After selecting the random record, append it's selection to a > > different table and delete it from the original. > > > > Can anyone tell me what the Access Offset expression is to go to a random > > record and how to set up the macro to do what I need it to do? > > > > Thanks so much! > > > Tina, That was very helpful. Now I have it working to randomly select a record. But there is more I want to do. Perhaps you can help. Here is the scenario. There will be 3 tables total. I want to randomly pick a record from table #1, compare that record to all of the records in table #2. If the record that was chosen from table #1 has already been picked once before (appears in table #2) then Access would go back to table #1 and pick another until it finds one that has not been picked before (does not appear in table #2). Each time it is successful in picking an unused record it will write that successful record to table #3 and append it to table #2 so that it can not be chosen again. Can you help with this? I am assuming that I could have several commands and put it all together using a macro? Thanks so much for your help. -Bill

Re: Random "tina" <nospam[ at ]address.com> 22.09.2006 02:16:57 well, it seems like it would be a whole lot easier to use one table. just add a Yes/No field to Table1, where a Yes value (boolean True or -1) means that the record has been previously picked. i'll call the field "Picked". just change the SELECT query slightly to return only records where the value of Picked is No, as SELECT TOP 1 PKFieldName FROM TableName WHERE Picked = False ORDER BY isRandomNum([PKFieldName]); you can turn the above query into an Append query, to append the record to Table3 (though it's unusual to put duplicate records in two tables in a database - are you sure it's necessary in your case?) then you can link use the query Wizard to write a query that matches the records in Table1 and Table3, WHERE Picked = False. turn that query into an Update query, to change the value of Picked to True in the Table1 record. hth "Bill" <Bill[ at ]discussions.microsoft.com> wrote in message news:E2D786E9-9C10-4C40-8210-7421151F0326[ at ]microsoft.com... [Quoted Text] > > > "tina" wrote: > > > well, i've no idea what you mean by Access Offset expression. but here's one > > solution you might try: > > > > paste the following function into a public module, as > > > > Public Function isRandomNum(ByVal x As String) As Single > > > > ' the x variable is included to force a call to this function on every > > ' record in the query. it is NOT used within the function itself. > > > > Randomize > > isRandomNum = Rnd > > > > End Function > > > > create a SELECT query based on your table, as > > > > SELECT TOP 1 PKFieldName > > FROM TableName > > ORDER BY isRandomNum([PKFieldName]); > > > > replace TableName and PKFieldName with the correct names of your table and > > it's primary key field. this SELECT query returns a random record from your > > table. you can change the query to an Append query, and use it to append the > > random record into another table. if you include the primary key field(s) in > > the append, you can identify the record that exists in both tables, and > > delete it from the first table. > > > > hth > > > > > > "Bill" <Bill[ at ]discussions.microsoft.com> wrote in message > > news:694CD5E4-1543-42A8-A3A5-3C95470FB722[ at ]microsoft.com... > > > Trying to create a macro that will open a table and then go to a RANDOM > > > record. After selecting the random record, append it's selection to a > > > different table and delete it from the original. > > > > > > Can anyone tell me what the Access Offset expression is to go to a random > > > record and how to set up the macro to do what I need it to do? > > > > > > Thanks so much! > > > > > > > > Tina, > > That was very helpful. Now I have it working to randomly select a record. > But there is more I want to do. Perhaps you can help. > > Here is the scenario. > > There will be 3 tables total. > > I want to randomly pick a record from table #1, compare that record to all > of the records in table #2. If the record that was chosen from table #1 has > already been picked once before (appears in table #2) then Access would go > back to table #1 and pick another until it finds one that has not been picked > before (does not appear in table #2). Each time it is successful in picking > an unused record it will write that successful record to table #3 and append > it to table #2 so that it can not be chosen again. > > Can you help with this? I am assuming that I could have several commands > and put it all together using a macro? > > Thanks so much for your help. > > -Bill