Hello! I've created relational tables and the purpose is to extract the text part of three tables, which is 30 texts for each, a total of 90 texts. What does the feature have to swap 1 of 30 text for each of them each time a query is run?
I do not ask for any time ... My question is how to display the different text part of each of the three tables shuffled ... Each table has 30 text and should be randomly mixed when executing the request.
I see. Sorry, I guess in a little thick because I am still not sure what it is you’re asking. There may be some one who comes along who does. Sorry I couldn’t help you...
No, it's not that. ..with this meter it is shown just how they are moving places ... I have to exchange one of 30 text in any order after each execution of the request ...
I *think* what the o/p is asking is to be able to generate a random number between 1 and 30 and then use that to extract some text from a table using that number as the ID.?
However that link allows you to select a random record.
O/P try this for each table
Code:
SELECT TOP 1 Transactions.ID, Rnd([id]) AS Expr1
FROM Transactions
ORDER BY Rnd([id]) DESC;
To use, because I do not speak well English ... I'll let it out more easily ... I hope you understand me. I have a database of texts that are divided into three categories: Love, Jobs, Finance. For each of these categories, I have over 30 texts. The question is to create a form that, when executing a query, mixes me for each category individually so as not to repeat it.
Since you have suggested that you have a language issue, I might be a little bit overboard in my explanation here.
In true random sequences, a number can repeat. However, if you want a nearly random ordering, there IS a way to come close.
Add one column to your table, make it of datatype SINGLE (meaning "REAL number in 32 bits" or "floating point" or "number that is NOT an integer"). Call this SINGLE field RandOrder.
What you do is in two parts, run queries. First part establishes the randomness. In a VBA routine,
Code:
Randomize
CurrentDB.Execute "UPDATE my-table-name SET RandOrder = Rnd() ;"
Then you could look at this query or make a recordset using this query.
Code:
SELECT The-text-field FROM my-table-name WHERE Category-name = "Love" ORDER BY RandOrder ;
Of course, for all of this code, you fill in YOUR field and table names as appropriate.
And of course, do the same query with a different WHERE-clause value to get the randmized list for Jobs and Finance. You only have to re-run the Randomize action and the UPDATE query to change the ordering for the SELECT query. Now, as I said before, it IS possible for a truly random sequence (or even a NEARLY random sequence) to have a repetition in it. However, by retaining the raw SINGLE value, you have lower ODDS on duplicate ordering numbers; something like 1 in 10 million chance of duplication, which is GREAT odds for only 30 choices.
This possibility of duplicated ordering numbers in SINGLE precision isn't really as bad as you might think, though, because the SELECT query will still only present the numbers to a recordset one at a time and you vary rarely know what that order will be anyway.
You have to remember that ALL MS Office programs use a library of subroutines that includes a random number generator shared by all. So if you were going to do this in Excel or in some other Office utility's VBA, you would STILL have the same odds, and they are pretty good.
Note also that using the method I described to you, the RandOrder field would be filled by fractional numbers between zero and one (0.00 and 1.00). This is OK because Access can sort that as well as it can sort anything else.
Since you have suggested that you have a language issue, I might be a little bit overboard in my explanation here.
In true random sequences, a number can repeat. However, if you want a nearly random ordering, there IS a way to come close.
Add one column to your table, make it of datatype SINGLE (meaning "REAL number in 32 bits" or "floating point" or "number that is NOT an integer"). Call this SINGLE field RandOrder.
What you do is in two parts, run queries. First part establishes the randomness. In a VBA routine,
Code:
Randomize
CurrentDB.Execute "UPDATE my-table-name SET RandOrder = Rnd() ;"
Then you could look at this query or make a recordset using this query.
Code:
SELECT The-text-field FROM my-table-name WHERE Category-name = "Love" ORDER BY RandOrder ;
Of course, for all of this code, you fill in YOUR field and table names as appropriate.
And of course, do the same query with a different WHERE-clause value to get the randmized list for Jobs and Finance. You only have to re-run the Randomize action and the UPDATE query to change the ordering for the SELECT query. Now, as I said before, it IS possible for a truly random sequence (or even a NEARLY random sequence) to have a repetition in it. However, by retaining the raw SINGLE value, you have lower ODDS on duplicate ordering numbers; something like 1 in 10 million chance of duplication, which is GREAT odds for only 30 choices.
This possibility of duplicated ordering numbers in SINGLE precision isn't really as bad as you might think, though, because the SELECT query will still only present the numbers to a recordset one at a time and you vary rarely know what that order will be anyway.
You have to remember that ALL MS Office programs use a library of subroutines that includes a random number generator shared by all. So if you were going to do this in Excel or in some other Office utility's VBA, you would STILL have the same odds, and they are pretty good.
Note also that using the method I described to you, the RandOrder field would be filled by fractional numbers between zero and one (0.00 and 1.00). This is OK because Access can sort that as well as it can sort anything else.
Hello again. Now I am learning and not understanding this function. My job is to do a Horoscope Database. In it I have made four tables: Zodiac, Love, Work and Finance. I have 30 descriptions for each of the three Love, Work, and Finance tables. In order for it to work properly, it is necessary for a query to display different information on the three parameters each day for each zodiac, and after 9 days the descriptions of the three parameters pass to another sign. What do I have to do to mix each of my texts and get me a different text for each zodiac? If you can help me I would send you the Database, just give me your email. Thanks!
Always remember to call Randomize before invoking Rnd for the first time. Otherwise you might be surprised by the results of Rnd not being as random as you'd expected.
KrIs86BsBG, I do not offer that level of help as you requested. I will not post my e-mail to you. This is not because of anything other than that I strictly limit my level of contact. It is not about you, but rather is that I do not provide direct database modification support.
Read over what I said. The basic solution is that you add a field to the table that will be the "driver" of this randomization.
However, your second post appears to have added a couple of wrinkles. First, you might wish for these selections to be different for each person. Second, you might want to have the choices persist for a while to avoid the chance of duplication over a period of several of these transitions.
NONE of us can help you without a good understanding of your goals. Therefore, when you ask questions, please be sure that you explain all of what you want. We are all here because we want to help but it works best if you can clearly explain your goal. Given that you have a language issue, we can be patient - but that need for understanding is extremely important for us to be ABLE to help you.
KrIs86BsBG, I do not offer that level of help as you requested. I will not post my e-mail to you. This is not because of anything other than that I strictly limit my level of contact. It is not about you, but rather is that I do not provide direct database modification support.
Read over what I said. The basic solution is that you add a field to the table that will be the "driver" of this randomization.
However, your second post appears to have added a couple of wrinkles. First, you might wish for these selections to be different for each person. Second, you might want to have the choices persist for a while to avoid the chance of duplication over a period of several of these transitions.
NONE of us can help you without a good understanding of your goals. Therefore, when you ask questions, please be sure that you explain all of what you want. We are all here because we want to help but it works best if you can clearly explain your goal. Given that you have a language issue, we can be patient - but that need for understanding is extremely important for us to be ABLE to help you.
Understood. And are these 4 tables only necessary for me to retrieve the information mixed up by them and how do I need to connect the relationships properly? Is any table necessary to have an ID? And do I put the expression to shuffle on each table or just when a query is being executed ... What exactly should the expression contain to get me a difference of information each time in the three categories ... This is important. Thanks!
1. Can you post your db? Zip it first.
2. My guess is you actually need no relationship because you will want to do a cartesian join to get all possibilities and then sort all possibilities.
3. You need an unique field so I would add an autonumber field to each table
ZodiacID, LoveID, FinanceID, WorkID
4. You can do this in a query.
However, I think there may be some complications if you want no repeats in the fields.
Getting a random choice of Love, Finance, Work is easy. Ensuring no replication of the choices until each choice is used up, is a little harder.
To explain what I mean about repitition. For demonstration, assume you have 5 values in each table instead of 30.
Love
1
2
3
4
5
Work
1
2
3
4
5
Finance
1
2
3
4
5
If you randomly draw for three days you could easily get something like.
Code:
LoveID WorkID FinanceID
2 1 3
2 2 3
3 2 3
Not sure if this is a problem or not. However, on the first two days you get the same Love description and Finance description. On the last two days you get the save work and finance description. Less likely with 30, but still quite possible. If you need to ensure no repetition of love, work, finance until you use up all the values you will need a little more involved solution. Not too complicated, but more involved than a simple query. You will have to write your choices to a table.
Instead of "Post Quick Reply" choose "Go Advanced" and you can upload a file using the paper clip. Zip it first.
You mentioned you had separate tables, and I suggest each table has a Primary Key ID field. I was providing names for those fields. Often people name the field simply "ID" in every table and I find that confusing. Instead of simply "ID" I use something more descriptive like ZodiacID or Zodiac_ID.
Instead of "Post Quick Reply" choose "Go Advanced" and you can upload a file using the paper clip. Zip it first.
You mentioned you had separate tables, and I suggest each table has a Primary Key ID field. I was providing names for those fields. Often people name the field simply "ID" in every table and I find that confusing. Instead of simply "ID" I use something more descriptive like ZodiacID or Zodiac_ID.
If I understand your question. The normal approach is to make a query sorting your records based on a random number. You do not need to alter the table structure. If you need a single random record you can pick the very first record. If you need 9 random records you can select the first nine. However, as stated just because it is a random draw does not ensure it has not already been used. You therefore may need to store what has already been drawn and not pick it again until all choices for that value are assigned. If that is the case you may need a seperate table or field to store the choices. You will have to describe these rules.
