Hi all,


I'm trying to slowly teach myself this stuff and need a little help. I'm using expression builder for this and here's what I'm trying to do:

I have a table, tblAssemblies, connected to a junction table, tblAssy_Price, so that one Assembly may have multiple prices based on quantity of assemblies required, Price_QTY.
Each record for a given assembly in tblAssy_Price also has a field for Assembly_Time (Time to assemble one at a given quantity) and Assembly_Material (Material cost of one assembly at a given quantity.

I also have a table, tblSales_Order_Lines, where information about orders for assemblies is written. This table has, among others, fields Order_QTY, Total_Assembly_Time, Elapsed_Time and a field for Efficiency. Elapsed time is the time actually taken and Efficiency is Elapsed_Time/Total_Assembly_Time. Information is written to the table with a fairly simple form.

I would like to write an expression in the control source of the data entry form that will, if the field has a null value, examine all records in tblAssy_Price related to the current record in tblSales_Order_Lines by Assy_ID and find the largest value of Price_QTY that is still less than Order_QTY, THEN multiply tblAssy_Price.Assembly_Time from that record in by the value in tblSales_Order_Lines.Order_QTY for the original current record and write that to the Total_Assembly_Time field.

It's a pretty long story, but I believe it's possible, after all it's just a logic operation, but I'm very much used to Excel formulae and am having trouble wrapping my head around this. Any help or direction would be welcome!

I also want to, when a value is entered into tblSales_Order_Lines.Elapsed_Time, have the control do the simple division mentioned above (Elapsed_Time/Total_Assembly_Time) and record that as a percentage value in the tblSales_Order_Lines.Efficiency field. This last part, if I'm understanding things correctly, can just be a simple expression put into the 'After Update' event, would that be correct?