Update variable for each item based on calculation

[1011000]

I have a database including the time it should take to do any task listed based on an average time it took to do the task in the past. Such as it takes someone about 5 min to install a receptacle, so the database should show the time goal for that task at 5 min.

I record the tasks done on a worktable with the total time for that work. I have a query that will take the total time and divide it among the tasks to get a simulated average for each task. I'm still new on access so I can only get the query to make a new table with the results.

I need the query to update the time column in the tasktable for each individual item. Not every record should have the same time and having a separate table is making other queries not work due to no record of some tasks in the resulting table.

 

[Darrylles]

Hi,

Post the table structure that you want updating, and the table structure that holds the recorded times. (Include table names and all field names).

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[1011000]

sorry,

Tables:

__________________________________
tasktable:

task time cost
----------------------------------
receptacle 2.34
service run 8.79
3-way switch 3.44
ect... ect...

__________________________________
Ttime:

task time
------------------------
receptacle .083333
service run 2.86304
3-way switch .894700
ect... ect...
__________________________________
worktable:

workdate workid hours
--------------------------------
11-4-06 352 2.5
11-6-06 353 8
11-7-06 354 3

________________________________
worktasktable:

workid taskdone Qty
--------------------------------
352 receptacle 25
353 receptacle 3
353 3way switch 12
353 floor rec. 4
354 service run 2
354 3way switch 15

================================
Queries:
________________________________
timeSum:

SELECT [worktasktable].[workid], Sum([worktasktable].Qty) AS timeSum
FROM [worktasktable]
GROUP BY [worktasktable].[workid];

________________________________
timesplit:

SELECT worktable.workid, worktable.hours, timeSum.timeSum, worktasktable.taskdone, worktasktable.qty, ([hours]/[timeSum]) AS tvalue, ([Qty]*[tvalue]) AS UTime
FROM worktable INNER JOIN worktasktable ON worktable.workid = worktasktable.workid
WHERE ((([Material Use Table].Qty)>0));

_________________________________
sumtime:

SELECT [worktasktable].[taskdone], Sum([worktasktable].Qty) AS SumOfQty, Sum(timesplit.UTime) AS SumOfUTime
FROM [worktasktable], timesplit
GROUP BY [worktasktable].[taskdone];

_________________________________
avgtime:

SELECT [sumtime].[taskdone], [sumtime].SumOfQty, [sumtime].SumOfUTime, ([SumOfUTime]/[SumOfQty]) AS [Time]
INTO Ttime
FROM [sumtime];

==================================

I need the information currently being put into a new table "Ttime" to go into the existing table "tasktable" for each task.

 

[lespaul]

so what values do you want to put in the TaskTable.Time field and how do you calculate that (not in queries, in math)?

[tt]
tasktable RESULTS:

task time cost
----------------------------------
receptacle ?? 2.34
service run ?? 8.79
3-way switch ?? 3.44[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[1011000]

I want to put the calculated time based on queried average in the time field of the tasktable.

The time it took to do a job
/ total qty of tasks completed
= time value

time value
* qty of any particular task
= average time to do that task on that job

now average the time that task takes across all jobs

plant that time average for each task into the existing tasktable.

it should result in an ever-changing average time goal for each task. If you keep taking less time to do a task, that task's time should get shorter.

 

[1011000]

Will I need to use VB?