Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Red face Unanswered: Help - Trying To Multiply Two Entries In Two Records Into A Form

    Hi, am trying to set up a little database for work using access 2003 and am encountering what I'm sure is a very small problem but which I cannot resolve, due to me having last used databases 3/4 years ago (and on SQL not Access.)

    Basically, I have a list of employee records, and each employee populates their record with a list of tasks, numbering how much of each tasks they do throughout the day. I have a second table that lists a timing for each of these tasks.

    I want to create a form that will pull out the employee information, and then show the time spent on each task (i.e. multiplies the number of times one task is done, by the timing given for each task.)

    I don't have any relationships set up between the tables as I can't quite get my head around what it is I need to do.

    Any help would be much appreciated!

    Thanks,
    Brekky

  2. #2
    Join Date
    Apr 2009
    Posts
    14
    Your first step is to put in the relationships. I know they can be tricky to get your head round, but without them you don't really have a database!

    You presumably have a table for Employees and a table for EmployeeTasks (ie the list that the employee populates with what they have done). Both tables should have primary keys. The EmployeeTasks table also needs a field that holds the key for the Employee it relates to. The relationship goes from the Employees primary key to the Employee field in the EmployeeTasks table.

    You need a similar relationship from EmployeeTasks to TaskTimes (primary key from EmployeeTasks links to EmployeeTask field in TaskTimes table).

    Basically for relationships you need to think in terms of one piece of info relating to many other pieces of info. So one employee can have more than one task and one task can have more than one timing. The 'one' side means the link comes from the primary key, the 'more than one' side means the link goes to a field that is not the primary key (this field holds the relevant primary key from the 'one' side).

    If you have a list of Tasks, and each task can be done by more than one employee, then you need an intermediate table. This is the EmployeeTasks table. So you would then also need a link from Tasks (one) to EmployeeTasks (more than one).

    Once you have the relationships in place the other things you want to achieve will be much easier, so try to sort that out and then repost the problem.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you just need to put the calculated value on a form/report, I would create a text box on the subform (or whatever form has the number of Number of Tasks and TaskNumber field (or whatever the fields are called)) and simply put in this expression as the sourceobject (or you can also populate the value in vba code.)

    =[HowMuchOfEachTaskField]*Dlookup("[TaskTime]","TaskTimeTable","[TaskNumber] = " & Forms!MyMainFormName!MySubformName!TaskNumber & "")

    If you're not sure about relationships, using the dlookup command will return an error where there is no corresponding TaskNumber in the TaskTimeTable. (note: you can also use the nz expression to account for these.) But your calculations of values are usually left to be calculated on the form/report.

    Otherwise for your relationships, use Kafrin's advice on setting up the relationships.
    Last edited by pkstormy; 09-28-09 at 22:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2009
    Posts
    7
    Hi Kafrin

    Thanks for your post, however I am still having problems with my relationships, will try and explain below:

    I wish to have one record, per employee that lists a quantity for all possible tasks, e.g David has 2 for Makes Tea, and 4 for Drinks Tea

    I then have a table with the heading Task and another heading Time to hold all tasks and their relevant timings. So it has 2 records,
    Makes Tea - 4, Drinks Tea - 7

    The problem then is that I can't define any relationship because one heading is Task and the other headings are all the individual task names (e.g Drinks Tea, Makes Tea)?

    Does that make any sense?

    Thanks,
    Heather

  5. #5
    Join Date
    Apr 2009
    Posts
    14
    That does make sense, and you're right that it causes you major problems for putting in relationships. In which case have a look at pkstormy's advice about using DLookup (and obvioulsy repost if you need more help with this function in your case).

    On a general note, your biggest issue for relationships is the basic structure of your tables. The table holding the quantities of tasks has one field per task. So if you have a new task you need to add a new field. Personally I would choose the following structure:
    One table of Employees (fields are EmpID (primary key), EmpName, EmpNo, any other fields needed such as Address, dept, phone, etc)
    One table of all possible Tasks (fields are TaskID (primary key) and TaskName).
    One table linking tasks to employees called EmpTaskMap (fields are ETMapID (primary key), EmpID (links to Employees), TaskID (links to Tasks))
    One table recording times for the tasks for the employee called TaskTimes (fields are TTID (primary key), ETMapID (links to EmpTaskMap), TimeSpent)

    Obviously this would involve some major redsigning of your database, so I'm not suggesting this as a feasible option for you this time, but hopefully this will help you in the future.

  6. #6
    Join Date
    Sep 2009
    Posts
    7
    Hi Kafrin

    Thanks for your help again. Apologies but I don't understand part of your proposed database solution. I see TimeSpent is there for the employee to put number of times they have done a particular task, but I don't see a Timing anywhere linked to an individual task, with which to multiply by?

    Thanks,
    Brekky

  7. #7
    Join Date
    Apr 2009
    Posts
    14
    Timing (TaskTimes) is linked to an individual task for an individual employee so is linked to the EmpTaskMap.

Posting Permissions

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