I need help with counting.

October 18, 2006 12:12 PM

I got the job. Now I need a little help with it. Someone I briefly worked under while at this job has posed a Microsoft Access question to me. I understand a good amount of Access but this was immediately over my head. I told her that I would see what I could find out for her.

FWIW, we are using Access 2003.

She has a database in which we enter data for projects requested by others. This data includes requestor name, end user name if not the same as the requestor, date requested, description of project, ETA for completion of project, etc.

For this question the ETA field is our concern. It has been requested that a counter be implemented for how many times that ETA field has been edited. For example: My original ETA is 10/20/2006 and today I realize that I can't make that ETA but I think I can make it by 10/25/2006. Next to my ETA field would be a field that would automatically display the number 1, noting how many times I have adjusted my ETA. Furthermore let's assume that I end up twice more editing that field to new dates thus the "counter field" would automatically display the number 3.

Can this be done? If so how? Can you point to examples on the web? I'd like to learn how this can be accomplished. Is there anything else I should know?

posted by horseblind to computers & internet (10 answers total)

previously - seems you could use VBA or a trigger (if you're using Access as a frontend to SQL Server) to do it. (I dunno about specific examples, though.)
posted by mrg at 12:33 PM on October 18, 2006

I think Experts Exchange is a more appropriate and probably more helpful forum for this type of question. It's been a great resource for me in the past when I've been stuck on IT-type problems.
posted by PercussivePaul at 12:36 PM on October 18, 2006

I would suggest building logic into your form that will capture user information as well as date and time when the data in the form is modified. This page has an example of that: http://office.microsoft.com/en-us/assistance/HA010345351033.aspx

The problem with that scenario is that it will record any time the entire record in modified... not just one field. My suggestion would be to look at that code and see if you can tweak it to record specific activity within the record.
posted by ro50 at 12:43 PM on October 18, 2006

Recipe 10.3 in the Access Cookbook 2.0 (O'Reilly): Creating a transaction log.

Only works from a form (forms generate triggers). I believe you might even be able to get to it online at safari.oreilly.com
posted by jdfan at 1:13 PM on October 18, 2006

I'm fairly sure that there's no failsafe way to do what you're looking to do, short of migrating the database to SQL server and using triggers, as mrg pointed out.

The closest you're going to get would be something like what ro50 and jdfan pointed out, by placing VBA behind your form -- specifically in the "AfterChange" property of your ETA textbox. E.g.,:

Private Sub ETA_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE Table1 SET ETACount=ETACount+1 WHERE id=" & Me.ID)
DoCmd.SetWarnings True
End Sub

(where Table1 is your table name, ETA is the date field, ETACount is the counter field, and ID is the unique identifier for the record)

If this is something that only you're going to use, then that would work fine. If it's something you're going to distribute, however, you should be aware that falsifying this information is trivial by simply editing the ETA or ETACount directly within the table (which Access can't trap).
posted by Doofus Magoo at 1:23 PM on October 18, 2006

There are numerous ways you can do this, depending on your level of expertise and how much you can change the database.

You could pull the ETA out in to a separate table. This table would have projectID's and ETA's and date modified. Then, you use the join of the original table and this new table to get the data you need. A simple count(*) and group by on the ETA table would give you the number of changes.

Of course, you would need to change everything that modified or viewed the ETA.
posted by gus at 1:51 PM on October 18, 2006

What Doofus said. Triggers aren't applicable to Access, so don't worry about them (unless, as others have said, you're using Access as a front-end to SQL Server).

If you really think people messing with the data is likely and problematic possibility, you'll probably need to go down the dreaded path of Access security (shudder). If you do go the security route, make sure you backup your database first. Several backups aren't a bad idea.

Another thought for getting around the security issue might be set up a network folder (I assume your db is on the network) that only your userid can write to. Some VBA code could then write the # of times changed info to a text file.
posted by treepour at 3:14 PM on October 18, 2006

Here is an excellent Access forum with very knowledgable people willing to help.
posted by Roger Dodger at 6:30 PM on October 18, 2006

ETA count is a bad metric; knowing ETA's been changed doesn't tell you whether it's been increased or decreased, or by what amount, or how often it is changing.

Consider instead a one-to-many relationship between the table ETA is currently in, and a table of ETA estimates. The table of ETA estimates would have a foreign key to the id of the row in the current table, an estimate, and a timestamp indicating when the estimate was made. It would be read-only; a new estimate would result in inserting a new row into that table. The current ETA would be the row with the max( timestamp) for the parent row id.

That way, you could track not only number of changes, but what the changes are. You'd write the form so that any change to an estimate was simply an insert into the child table.

Doing it the way you're doing it now throws away (overwrites) valuable data. That's generally a sign that either a) the data isn't really valuable, so saving it at all is pointless, or b) that your design is flawed.
posted by orthogonality at 10:22 PM on October 18, 2006

Okay. Best answer has been chosen for previous question. I guess the comment about previous question was deleted due to relevance.

orthogonality: I talked to the requestor and she told me about a field that I was not aware of. There is a field (Orig Need Date) for the requestor of the project to put in the date they want it by and there is a field for the developers to put in the Plan Date: the date by which they believe the project can be completed by (previously referred to as ETA). They are interested in the history if it can be provided, however think it is not a requirement as they have the static Orig Need Date and the dynamic Plan Date fields to make comparisons with.

As far as migration to SQL goes, there is no objection to this if needed. If an Access solution can be provided then no migration to SQL will be implemented.
posted by horseblind at 7:52 AM on October 19, 2006