Can't change data in Query?

[thatguy]

Hey there folks--

This is rather odd.. I have a normal select query involving 5 tables; here's the SQL:

SELECT MASTERQUOTE.*, Salesperson.*, Customer.*, Part.*, Pricing.*, [nQuantity]*[rSellingPrice] AS Total FROM (((MASTERQUOTE LEFT JOIN Salesperson ON MASTERQUOTE.nSalespersonKey = Salesperson.pkey) LEFT JOIN Customer ON MASTERQUOTE.nCustomerKey = Customer.pkey) LEFT JOIN Part ON MASTERQUOTE.pkey = Part.nQuoteKey) LEFT JOIN Pricing ON Part.pkey = Pricing.nPartKey ORDER BY Salesperson.cInitials, MASTERQUOTE.dQCreate, Customer.cName, Part.cPartDesc, Pricing.nQuantity;

Seems simple enough, but I am not able to change any data in the Query -- not from a form or in datasheet view. I have no idea why.. there are no record locks, read-only flags or anything that should prvent changes to data.. Any thoughts??

Thanks
-- michael~

 

[dhookom]

Are all your joins on primary and foreign keys? I would create a form that is based on just a table or two. Then use subforms and combo boxes to display related records/values. The orders form in Northwind probably involves a similar number of tables but uses subforms and combo boxes.

Duane
MS Access MVP

 

[willir]

thatguy - Michael

Seems simple enough

Per Access, you are trying to update your quote table, salesperson table, customer table, part table and pricing table. It would not know which fields you want to edit.

Relational databases tend to want you to make changes on either the "one" side or the "many" side of a relationship.

Access uses DISTINCTROW (option unique to Access) to help get around this, but...

I would definitely go with Duane's suggestion and use subforms embedded in a main form.

My initial take would be to...
- Main form based on sales rep
- 1st subform retrieves quote and customer info in a continuous form.
- select a quote
- 2nd subform displays details of quote for selected quote

Richard