I have created a MS Access database and am experiencing two issues:
1) I need to create a query that displays results of specific records that have been created (this criteria is based on a date column) during three separate timeframes:
a) The previous seven days.
b) The previous month.
c) The previous six months.
d) The previous year.
For example, if the query is run on January 27, 2006, it will display records that were created from the previous seven days, month, six months, and year of that date. If it is run on January 28, 2006, the results will be different than the query ran on the 27th because the date the query ran has changed. I hope that I am making sense. :)
I think that I will need to create four separate queries to get the results and I will also need to prompt the user for the date (i.e., 01/27/06 or 01/28/06, etc so that the query will return the correct data). Please advise if four separate queries are needed. Also, what formula or criteria would I use for each query? I have tried several different methods and the results are not correct.
2) I loathe the reporting tool in MS Access. I am not an advanced user and have been learning a lot. The reports are difficult to create and are so ugly! (The reports will be eventually distributed to the CIO and executive management and need to look highly professional).
Is there an easier way to run reports out of Access or is there another reporting tool I could use after extracting the data from the query(s)? I thought about creating macros in Excel for this, however since the data frequently changes, the macro will not work each time. I am trying to automate the function of querying and reporting as much as possible.
Thank you in advance for your assistance.