Finding max/min values and displaing them in a subform in Microsoft Access -

good morning,

i'm building form in microsoft access track components/parts go building new products, , have question pertaining use of min/max values.

first, context. simplified*, tables involved are:

1. components: these pieces needed build widgets being sold
2. suppliers: these places can components from. components available @ multiple locations @ different price points.
3. products: various widgets can built.
4. compsuppliers: many-to-many relationship between components , suppliers. includes additional information, such unit price each item each supplier.
5. prodcomponets: many-to-many relationship between products , components. includes few additional details, such how many items required make widget in question.

all tables have autonumber id field , relationships defined in database.

i make form brings information when viewing given widget there sub-form lists components go said widget, plus best price & supplier each part. on main form can sum of these values 'best component price' (and similarly, worst-case price if have go more expensive suppliers) can set appropriate list price widget.

in end, form/subform should allow create new widget, add components through subform , give total parts cost (best case & worst case).

example:

building widgitizer requires 2 x foo , 1 x bar.

• foo available supercorp prime $1 ea
• foo available emergicorp alpha $2 ea
• foo available emergicorp beta $3 ea
• bar available supercorp prime $5 ea

• in components subform, foo should appear min cost $1 @ supercorp prime, worst-case we'd have pay $3
• in components subform, bar should have min & max cost $5, supercorp prime
• best case cost (2x$1 + $5) = $7 in parts build 1 widgitizer
• however, if can't parts our preferred supplier whatever reason might cost as (2x$3 + $5) = $11 in parts build widgitizer

i thought relatively straightforward, can't seem make work , haven't been able find topics similar enough show i'm going wrong in coding attempts.

thank-you help/insight! let me know if additional details needed.

~d

*the actual implementation designed few complications, if can simple case described above working should expand work i've got.

you're off well-designed start, there's quite lot do. assuming decent sql (especially group queries), can build read-only form (the editable version phase 2). here's breakdown see it:

1. make query serve basis main form. when done, you'll have 1 row of data per widget.
2. make query serve basis sub form. include mins , maxes per component.
3. make 3rd query lists details down individual suppliers each component. no mins or maxes here.
4. build sub form first. doesn't need alot of frills since it's going embedded.
5. build main form , embed subform. link 2 widget id.
6. build 3rd form listing individual suppliers each component.
7. in subform, add button detail section. when pressed, pop 3rd form listing individual suppliers , prices.