Form/Query: A row of a table, plus two editable fields from two different rows of a 1:n related table

Question

I have three tables, one holding material-data (materials), one holding suppliers (suppliers) and one holding prices per supplier and material (supplierPrices). One material can have multiple prices, one price per supplier.

I have a form that displays various material-data per row. This form also displays an editable price of a specific supplier (supplierID 100). The table relationship in the query is "include all rows of materials where the joined fields are equal" and in the criteria supplierID = 100. So there's exactly one row per material, including the editable price of that supplier.

But now i would like to display a second editable price per row, the price of supplierID 200. If i extend the criteria to "supplierID = 100 OR supplierID = 200" i get two rows per material, which is not what i want. What i want is to display both prices in one row, together with the big bunch of material-data. First i did it with a VBA function, calling it in the query, but then the controlsource is an expression and data can't be edited respectively stored.

Is there a way to do this with some special select in the query? Or would i rather have to use VBA (again) to store it in the proper table?

Thanks for your hints.

Alin I Alin I · Accepted Answer · 2013-04-03T13:21:54

TRANSFORM Max(supplierPrices.[price]) AS price
SELECT supplierPrices.[materialID]
FROM supplierPrices
GROUP BY supplierPrices.[materialID]
PIVOT supplierPrices.[supplierID];

but this is readonly.

Form/Query: A row of a table, plus two editable fields from two different rows of a 1:n related table

1 Answers