Results 1 to 4 of 4

Thread: Daily Record

  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Daily Record

    Once again I am stuck with a concept that I am not sure is possible. I have done some searching and can't seem to find any topic or sample that does what I am looking to do.

    Every day we track the number of times an event happens (i.e. We take a call from a certain department, get an email or alert, etc). We then use those numbers to calculate time spent per day, week, month and trends. What I want to do is create a table that has one record for each day and when the form is opened it will default to todays record. The biggest obsticle I am facing is making sure there is a record for each date without manually adding dates/records ahead of time. Would it be possible that a form could open to that days record, but if the record did not exist then create it?

    My thoughts were to set the date field as the primary key so that I don't end up with multiple records for a date. Please correct me if this isn't best practice.

    Thanks for any help in advance.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If you have one record per day, and make the date the primary key, then you will only be able to record one event per day. If you try to enter a second record on a given day, you will encounter a primary key violation.

    I don't see that you need to create records ahead of time. Instead, use the date and time as the primary key. Your data entry form can then have calculated fields that show the date and time that the form was opened. Your users then simply have to select values to indicate what the event is and what or who triggered it, and the record would consist of the date and time it happened, the type of the event and what it was that was occupying their time.

    If this is going into a multi-user environment, I would suggest making the current user ID a third part of the primary key, just in case two people created records at the same date and time on different machines.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Wow, that was so simple I am slightly embarrased that I asked. We're not bothering with time or tracking by user, so that won't be an issue. I am not sure what was giving me all the problems last week but thank you for setting me straight!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You're welcome! Sometimes we get so engrossed in a problem that it takes a fresh pair of eyes to see what we can't. I spent most of last week tracking down a problem (over-reporting of unauthorised absence) caused by one query lacking part of a join between two base tables...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •