2

I built a query based on a child table but the join is not the way I want it to be and it's not allowing updates.

I have other similar queries based on similar child tables which are all one-to-one linked on the main table by a unique patient ID number. All of the other queries work exactly as they are supposed to work and allow updates.

However, when I try to inner join this query with the patient ID number on the child table, it will not allow updates and I have been racking my brain for days now and I still don't have a clue why this query is not working correctly.

I am new to Access and unsure what information you might need in order to help me so I am attaching everything I can think of. Any help, or even a clue about where to look, would be greatly appreciated.

Here's the SQL from the query.

SELECT [6_Extubation].ID, [6_Extubation].*
FROM 6_Extubation INNER JOIN [02-ActivePatientsQuery] ON 
[6_Extubation].Patient_ID_Number = [02-
ActivePatientsQuery].Patient_ID_Number;

Here's a photo of the query structure.qryStruct

And here's a photo of my table relationship structure. tblStruct

Thanks for taking a look!

Jim Bray
  • 758
  • 8
  • 12
  • Users should not work directly with tables and queries. Conventional design would be a form/subform arrangement for data entry/edit. What is the structure of 02-ActivePatientsQuery? Are the updatable queries linked on PK/FK fields? – June7 Jun 03 '17 at 21:51
  • What exactly are you looking to update? Could the update perhaps be done through a subquery? – Kostas K. Jun 03 '17 at 21:55
  • I was attempting to simplify my situation. The form is where the problem resides. I have queries getting the information to the form and I also have subforms. However, after reading a little deeper on the one to one relationships, it appears that I should combine all three tables into one table. All of the fields in the four tables are about one patient.What are your thoughts? – Jim Bray Jun 03 '17 at 22:11
  • 2
    See: [Dealing with Non-Updateable Microsoft Access Queries](http://www.fmsinc.com/Microsoftaccess/query/non-updateable/index.html) and [Allen Browne: Why is my query read-only?](http://allenbrowne.com/ser-61.html) -- Whether the 1:1 relations are useful or should be integrated in the main patients table is for you to decide, but they do not cause the issue. My bet is on `02-ActivePatientsQuery`, please add its SQL. – Andre Jun 03 '17 at 23:39
  • 1
    I agree with Andre and the SQL will hopefully reveal the problematic details. Perhaps `02-ActivePatientsQuery` can be optimized to allow the behavior you want. But if not--if the conditions inside the query necessarily destroys the uniqueness of the joined column--it can still be possible to have an updatable query with something similar to `SELECT SHY.*, SE.* FROM [6 Hour Year] As SHY INNER JOIN [6_Extubation] As SE ON SHY.Patient_ID_Number = SE.Patient_ID_Number WHERE SHY.Patient_ID_Number In (SELECT DISTINCT 02APQ.Patient_ID_Number FROM [02-ActivePatientsQuery] As 02APQ)` – C Perkins Jun 04 '17 at 05:41
  • I stepped back and looked at my database and decided to combine all the fields into one table. There are around 50 fields in the original table and I broke it apart when I first designed it thinking that doing so would somehow improve performance because all the fields did not apply to every patient. However, the split database still needed an entry for every patient, in order to show they did not need those questions answered. I put the table back together and everything seems to be working fine now. Thanks again for all the help and I am very sure I will be back with another question soon! – Jim Bray Jun 04 '17 at 15:30

0 Answers0