Edit Query Data with multiple JOIN

[Bruce]

I have a query with 3 "INNER JOIN" statements that seems to be inhibiting me from being able to edit the query results.



The WHERE statement:

FROM ((Orders INNER JOIN WorkOrders ON Orders.OrderID = WorkOrders.OrderID) INNER JOIN TData ON WorkOrders.SID = TData.SID) INNER JOIN CDI ON TData.TDSID = CDI.TDSID

I found this explaination on the web:
http://bytes.com/topic/access/answer...ly-access-form

Note the statement regarding Multiple JOIIN statements in different directions in the "FROM" clause. He probably meant the WHERE clause.

Thanks

[jzwp11]

The FROM clause is where the joins are set up. The WHERE clause follows the FROM clause

SELECT...
FROM...
WHERE...

The best way to get around an un-updateable record set is to base your forms and subforms on the tables themselves rather than a multiple joined query. In your case, the main form would be based on your orders table and the subform on the workorders table. I'm not sure about the other two tables. Could you explain how those fit in relative to their relationships with the orders/workorder tables?

[Bruce]

BrainFart... I should read what I'm writing eh?

Relationship
Orders -> > 1toMany -> WorkOrders -> 1to1 TData -> 1to1 - CDI

I have a data entry form using VB to exec the query based on specifc field data entered by the user. The query uses the field data from the form and executes the query using that data. The results are displayed on a new form that allows users to edit/save the field data.

ex:
The user selected "Modify" from the open WorkOrders form, the query uses data from the open WorkOrders form to retrieve the individual TData and CDI records and present them in a new form allowing the user to modify the records.

[jzwp11]

Still not real clear on your table structure. Is it something like this?

Orders
-OrderID primary key, autonumber

WorkOrders
-WorkOrderID primary key, autonumber
-OrderID foreign key to Orders table
-SID foreign key to TData table

TData
-SID primary key autonumber
-TDSID foreign key to CDI table

CDI
-TDSID primary key, autonumber

If there is a 1-to-1 relationship between TData and CDI why not just put the CDI fields directly into TData and then you can have a main form based on orders with a subform based on workorder and in that subform have a combo box to populate the TData.

I have a data entry form using VB to exec the query based on specifc field data entered by the user. The query uses the field data from the form and executes the query using that data. The results are displayed on a new form that allows users to edit/save the field data.

I'm really not clear on what you are saying above. What type of query? an append query? Or is it like a search form where you users enter specific information and you open a form (based on your earlier query) to a specific record?

[Bruce]

very close

Orders
-OrderID pk - Entered by user

WorkOrders
- SID pk to TData table, autonumber
-OrderID fk pulled/copied from Orders table

TData
-SID fk to WorkOrders table pulled/copied from WorkOrders table
-TDSID pk to CDI table copy of SID
-TDSID pk to IDI table copy of SID

CDI or IDI
-TDSID fk to TData table pulled/copied from TData table

Yes a 1to1 between TData and CDI or IDI table.

example:
the customer already has an order entered into each table
the user receives a workorder to modify the details of the order
the user creates a new workorder using the add workorder form
the user enters the OrderID and SID in the form
the user selects WorkOrder Type "modification"
the user is prompted by VB if they wish to modify WorkOrder or CDI/IDI data.
If WorkOrder, execute query to retrieve WorkOrder data and present in form view for editing.
If CDI/IDI, execute query to retrieve CDI or IDI data and present in form view for editing

[jzwp11]

I don't understand your structure. The SID in the work orders table is a foreign key (fk) not a primary key. SID is the primary key (pk) of TData therefore, in TData SID is an autonumber field in WorkOrders it must be a long number field NOT autonumber. TData doesn't look right either. Can you post your database with any sensitive data removed?

[Bruce]

Thanks for your interest. I found a workaround where I shortened the query and eliminated 2 of the join statements. it works ok with one.

[jzwp11]

Glad to hear that you found a solution! Best of luck on your project.