Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Recording work DB

    I have been tasked with creating a DB to record work completed by team members on a daily basis.

    This is to help see how are days are being spent on different projects.

    I thought about a download, but couldn't think of the correct search criteria to find one - Any ideas?

    I want to record Staff Name, Date, Project Name, Project Sub Area & Time Spent

    Would you create this as 1 table or many?

    On any day 1 member of staff could be juggling several projects, so need to be able to enter data on a form for each of them.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    For starters I would think something like this:-

    Staff - StaffID, Name, other things you want to record against the member of staff, department or team maybe both.

    Project ProjID, Name, Description etc

    Time StaffID, ProjID, date, starttime, endtime, typeofwork,

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup. Just for some more clarity, the primary keys would be:

    Staff - StaffID
    Project - ProjectID
    Time - StaffID, ProjectID

    Relate the three tables together by the common names. Don't forget to enforce referential integrity!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2006
    Posts
    54
    In using 3 tables, how would be the best way to create an input userform?

    As mentioned previously, any staff member could be doing several projects duing the same day ie:

    STAFF: ME
    DATE: TODAY

    PROJECT1 09:00 to 10:30
    PROJECT2 11:00 to 12:00
    PROJECT1 13:00 to 14:00
    PROJECT3 14:00 to 16:30

    Ideally I'd only want to enter the staff name and date once and at a later date be able to cycle through old information.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Same way you'd create any form? You have to specify where you need help! Obviously we cannot detail every step required to create an application's forms.... that's your job!

    Try and see how you go, report back if you get stuck.

    On the most basic level, you create mainforms for Staff and Projects and a subform for each.

    However, I'd move the date into the subform data and just default it to today's date.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by StarTrekker
    On the most basic level, you create mainforms for Staff and Projects and a subform for each.

    However, I'd move the date into the subform data and just default it to today's date.
    So a staff Mainform and a project Mainform - Why do you need a subform on each?

    I'm assuming the subform is the time data?

  7. #7
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I would create a main form for each of the Staff and Projects data that can add,
    and edit data to the related tables. After you have done this you will need to
    pull data from both these tables when you create a a time entry. Creating the
    first two forms may give you ideas and (i would bet) questions on how you are going
    to get that data into your timeentry table.

    Good luck

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by michaelro
    So a staff Mainform and a project Mainform - Why do you need a subform on each?
    You don't NEED them both, but having both will make it easy to see the time data from either angle... to see which projects and date/times a staff member worked on and to be able to see which staff and date/times worked on a project.

    Quote Originally Posted by michaelro
    I'm assuming the subform is the time data?
    Yes.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2006
    Posts
    54
    Appreciate all of your help

    Both of you suggest two main forms but I would prefer to use reports to show the data cut different ways.

    In that sense I'd only want one form to add and edit all of the data to the tables - This is why I was asking as I was unsure how to construct it.

    Subforms are fine, I'm unsure how to link (child / parent) to get the relevant data into the correct tables.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Without two mainforms, you would run into complications with adding either Staff members or Projects.

    Linking the subform is the easy part. All you need to do there is either:

    * Use the Control Wizard to place the subform and let it guess the link.
    * Set the Link Master and Link Child field properties to the appropriate fields (both ProjectID on the Project mainform or both StaffID on the Staff mainform).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jan 2006
    Posts
    54
    I wasn't planning to use the Forms to add any Staff or Projects - I was going to allow a user to add directly into the tables themselves.

    A member of staff could work on a project more than once in a day so the primary key suggested doesn't work unless I incorporate Start and End time.

    The DB is going to be used to monitor work done during the day.

    The order of importance is Date / Staff / Project so I would want all of this on one form.

  12. #12
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Its good practice to Never let user access tables directly. And this is when the
    "Never say Never" saying will never apply.
    Just to be clear that is never let user access tables directly this can lead to all sorts
    of problems and loss of control of data stored in your database.

    By using forms you can control information that is added to tables.

    The Pirmary key will be for ProjectID and StaffID in the Project and Staff table. This will
    be unique. You will have mulitple entries against projects and for staff members but these will
    go in your timeentry table. The timeentry table will pull the ProjectID (that will be unique)
    and StaffID (that will be unique) into the table and assign the date and times work has been
    carried out on those projects.

    On your time entry form I would use the wizard to create this and choose the timeentry table.
    To get ProjectID/Project Name and StaffID/Staff name I would look at the Combo box facility.
    Select the Combo box from the toolbox and make sure the Control Wizard is depressed as this
    help you.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, my bad... the primary key for the Time table should be StaffID, ProjectID and at least StartDate. I guess you could also use a TimeID as AutoNumber for the primary key as well, which would free up data that can be entered and reduce other complexities down the track.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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