Orders > Products > Tasks to make Products > averaged times to complete tasks

[crossedout]

Good afternoon.
I'm new to the forum and have search to find the answer but maybe I'm not searching correctly. But, I am finding alot of very helpful, knowledgeable people here helping.
So, here is what I'm trying to do:

I have a database to enter Orders into. We pull down customers from pull down (from Table:Customers) into Table:OrderDetails. We enter Products from a subform (from Table:Products) into Table:Orders. So, now we have a list of ordered products.

On top of that, each product has a list of assigned tasks (a multiselect pulldown menu) from Table:Tasks entered into the Table:Product for each product.

I'm stuck figuring out how to make this happen:
When an order is entered, how can I make a list of tasks (assigned for each product for each order) populated automatically.

For example:
Order A = Company B orders
6 Product A (which has Task A, Task C, and Task E assigned to it)
4 Product B (which has Task A, Task B, and Task D assigned to it)

Order B = Company C orders
10 Product B (which has Task A, Task B, and Task D assigned to it)
5 Product C (which has Task A, Task B, and Task E assigned to it)

I want Table:Tasks to be populated with
Task A - Product A - Order A x6
Task A -Product B - Order A x4
Task A -Product B - Order B x10
Task A -Product C - Order B x5
Task B - Product B - Order A x4
Task B -Product B - Order B x10
Task B -Product C- Order B x5
Task C - Product A- Order A x6
Task D -Product B- Order A x4
Task D -Product B- Order B x10
Task D -Product C - Order B x5
Task E - Product A - Order A x6



I know I could enter these tasks manually, but I want to Table:Task populated automatically with the info from the other tables.

Is this possible?

=+=+=+=+=+=+=+=+=+=+=+=+
ADDITIONALLY I want to be able to track how long it takes to complete tasks. I could see a screen for entering this data to look like:
Form:Task Details
Button:New Details
Search Box: enter task AutoNumber and it selects that task from Table:Task
Button:Start = enters start date and time
Button:Complete = enters completed date and time
That shouldn't be too hard for me to figure out.

I then want to calculate averages of task times:
Task A average time =
(Task A, Product A, Order A)
+ (Task A, Product B, Order A)
+ (Task A, Product B, Order B)
+ (Task A, Product C, Order B)
======
total / 25 (25 is the number of parts that used Task A)


=+=+=+=+=+=+=+=+=+=+=+=+

Then the Product Task Time get calculated:
(Task A average time + Task C average time + Task E average time) X 6




I'm sure I over-shared but one thing I've picked up by scouring the forum is that more info usually better than less.
to recap: that main question is regarding Table:Task autopopulation.
But if you have advice for the other portions, I'm all ears.

Thank you very much

[June7]

Automatically creating records requires an INSERT sql action.

You have the associated tasks in Products table? Or is there a table of Products, table of Tasks, and a junction table ProductTasks?

Then you have another table that will document the task accomplishments for each OrderDetail record?

Code behind combobox that selects product (or the form BeforeUpdate event) can execute INSERT sql, something like:

CurrentDb.Execute "INSERT INTO TaskTracking(OrderDetailID, TaskID) SELECT" & Me.OrderDetailID & " AS ID, TaskID FROM ProductTasks WHERE ProductID=" & Me.ProductID

You might also want code that will verify tasks not already saved for that OrderDetailID (or set up compound index to prevent duplicate combinations of OrderDetailID and TaskID). Also, code to delete the tasks if the product is deleted from OrderDetails (or maybe Cascade Delete in relationships can handle that).

[crossedout]

Thanks for the reply June7,

I haven't had a chance to try out your suggestion. I had to make a few changes to prep the DB first.

I'll try to answer your question tho:
Yes, the Tasks have their own table with ID, description, details and a couple other fields. These are normal recurring tasks related to machinery we operate. Those tasks then are selected inside a multi select combo box (if I'm using the right word) inside the products table.

I don't, yet, have another table for tasks assigned from the order form. I was waiting to see if that was what was recommended.

Those are very true recommendations about the verification. I will look into that.
Thank you again for your help. I'll update here when I know more.

[crossedout]

If interested, here is a copy of the DB I'm working on. I am having a little trouble understanding the SQL and matching it to what I've got. I'm not asking anyone to do the work for me, I want to understand what I'm doing just as much as I want to get this working. But I'll take all the help I can get. Thanks

(I'm not sure how a database this small is already 1.4M before zipping...)

q7 db.zip

[June7]

I NEVER use multi-value fields. You are using a multi-value field in ProductDetails table to associate processes with products. This complicates my suggestion somewhat. This will require query that expands the multi-value field to separate records:

SELECT ProductDetails.[Product Number], ProductDetails.ProductID, ProductDetails.ProductProcesses.Value FROM ProductDetails;

Incorporating into my suggestion:

CurrentDb.Execute "INSERT INTO ProcessTracking(OrderDetailID, ProcessID) SELECT " & Me.OrderDetailID & " AS ID, ProductDetails.ProductProcesses.Value FROM ProductDetails WHERE ProductID=" & Me.ProductID

You have ProductID set as primary key in ProductDetails but saving ProductNumber into OrderDetails. The primary key should be saved as foreign key. Either save the ProductID or set ProductNumber as primary key. My examples assume ProductID is primary key and is saved as foreign key. Numbers are supposed to make better PK/FK due to indexing. ProductNumber is a text value.

Same thing seems to be set up for other tables. The autonumber fields are set as primary key but not saving as foreign key.

1.04mb is reasonable size for this file.

[crossedout]

I'm sorry for the long delay. Got caught up with the other parts of my job.

I just wanted to update that your solution @June7 worked perfectly. Thank you again. I've got some more questions I'm working on, so I will be sure to post here.
(I by the way have dumped the multi-value fields)
Brad