Ok, I'me a little confused with the different lookups.

I have made a blank db whilst trying to figure this out.

In one table I have the following fields

INGREDIENTS DATA TABLE
---------------------------
INGT - short text - ingredient name
KEY >>> INGN - Autonumber - Ingredient Reference Number
INGMIN - number - minimum ingredient yield (10% less than target)
INGMAX - number - Maximum ingredient yield
(10% more than target)
INGTAR - number - Target Ingredient yield

COLLATE TABLE
--------------------

Key> JOB - Autonumber - autogenerated job ID
INGN
- Number - Ingredient Reference Number > preloaded form previous form
INGPART - Autonumber <--- job ID individual parts
INGTAR
- number - Target Ingredient yield
> preloaded form previous form
INGACT - number - Actual yield for Ingredient
INGBC - number - batch code of ingredient

Collate Form
------------------

user inputs INGACT and INGBC fields only, other data is preloaded upon ingredients selection


GOAL In this exercise
-----------------------------

I'm trying to have it so if I type in a yield UNDER the minimum value, it will allow me to add a duplicate ingredient for the remaining amount ie a "SPLIT" amount:

so for example:

JOB - 1
INGPART - 1
INGN - 1
INGTAR - 10,000
INGACT - 7000
INGBC - 1234

Split dose accepted

JOB - 1
INGPART - 2
INGN - 1
INGTAR - 3000


INGACT - 3000
INGBC - 1235

Target accepted - batch complete




Is this possible? I'm trying to do this for my other topic but have stripped it all back until I understand the lookups and how to achieve this?

Thanks for your help in advance!

Cryst