Creating an Updateable query even when linking another table.

[jamesborne]

Hello there!


I am trying to create a POS db but I have hit rock bottom.
Here are the tables I have:

ProductT:
ProductID
Product Name

PriceT:
ProductID
Price
PriceStartDate

OrderT:
ProductID
Quantity

Now Here's my query:
ProductID (From OrderT or ProductT)
Quantity (From OrderT)
Max(Price) (From PriceT)

Problem: As soon as I add PriceT to the query it becomes in-updateable and I cannot add new records to the query.

Think you can solve it?
Cheers

J. Borne

[June7]

Show the query SQL statement.

Issue is the aggregate function Max(). SQL aggregate functions must be used in a GROUP BY (totals) query.

You could use DMax, a domain aggregate function.

Calculated values in a query or in textbox ControlSource will not save to table.

[jamesborne]

SELECT OrderSubT.ProductID, OrderSubT.Quantity, PriceT.Price, [Quantity]*[Price] AS SubTotal
FROM PriceT INNER JOIN (OrderT INNER JOIN OrderSubT ON OrderT.OrderID =OrderSubT.OrderID) ON PriceT.ProductSubID = OrderSubT.ProductSubID;

[June7]

Not seeing Max in that query. Is there more than one price record for each product? If so, the query will not be editable with a direct join and cannot include PriceT table.

Options:

Search Price table for the most current price and VBA code to save the found PriceID or actual price value.

Select price from a bound combobox list.

[jamesborne]

Regarding your option: "Search Price table for the most current price and VBA code to save the found PriceID or actual price value.", do you mean using DLookup in the form?

Thanks

[June7]

Would be a little more complicated. Getting the syntax correct for nested domain aggregate functions is tricky when used in query or textbox ControlSource. I assume the ID fields are all number type.

DLookup("Price", "PriceT", "PriceStartDate=#" & DMax("PriceStartDate", "PriceT", "ProductID=" & [ProductID]) & "#")

Possible issue during data entry is that the ProductID is not known when the record is initiated. I think the expression will recalculate as soon as the ProductID is selected.

Do you want the price saved to OrderT? You don't show a field for the price.