Record Performance within my Database

[Dave Smith]

I’m looking for some help on reporting performance within my database. Below are a number of things I would like to record but if there are other things I can use to determine performance that would be helpful also.

I running a query to update my table behind the form

Start time (Time a form is open)
End time (Time a form is closed)
Calculate the time between start and stop times
Modified
Updated
User Name (Their log in name)
Workstation Name

Thanks for your time and help
Corey

[patjones]

Hi Dave,

Most of these things will take the form of an UPDATE query that runs when the event in question occurs.

Calculation of the difference between start and stop times is not something that you should store in the table, because it's redundant information. When you need that information (in a report, for instance) you can simply calculate it at that time.

Also, what do you mean by 'modified' and 'updated'? Do you already have some means of obtaining their user name and workstation name?

I think we just need a little more information here to provide you with a helpful answer. Thanks.

Pat

[Dave Smith]

Thank you Pat, Just the advice that I will be doing update queries for most of the stuff mentioned helps a lot. I guess the Modified and update are the same think so I will only be using one of them. I’m using a formula to get the workstation. Is there a better way

[patjones]

In the work that I do I have not had a need to get the workstation name, so without that experience I can't suggest something better than what you're doing. A better way may well exist though.

Let us know if you need assistance with the nitty gritty of the UPDATEs.

Pat

[NeoPa]

You may have to run some action queries, though I can't think too many (if any) of them would be UPDATE queries myself. It all rather depends on the details that aren't very forthcoming in the question. Before offering an answer I'd want to know more about what you're trying to achieve. What is linked to what? Is every event logged separately? What would tie a start event to a stop event logically (What is being started and stopped)? Far too many questions for a meaningful answer to my mind.

I would suggest a lot more careful consideration is required before posting a clear question. You will very likely find that the mere fact of putting that thought into your problem gives you most of the answers you need, but we're happy to clarify areas of continuing doubt once you've reached that point.

[patjones]

I suppose I should be more specific and say INSERT or UPDATE depending on what the circumstances are, but NeoPa is correct that it really depends on the specific issue at hand.

For instance, if you're talking about something like the opening and closing of a form, you can INSERT a record into the table that records this information, with the time that the form was opened. When the form is closed, an UPDATE to that record can indicate the time at which it was closed.

On the other hand, if you're looking to track something like when a particular record was modified, and who modified it, I would personally say that this is information that is best stored in the table where the record is located...as part of the record. I have a few projects where 'last modified' and 'last modified by' columns are part of the table design.

Again, this is all conjecture. If you have issues with a very specific task and have the ability to specify all the parameters associated with that task, we can probably give more focused answers.

Pat

[Dave Smith]

Thank you both very much. I'll be back if I have any more problems

Thanks again

[NeoPa]

No worries Corey.

Dave Smith:
I'm using a formula to get the workstation. Is there a better way?

This info is stored in an Environment Variable. I don't want to take the thread off-track here, but if you're interested in more details post up a question about it and I'll fill them in for you.

[Dave Smith]

Thank NeoPa. Greatly appreciated