How it Work function Rnd()?

[MajP]

As previously stated the way it currently works
1. is that it sorts each list of messages randomly.
2. It counts how many messages are available (in this case 250 for work, love, and finance).
3. For each day you need 12 groups of three messages so it divides 250\12 to get 20 days that you can write.
4. It reads each list from top to bottom giving the first 20 messages for to the first zodiac, then resets the date and gives 20 messages to the next zodiac.

So this gets a little confusing and I will have to post a new DB. But to avoid when writing day 21 that you do not reuse a message from day 12 - 20 I first determine if a message was used in the last 9 days. (12 messages per day * 9 days = 108)

qryLast9

SELECT TOP 108 
  tblHoroscope.LoveID_FK, 
  tblHoroscope.FinanceID_FK, 
  tblHoroscope.WorkID_FK, 
  tblHoroscope.HoroscopeDate
FROM 
 tblHoroscope
ORDER BY 
 tblHoroscope.HoroscopeDate DESC;

in the old qryRandomWork, qryRandomLove, qryRandomFinance I simply sorted by my random function. Now I want to sort first by if it was not in the last 9 days then by a random number. Unfortunately I could not get it to sort without an error so I did it in two parts.

new qryRandomWork

SELECT 
 Work.WorkID, 
 Work.Discription_Work, 
 (Select count(*) from qryLast9 where qryLast9.WorkID_FK = Work.WorkID) AS CountInLast9, 
 MajP_Rnd([workid]) AS Sort
FROM 
 [Work]
ORDER BY 
 MajP_Rnd([workid]);

if it was used in the last 9 days the field would return a 1.

Now (since I am not smart enough to do all the above in a single query) I sort it by if it was previously used and by the random number

qryRandomWorkSorted

SELECT 
 qryRandomWork.WorkID, 
 qryRandomWork.CountInLast9, 
 qryRandomWork.Sort
FROM 
 qryRandomWork
ORDER BY 
 qryRandomWork.CountInLast9, 
 qryRandomWork.Sort;

Then do that for love, and finance and use the new queries in the code

     Set rsWork = CurrentDb.OpenRecordset("qryRandomWOrksorted")
     Set rsLove = CurrentDb.OpenRecordset("qryRandomLovesorted")
     Set rsFinance = CurrentDb.OpenRecordset("qryRandomFinancesorted")

the above will ensure you can do 20 days at a time or (Number of messages \ 12) without any repetition, and there will not be repetition within 9 days from the last group of 20 messages.

Although I added these two extra queries there is probably a way to do it in a subquery.

 

[KrIs86BsBG]

As previously stated the way it currently works
1. is that it sorts each list of messages randomly.
2. It counts how many messages are available (in this case 250 for work, love, and finance).
3. For each day you need 12 groups of three messages so it divides 250\12 to get 20 days that you can write.
4. It reads each list from top to bottom giving the first 20 messages for to the first zodiac, then resets the date and gives 20 messages to the next zodiac.

So this gets a little confusing and I will have to post a new DB. But to avoid when writing day 21 that you do not reuse a message from day 12 - 20 I first determine if a message was used in the last 9 days. (12 messages per day * 9 days = 108)

qryLast9

SELECT TOP 108 
  tblHoroscope.LoveID_FK, 
  tblHoroscope.FinanceID_FK, 
  tblHoroscope.WorkID_FK, 
  tblHoroscope.HoroscopeDate
FROM 
 tblHoroscope
ORDER BY 
 tblHoroscope.HoroscopeDate DESC;

in the old qryRandomWork, qryRandomLove, qryRandomFinance I simply sorted by my random function. Now I want to sort first by if it was not in the last 9 days then by a random number. Unfortunately I could not get it to sort without an error so I did it in two parts.

new qryRandomWork

SELECT 
 Work.WorkID, 
 Work.Discription_Work, 
 (Select count(*) from qryLast9 where qryLast9.WorkID_FK = Work.WorkID) AS CountInLast9, 
 MajP_Rnd([workid]) AS Sort
FROM 
 [Work]
ORDER BY 
 MajP_Rnd([workid]);

if it was used in the last 9 days the field would return a 1.

Now (since I am not smart enough to do all the above in a single query) I sort it by if it was previously used and by the random number

qryRandomWorkSorted

SELECT 
 qryRandomWork.WorkID, 
 qryRandomWork.CountInLast9, 
 qryRandomWork.Sort
FROM 
 qryRandomWork
ORDER BY 
 qryRandomWork.CountInLast9, 
 qryRandomWork.Sort;

Then do that for love, and finance and use the new queries in the code

     Set rsWork = CurrentDb.OpenRecordset("qryRandomWOrksorted")
     Set rsLove = CurrentDb.OpenRecordset("qryRandomLovesorted")
     Set rsFinance = CurrentDb.OpenRecordset("qryRandomFinancesorted")

the above will ensure you can do 20 days at a time or (Number of messages \ 12) without any repetition, and there will not be repetition within 9 days from the last group of 20 messages.

Although I added these two extra queries there is probably a way to do it in a subquery.

1. I attach the last database file to view it and I will try to add all the new codes myself to test if they work.

2. Прикачвам последния файл на базата данни за да я прегледаш и ще се опитам сам да добавя всички нови кодове за да тествам дали работят.

 

[MajP]

Try This version.
One thing. You can only add days to the end of the existing dates. If you add to the front by picking an earlier date that has no records then you can get duplicates.

 

[KrIs86BsBG]

Try This version.
One thing. You can only add days to the end of the existing dates. If you add to the front by picking an earlier date that has no records then you can get duplicates.

1. Thanks a lot for the changes made. In the new base type I have 205 text for each category and not 250. When deleting the existing entries from the qryDelHoroscope table when you open the form and pressing the last date button, it automatically takes 17 days with horoscopes. We have 8.05.2019 to 10.06.2019. 34 days are obtained. Every 9 days there should be no repetition of categories and signs. My question is this: Will I break them without repeating for 9 days for each new 17 days of the calendar or should I put more text on the categories?

2. Благодаря много за направените промени. В новия вид на базата имам по 205 текста за всяка категория, а не по 250. Когато се изтрият съществуващите записи от таблица qryDelHoroscope при отваряне на формуляра и натискане на бутона за последната дата, автоматично прави 17 дни с хороскопи. В момента има от 8.05.2019 до 10.06.2019. Получават се 34 дни. На всеки 9 дни би трябвало да няма повтаряне по категориите и зодиите. Въпроса ми е следния: За всеки нови 17 дни от календара ще ги разделя ли без повтаряне за 9 дни или трябва да поставя още текстове по категориите?

 

[MajP]

1. Thanks a lot for the changes made. In the new base type I have 205 text for each category and not 250. When deleting the existing entries from the qryDelHoroscope table when you open the form and pressing the last date button, it automatically takes 17 days with horoscopes. We have 8.05.2019 to 10.06.2019. 34 days are obtained. Every 9 days there should be no repetition of categories and signs. My question is this: Will I break them without repeating for 9 days for each new 17 days of the calendar or should I put more text on the categories

As long as you move beyond the last date it will make 17 days without repetition. It will also ensure that there is not repetition between the first 17 days and the next 17 days for any nine day period.