Query with Multiple Right Outer Joined Tables 1

[LindaLou]

Is there a limit to the number of right outer joins you can have in a query? I have a form based on such a query. The Add New Record command button works correctly until I attempt to add the 4th table to the query. At that point I get the message "You can't go to the specified record" When the query contains only the primary table with right outer joins to only 2 other tables the form works correctly. The query will actually run and return the data appropriately in the query even when I add the additional tables, however the Add New Record button in the form stops working at that point. ?? Any help appreciated.

 

[Steve101]

Certain queries will be designated as "Read Only" depending on the underlying table definitions and the nature of the inter table joins.

The rules for this can be quite complex, but generally speaking, you need to ensure that any field involved (on either side) in a join between two tables is uniquely indexed if its on the "one" side of the join, and indexed if its on the "many" side. If you dont do this, the query will still return results, but will NOT allow you to make ANY changes to the returned results.

I think that the reason you are getting the error on your form's Add button, is that the form is working with a "read only" underlying query, and hense it cannot add a new record. You can check this by simply running the query independently of the form, and trying to add a new record from the query itself. If it wont allow you to add this record, then thats the problem. In this case, go through the fields in all of the tables associated with the query, and check that they are appropriately indexed.

Finally, I would question the query itself. I prefer to keep the underlying recordsets to forms simple, usually limited to a single (sorted) table. Links to other data can be done through combo boxes, and/or through the use of linked subforms on the main form. This may be food for thought, though your approach might be equally legitimate,

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[LindaLou]

Thanks Steve for the good suggestions. I verified that the field on the one side of the join was uniquely indexed and the fields on the many side were indexed. I tried to add a new record from the query directly and found an interesting situation. Let me explain:

Patients Table is the primary table on the One side of each join with the uniquely indexed field PatientID (primary key)

Tables A, B, C, D are all joined to Patient Table with a right outer join to the PatientID field in each of these tables. (ie Show ALL records in PATIENT table and only those records in each of tables A,B,C, and D which have corresponding records.

Following is the various combination of tables in the query which WILL allow addition of new records to the query:

Patient Table + A + B
Patient Table + A + C
Patient Table + A + D

The table combinations below will NOT allow addition of new records to the query:

Patient Table + B + C
Patient Table + B + D
Patient Table + C + D

If I make the following single combination of the Patient Table with ANY of the other tables the query WILL allow new record adds:

Patient Table + A
Patient Table + B
Patient Table + C
Patient Table + D

I then assumed there must be something significant about table A since the query always works when that table is used in combination with any other table, however, I could not find any difference in how the fields were indexed or defined in that table from the other tables.

It is also true that any time I try to combine ANY 3 tables plus the Patient table the query will no longer allow new record adds. ie:

Patient Table + A + B + C etc.

I appreciate your suggestion that perhaps I should rethink my query structure and form itself. I could build a subform or link to a second form from the first and change the underlying query for each form. I would be interested to hear any comments on why I may be seeing the difference in my current query pending the combination of tables used. I'm also wondering if it would be possible to make separate queries and then link the queries? I would like to keep things as simple as possible but am not sure how to get the desired result on the form I am building.

Thanks again for your help.

 

[Steve101]

Linda,

(a) Are the relationships intended to be one-to-optional-one; ie. for each Patient record, there might or might not be an associated A or B or C or D record (in any combination).

(b) If its one-to-many, I would advocate you look at the following possible approach: A main form, with a tab control on it with 5 tab pages; the first would contain them main Patient fields, the next four would contain subforms, linked on the PatientId field.

(c) Again, I mention that I dont like complex joined queries for data entry; you're running into one of the reasons why. I know that that does'nt help answer your question though.

If you send me a copy of your database (cut down appropriately, with sample data only, compacted and zipped), I'll happily take take a look at it.

Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[LindaLou]

Yes, Steve that is correct. The relationships are optional and there may not be an associated record in each table to the Patient ID.

I have been following your thoughts on rebuilding the form and have just completed a second form which at the moment appears to meet my purpose. The original main form is now based on a simple query of the Patient table and used solely for looking up existing patients or to add a new patient record. I have added a command button on the main form which will open the new second form filtered by the selected Patient ID from the main form.

The second form is based on the original query using all tables A,B,C,D tied to the patient table by the right outer joins. I do not need to add any new records from this form, only view the results of the query. Then from the second form I have command buttons which will open the appropriate data entry forms specific to each of the tables listed.

I will need to continue to add additional tables to this query as I build the database so am not sure if new problems will present down the road. But for the moment I now have the ability to add new patient records from the first form and view the query results on the second form.

Thanks again for your help and offer to take a look at the database. I may yet take you up on that offer as I get further into the project.

Linda