How to update records when there is a multiple join? 1

[idbands]

I have two tables a, b. Table A has a bunch of data. Table B has nothing in it (the two fields in the table are Order Number and Notes).

My query pulls a bunch of data from A and then you can type in a comment in the Notes field that then gets written to table B.

My query looks like this:
Select blah, blah, blah, blah, Notes
FROM RIGHT JOIN A ON b.OrderID=a.OrderID

And I can add notes into the Notes field when in Datasheet view and the notes are saved to table B.

My problem comes in when I also need to see data from table C too.

If my query looks like this:
Select blah, blah, blah, blah, Notes
FROM (C inner join A on C.orderid = A.orderid) left join ON b.OrderID=a.OrderID

Then I can't write notes to the Notes field in the Datasheet view anymore. Is there another way of doing this so I can still write data to table B?

Thanks in advance!

 

[dhookom]

Are all of your joins linking primary and foreign keys? What are the one to manys in the joins?

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Some reading:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[idbands]

Thanks, Duane. That simple question totally made me realize what I was doing wrong. I should have linked everything by OrderItemID instead of OrderID.

Thank you!