AboutBob Heifler Expertise ALL MICROSOFT ACCESS DESIGN AND PROGRAMMING
QUESTIONS
Free Microsoft Access Tutorial Videos are available to you at
my web site. Fifteen years of Microsoft Access experience allows me to
answer most of your questions. Common question example files available
here.
Experience Microsoft Access
programming since 1994. Support for all versions.
Publications My Microsoft Access
articles are published at:DailyAccessTips.com
Education/Credentials UCLA GRAD, Member of Association of Database Developers
Awards and Honors AccessToExcel.com
has won several
shareware awards for assisting analysts in moving data from MS Access
to Excel.
I need to create a trending report that gives a bar graph of all project's status at the end of each of the last 4 months. For example:
March: Not Started (1); Active (2); Completed (3)
April: Not Started (2); Active (3); Completed (1)
May: Not Started(4); Active (2); Completed(3)
June: Not Started (3); Active (1); Completed (4)
I know how to create the report, but I'm not sure how I can capture these same projects at 4 different points in time. (These are the same projects and over the months they'll be updated from one status to the next). I could do an append query, which a user would click at the end of each month, but that would be an additional responsibility for the user that I'd like to avoid.
Ideally, I'd like for the database (whether opened or closed) to automatically append the projects (along with their status') at the end of each month. This way, there is no responsibility placed on the user and the information is always accurate.
How would you recommend that I try to accomplish this?
Thanks in advance for your help!
Adam
Answer Adam,
Create a table to store the last date the append query ran.
Use a macro you create and name 'autoexec' that will trigger code to run the query only if the current date is the last date of month and the table shows it hasn't been run for current month. Then have it update the table with current date. If the program was not started on end of month, it would have to contain more logic of course.
Bob Heifler
ALL MICROSOFT ACCESS DESIGN AND PROGRAMMING
QUESTIONS