I am trying to create a query in MS Access that will generate a list of dates starting from today and ending seven days back. I have been able to get them in columns, but I want to get them in rows. Please note that I need the query to dynamically generate the data depending on today's date and I want to avoid creating tables. Is this possible using just a query? Please help.
It's really easy if you have a special utility table which contains the numbers 0 through 7. I call this the integers table, and it has one column, called i.
select dateadd("d",-i,date()) as yourdate from integers where i between 0 and 7
For each value of i, that number of days is subtracted from today's date. The result is today's date and the previous seven dates.
You did indicate in your question that you wanted to avoid creating tables, but I urge you to create an integers table. Perhaps you wanted to avoid a table of hardcoded dates, which would, admittedly, be unwieldy. But an integers table comes in so handy in so many different scenarios, it really is worth it. See The integers table (28 January 2004) for an example which generates dates for an entire year.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.