I am trying to figure out the logic involved in what I want. If I can understand the logic, I can figure out how to write code to do it.
Let’s say, I have a database of 10 items. Each item has a date field.
ID1 - Feb 21 - Canada
ID2 - Feb 20 - USA
ID3 - Feb 19 - Japan
ID4 - Feb 18 - Korea
ID5 - Feb 17 - China
ID6 - Feb 16 - Australia
ID7 - Feb 15 - Mexico
ID8 - Feb 14 - Taiwan
ID9 - Feb 13 - Russia
ID10 - Feb 12 - Ukraine
Every day I have 5 items showing. This list changes every day with 1 new item and 1 old item dropping off the list. This is done very simply by showing the item which matches today’s date and then list 5 descending so I get the previous 4 days shown below it. So if we assume today is Feb 20th, this is what would be displayed.
ID1 - Feb 21 - Canada – Not displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Displayed
ID6 - Feb 16 - Australia – Displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed
ID10 - Feb 12 - Ukraine – Not displayed
The next day Feb 21st would be…
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed
ID10 - Feb 12 - Ukraine – Not displayed
Ok, the above is simple enough and I’ve managed to setup a query which does this fine–took me 3 years to figure out how to do it though and I’m hoping it won’t take another 3 to get the next problem solved. The problem is when we reach Feb 22 and there is no more data. What I’ve been doing is just using a spreadsheet to copy the data and increase the dates so that it’s a really long list that lasts a year. What I want is for it to go to the other end of the list and grab the next item instead of me having to use duplicate data in the database. For example…
ID10 - Feb 22 has no record so Ukraine is displayed here.
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Not displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed
The next day Feb 23…
ID9 - Feb 23 has no record so Russia is displayed here
ID10 - Feb 22 has no record so Ukraine is displayed here.
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Not displayed
ID5 - Feb 17 - China – Not displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
My goal is to be able to have the list change daily and just repeat so that it never ends. I also need to be able to add new records… ID 11, ID 12, ID 13 at any point in the future with no problem of messing up the coding.
This makes a little more sense now that I’ve explained it here, but I’m still a bit confused about the logic behind this. What I’m thinking is that I can assign a date to just one record which would be the starting date. The database then adds a day until it gets 5 days from today and then it starts displaying. If the ID is NULL (end of list) then ID = ID1. Something like that.
Ideas? Anyone? I think just discussing it with the people here will help me to better understand this and figure it out. So if you have questions or comments or better yet suggestions, please let me know.