Oct 13 2010, 01:32 PM
I'm quite new to access. (Like, I started today.) So, I apologize in advance if this is a dumb question. I couldn't find an answer in another thread.
I'm making a daily checklist to keep track of tasks I do regularly, and I want Access to keep a running count of how many days in a row I've done a task.
So far, my approach has been to make two fields - Action and Action Chain. Action is Yes/No and Action Chain is a number. I'm trying to make Action Chain's default value equal to that running total, but I don't know how to make an expression for that. Just to clarify - if, for example, Action is Yes on day 1, then No on day 2, and Yes on days 3 through five, I want Action Chain to be (respectively) 1, 0, 1, 2, 3.
Also, as a follow-up question, I will want to do this to multiple fields. Does Access have some sort of functionality so that I can make a "Chain" subroutine, so that I could have running total columns for multiple fields without having to key them in?
Thanks a ton for the help.
Oct 13 2010, 02:28 PM
Generally (and it is a good rule) you do not want to store calculated (ie. running) totals, just create code to calculate them on the fly. It depends on where you want to view this information on how you will end up setting it up. For instance, if you want a print out to show someone, you would create a report with some running totals. If you want a form so you can open and look at it at a glance, you could cycle through the records and post the results to a listbox or text box.
Setting up tables is a good place to start, but things will roll a lot easier if you get a general idea in your head (or sketched out) how you would like to view and access the information.
Oct 13 2010, 05:53 PM
Well, currently, I'm planning on showing that information on the checklist form beside the checkbox for the action, so that while I'm filling out the form I'll get some satisfaction from building the number. I want to start running in the morning, so when I go to check it off the list for the 3rd day in a row, that 3 will feel good and I won't want to skip a day and reset it. Or, if it's something that needs to be done periodically every week or so, I'll get a reminder of how long it's been, maybe in red if it's over some certain target number, i.e. don't go without cleaning the cat box for more than 3 days!
That is mainly what I want to do right now but I may also want to do some analysis on these totals in the future (high scores?) so I was just looking for generally how one codes the "days since the last No" concept.
Oct 14 2010, 11:12 AM
Being new to access, I am pretty sure you didn't quite understand what you were asking when you posted the question. So, I worked out a sample db for you to check out. It is fully annotated and as I don't know what level of general programming you have, concepts have run from basic (loops) to medium difficulty (using an sql SELECT statement to populate a recordset). If you have questions about any of the specifics, please ask. If you have general questions, this forum is full of useful information and a quick search will probably get you some very good answers.
sample database is 2003.