Random

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
tina said:
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 said:
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!
Click to expand...
Click to expand...

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
 
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 said:
tina said:
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 said:
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!
Click to expand...
Click to expand...

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
Click to expand...
 
Back
Top