Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: Microsoft Access Timestamp a List

    Hi

    I need to set up a timestamp on a list in Microsoft Access 2003.
    I have a list a,b,c,d and I need to record the time the item is at a, then b, and so on. Also if a letter is skipped it is recorded at 0. and i final column with the total number of hours.

    Thanks for the help im really stuck on this.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what you want to record then is a series of datetime values, NOT a timestamp (Timestamp has a very specific meaning in the db world usually its the date and time the row was last modified or updated)

    How you implement it is entirely up to you

    If you KNOW that there is never ever going to be a step e or f,g....z then you could consider storing these in the same row

    A more purist and extensible design could suggest a sub table recording events with event types. the event types describe a,b,c,d and whatever. you could expand that by some form of recursion so you know what event follows or precedes. (if its linear then its not a problem, the problem is if you have multi path ways that coudl skip evetnjs
    say a,b,d OR a,c,d

    in any event store the time an event occurred as a datetime value

    in any event duration can be handled using datetime maths (have a look at the datetime functions.

    as to if a letter is skipped its recorded at 0, that sounds like you are referrign to some function in your DB that we don't know about.

    if you go down the events sub table model, then by definition there is only a row present if an event has been recorded.. no event recorded means no value.
    if you opt to store four values in one row then allow default values of NULL. use the NZ function to handle your calculations
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    3
    Thanks healdem

    Im still a little confused, a,b,c etc are actually one box set at "a" (qued) then from a drop down menu the user can set "b" (in progress), "c" (on hold) or "d" (complete)
    I just want to find out how long the project is at each stage.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so buy the sounds of it you are usign a combo box to toggle the state. so you need some code int he combo box's on click event that assigns a value to the relevent event timing.
    how you do that depends on your design (are all 4 columns in a single row, or are they in 4 different rows).
    Im not certain tha ta Combo is neccesarily the best design paradigm for this.. possibly a list box (showing all states with corresponding dates may be better). or even use a sub form. the final design will depend on the way you store your data.

    no matter
    design your schema (how the data will be stored)
    then design your user interface.

    If you go down the combo/list box (and in a closed systems ie where the state can only be one of a limited number of values AND the user cannot add new values then a list box is probably preferable to a combo), then you need to add some code that spots when a different status is chose (put some code behind the list/combo on click or on change events (or even after update)
    you need to resolve how you will handle conflicts (eg if the user selects a status which has already got a datetime set then do you ignore the update request or do you automatically allow it, do you only allow a limited number of people to overwrite existing status and so on.

    you can set the time using anyone of a number of design stylistics
    do you use the computers time (use the now() function)
    DO you get the user to enter a time using say a set of scrolling list boxes detailing the hours / minutes and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2012
    Posts
    3
    Thanks for all the help. I will have to look into it in more details to see can |I get a solution.

    Thanks again.

Tags for this Thread

Posting Permissions

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