I designed 3 tables that have a 1-1 relation between all three. I created a select query that essentially dumps all 3 tables’ fields into the query. I can’t update the query and my form is utterly based on it. Form properties edit settings allow for additions & can’t think how else to fix problem. I faced it before but have forgotten how I cleared it. The form was originally based on the 3 tables rather than the query, but that gave same results. Any ideas, I’ve pored over below answer to me, but still nothing.

BarkerD
Member posted 01-08-2001 11:56 AM
--------------------------------------------------------------------------------
Applying or removing a filter should have no effect on adding a new record. You should check a couple of things.
1. Security Settings
2. Form properties - set to edit only
3. Recordsource of the form may not be updateable if based on multiple tables.

Option 3 is causing you the problem.

You can't just join any tables together in a query and hope to add a record to it.

The relationships need to be re-enforced and you need to use to the correct fields in order to update a record.

hth

What is meant by reinforcing relationships? I've been working with some multiple table joins and this is of interest to me, too.

Matt

In the Relationship window if you click on a join between tow tables you have the option of making it reinforced. This basically means that the linking field value in the 'Many' table MUST exist in the main table FIRST.
You can aslo tick the cascading deletes and updates so changes in the main table are reflected in th elinked table.

HTH

I’m assuming ofcourse Kevin that when you say reinforcing relationships in the relationship window, you mean the Enforce Referential Integrity tick box in the edit relationships window that occurs between tables?

Kevin,
Your explnation on the refer. Integ. Between multiple table takes into account if the the query is based on the tables, but what if the form is based on a query and not a bunch of tables you join together while in query builder. Or is your advice something that applies to queries as well, that they (the queries) ought to be based on tables w/ref. integ.?

I decided to go thru my tables & add referential integrity in 1-many relationships between my tables & found an error in one table that I corrected. That however did not help the query my form is based on, it still won’t add a record.

KevenM,
Yes you can add rows to a table via a query that joins multiple tables.

arage,
You need to fix the query so that it is updateable in order to make this work. The problem may be that you have not selected the linking columns from the related tables. For example, if the tables are joined on CustID, you need the CustID from each table to be included in the query and in the BeforeUpdate event of the form,you need to supply a value for the "other" two CustID columns (they should not be visible on the form so the user cannot enter a value for them).

Me.txtCustID3 = Me.txtCustID1
Me.txtCustID2 = Me.txtCustID1

The other possible problem is the way the tables are joined. This query will only be updateable if table1 is joined to table2 and table1 is also joined to table3. ie.
table1--->table2
table1--->table3
It will not work if table1 is joined to table2 and table2 is joined to table3. ie.
table1--->table2--->table3

Ok, I solved my problem & now I can add a rec. I would appreciate opinions on what criteria my incorrect table design met as to prevent me from updating my query thru the user form. Basically the tables behind my query were 3. The link was 1-1-2 and I changed it to 1-1-1. & that allows me now to enter data thru the user form.

The way I was understanding was thus, take this example for instance. 1 table is linked to 2 tables by the same index in both. I would consider this a 1-1 relationship b/c each record in the main table links to just 1 record in each of the other tables. I wouldn’t consider this a 1-many relationship b/c the main table records don’t link to more 1 record in each of the other tables. But this was wrong obviously as my situation dictates.

I'm experiencing exactly this same problem, but I can't seem to get the query to be updateable even after following the instructions provided here. What else can be causing this?

I have a two-table query which I can't update, but I have a three-table (unenforced joins) query elsewhere in the database which works perfectly (in this case, the fields are daisy-chained together, and the common field which joins the first two tables is not included in the query, but it still works fine!). The field which links the two tables in my faulty query is included in the query, but regardless of whether I include both, either or neither of the fields in the join, the query remains unupdateable. I also included a hidden field to automatically synchronise the two fields (if I understood the CustID example correctly), but this didn't seem to help. Previously I had enforced referencial integrity on the fields in question, but also to no avail.

Everything works fine when there's only one table in the query - but as soon as I add the second table to the query, it won't allow me to add extra records, even if none of the fields in the second table are being included in the query - simply having the second table sitting there is enough to break it. This even happens with only one field in the whole query! tblLicenseIssue is the first table, tblInventory is the second table. The SQL statement in this last case is:

SELECT tblLicenseIssue.LicenseIssueID
FROM tblLicenseIssue, tblInventory;

Not really rocket science is it? So what's the problem? It's fine as soon as you remove tblInventory from the SQL statement.

I have to implement this in another database I'm having the same problem with, and would really appreciate any help with this.

Thanks!

Greg

The problem is that your join is non-specific. Jet uses the newer join syntax and requires that you make explicit joins between tables in order for the query to be updatable. A join as in your query will produce a cartesian product (every row of tblA is joined to every row of tblB) and cartesian products are NOT updatable. Your join needs to look like:

SELECT tblLicenseIssue.LicenseIssueID
FROM tblLicenseIssue Inner Join tblInventory On tblLicenseIssue.SomeField = tblInventory.SomeField;

BTW, if you are not selecting any columns from tblInventory, why is it in the query at all?

Hi Pat,

Thanks once again for your help (I don't think this is the first time you've come to my rescue!) That worked fine. I do actually need fields from tblInventory, but I removed them from the example statement for simplicity's sake, to remove as many factors as possible which weren't affecting the problem. I've now added the extra fields in which I need.

Originally my joins were unenforced, but in this example I've included a primary key field in the join, and thus made it a one-to-many join. This would seem to imply that KevinM's original post had some merit, but as I mentioned I have a three-table query in which all tables are joined by unenforced joins, but this works perfectly, so I discounted this. What criteria could my three-table query be following to allow it to be updateable? I can post the db if this will help. Is "you must make explicit joins between tables in order for the query to be updatable" the golden rule, or are there other factors which control this? The SQL statement is:

SELECT tblInventory.EE_No, tblProjectBookings.CurrentlyAllocated, tblProjectBookings.SuperInitials, tblSupervisors.SuperLastName, tblSupervisors.SuperFirstName
FROM tblSupervisors RIGHT JOIN (tblInventory LEFT JOIN tblProjectBookings ON tblInventory.InventoryID = tblProjectBookings.InventoryID) ON tblSupervisors.SuperInitials = tblProjectBookings.SuperInitials
WHERE (((tblProjectBookings.CurrentlyAllocated)=False))
ORDER BY tblInventory.EE_No;

Hmm, maybe it's easier to post a sample db?

Thanks again for your help Pat, it's fixed my problem but I'd like to be certain I can avoid it next time around.

The only other issue I faced I've been able to get around (relational databases seem incredibly involved, which is why I'd like to know the 'golden rules' if there are any). The field EE_No is the asset number of our equipment, and the field tblInventoryID is the primary key in the inventory table. Not every piece of equipment has an asset number, so I can't use it as the primary key, so I've chosen an autonumber field which isn't used for anything else apart from being a unique primary key and ensuring the integrity of the database. But since this number isn't used for anything, no-one will ever see it or know what it is, but to be able to complete a new entry, you have to type in what this inventoryID is, as it's the field required for the join. There will only ever be one inventoryID for one EE_No, and vice versa, and the user will know and enter the EE_No, so the entry of the EE_No should be able to be translated to the inventoryID. But such is not the case - to be able to complete entry of a record, I have to explicity enter the inventoryID, even after the EE_No has been entered in the same record.

The fix was: tblInventory.EE_No is in the same table as tblInventory.InventoryID, which is the primary key for tblInventory (the 'one' part of the join). tblLicenseIssue.EE_No is in the same table as tblLicenseIssue.InventoryID (the 'many' part of the join). Originally I included tblLicenseIssue.EE_No in the query (same table as has the 'many' part of the join), but once I removed this and replaced it with tblInventory.EE_No (same table as the 'one' part of the join), the inventoryID fields are automatically populated with their correct values.

Hope this helps someone out there, but two questions remain:
1) what are the 'golden rules' for linking tables?
2) why did my three-table scenario work?

Greg

The reason why the third table approach works is becasue it can provide a link between two or more tables.
The third table allows you to enter fields that may be secondary or even foreign resultsing in one to many relationships.
With a two table database your data is restricted therefore getting the 1-1 relationships.
To make a system you have to have a minimum of 3 tables where at least one provides some sort of a lnk between the tables

I told you why your two-table join did not work. You did not specify whether it was an Inner, Left, or Right join and you didn't specify the fields that should be joined. Your three-table query did work because the join was properly specified. As soon as you specified the join in your two-table query it worked.

It is not the number of tables involved in a join but the type of join and the relationship between the tables. For example, you can update Customer-Order-OrderDetails because the three tables have a hierarchial relationship - 1 customer - many orders- many order details. But you cannot update a three table join where there are two separate 1-many relationships. An example of that would b Students-Grades and Students-Activities. Grades and Activities are both related to Students but not to each other therefore a query containing these three tables would produce a non-updatable recordset.

Hi Pat,

Yes, thanks for what you said - I realised why my 2 table example didn't work, and it did what I wanted it to, but there had been a couple of earlier suggestions about how to fix it, so I was very interested to know whether the key is wholly and solely the join type, or whether some of the other factors are also required (e.g. Me.txtCustID3 = Me.txtCustID1; Me.txtCustID2 = Me.txtCustID1: only if multiple tables aren't joined (implied by KevinM)). What I was requesting was I guess dot points of things to do to make sure the recordset is updateable, including everything you need and discarding anecdotal stuff which doesn't actually make any difference. I've got my present example working now, but a condensed list may help someone for next time.

Greg

Open help from the database window and search for "updatable query". The entry will explain how Jet determines if a query is updatable. It is one of the better help entries.

Relationships defined in the relationships window should have the Enforce Referential Integrity checkbox selected. You should also choose Cascade Delete if appropriate. I never use changable keys so I've never had occasion to use Cascade Update. You should also be aware that other relational databases may not support Cascade Update should you need to upsize. When you choose to enforce RI (not reinforce as KevenM kept saying), Jet will ensure that the many-side (or right side of the 1-1) table does not contain any orphan records. Once RI is established on the relationship between two tables, you will normally be allowed to update either/both tables in a query. Depending on whether you have included the foreign key from the 1-side and its related primary key from the many-side table, you will be able to add or change key data. Deletes ALWAYS apply to the many-side table. If you want to delete rows from the 1-side table, you cannot include the many-side table in the query.

Great, thanks! :)

Greg

Now I really feel I'm going crazy. After what I thought was a success, I got to work this morning and found the changes I'd made to the database were all gone. The results I now have are different.

I have two tables: tblLicenseIssue and tblInventory. The purpose of this query is to add information to tblLicenseIssue, some of which relates to information in tblInventory. So tblLicenseIssue is where I'm wanting the new data to go. Yesterday when I typed in the EE_No into the tblInventory.EE_No, the query would add a new record to tblLicenseIssue, and tblInventory.InventoryID would be displayed. I could achieve the same by entering tblLicenseIssue.InventoryID (which is the many part of a one-to-many link with tblInventory.InventoryID). Now I have the situation that whenever I type a value into tblInventory.EE_No, rather than adding a record to tblLicenseIssue and extracting the appropriate information from tblInventory, it adds a new record to tblInventory, even when I type a EE_No which already exists in tblInventory. It will behave as it did yesterday if I type a number into tblLicenseIssue.InventoryID, but as previously described, this number wouldn't normally be shown anywhere, and is meaningless to the user.

Trying to follow the logic through, I can see why it's doing what it is, but I can't see how it could have done what it did yesterday. Is there something simple I've missed in the configuration somehow? How can I instruct Access to extract existing data when I enter a number into tblInventory.EE_No, rather than creating a new record in tblInventory?

Hope this hasn't confused completely!

Greg

I can't tell what is causing your problem so I'll try to explain how a query with a join adds/updates data.

tblA.PKAfld, tblA.Datafld, tblA.PKBfld, tblB.PKBfld, tblB.Datafld

The two middle fields are respectively the foreign key to tblB and the primary key of tblB. Assume that PKAfld and PKBfld are both autonumbers.

Scenerio1 - enter data in tblA.Datafld, enter existing ID in tblA.PKBfld -
A row is added in tblA and tblB.PKBfld and tblB.Datafld are autopopulated.
Scenerio2 - enter data in tblA.Datafld, enter invalid ID in tblA.PKBfld -
An error message is returned because the ID does not exist in tblB.
Scenerio3 - enter data in tblA.Datafld, enter data in tblB.Datafld -
A row is added in tblA, a row is added in tblB and tblA.PKBfld and tblB.PKBfld are autopopulated with the new autonumber.
Scenerio 4 enter data in tblA.Datafld, enter existing ID in tblA.PKBfld, enter a value in tblB.Datafld -
A row is added in tblA and tblB.PKBfld is autopopulated. tblB.Datafld is updated to the new value.

My guess is that you have Scenerio3 and that is causing a new row to be added to tblB. Your code needs to populate tblA.PKBfld NOT tblB.Datafld if you want to reference an existing record. This is normally done via a combo if you don't want to show the primary key fields. The combo would show the tblB.Datafld value but would store the tblA.PKBfld value.

To verify my analysis, open your query directly and try populating the various fields as I have described.

Hi Pat,

Yes, as you suggested, my scenario was like scenario 3. Translating this into my situation, I can't really see how it worked the way I described. Every time I entered data into tblB.Datafld (tblInventory.EE_No), a new record would be added to tblB (tblInventory). Maybe I am insane???

My final implementation seems to work now - what you suggested in the examples works in the queries so long as I enter the tblA.PKBfld. Then, in the form, I've got two combo boxes which looks up two sets the required data (tblB.Datafld1 and tblB.Datafld2). The combo box queries take with them the tblA.PKBfld and store the corresponding values in the appropriate fields. I've also made use of the hidden fields in the BeforeUpdate property as you advised, to populate EENo with EE_No.

For anyone else's reference, here is the sample database Pat proposed. It helps to be able to see how the joins / updates work.

Thanks again!

Greg

Ok I'm in similar situation here now.

I have a form for adding new clients that is based on a query of three tables. As per Pat's suggestion for enabling multiples entities (of which client is one of those) to hold multiple address, client table is based on the entity table and address table is related to client table:

tblEntity
EntityID
*attributes relating to all entities*

tblClient
EntityID (FK to tblEntity; 1-1 relationship)
*attributes relating to all clients*

tblAddress
EntityID (FK to tblEntity; 1-M relationship)

Because the Client and Address aren't directly related, does that render the query nonupdatable?

What can I do to allow someone to enter a Client record with an address on single form?

Thanks

I should add that before a Client record can be created, Entity record must be created first in order for Client record to relate.

However, Access doesn't seem to recongize EntityID as a PK and propagate the keys, even though I included tblEntity.EntityID in the form's recordsource query, while specifying that both Client and Address must have have same EntityID. Because of this, query is nonupdatable since there is no PK to exist.

Does that mean I have to write VBA code to insert in a Entity record in order to allow users to create a new Client Record? (I would prefer to keep tblEntity mroe or less transparent to the users as it is more for Access's benefit than for user's benefit).

Thanks.

Played with my queries a bit. I could create two separate updatable queries that joins Entity table with Address and Contact tables respectively. Howevering joining those queries together only allows for partial updates (meaning I can update only one table's fields, but not the other tables depending on how I define the joins), regardless how I join the tables together.

Any suggestions?

1. Your form should be based on a query that joins entity to client. The form MUST populate at least one field in each table for rows to be added to both the entity and client. If you do not populate at least one field in the client table, no client row will be inserted.
2. The join in the query should be entity-client and entity-address NOT entity-client-address as you may have it. The Entity-Client relationship ia 1-1 so this is considered a "lookup". I'm not sure how you implemented the entity-Address relationship. Whether it is 1-1 or 1-m shouldn't matter since at most a single 1-m relationship exists in the query.

#1, Yes; I figured that out.

#2 is exactly what I did with my first two queries; query 1 is Entity-Client and 2 is Entity-Address. Both are updatable.

But the thing is to get those two queries joined together to update three tables from single form. Doing so only allows for partial updates...

BTW, Address is a 1-m relationship.

What do you mean - partial updates?
Post the query.

Partial updates as in I can enter data only for Entity table and Client table but not for Address table. That is even if I use the two beforementioned queries toghether, which are fully updatable independently but not when joined together.

First Query
SELECT tblEntityRecord.EntityRecordID, tblEntityRecord.EntityID, tblClients.LastName, tblClients.FirstName, tblClients.SSN, tblClients.DOB, tblClients.CategoryID, tblClients.GenderID, tblClients.EthnicityID, tblClients.IntakeDate
FROM tblEntityRecord LEFT JOIN tblClients ON tblEntityRecord.EntityRecordID = tblClients.EntityRecordID;

Second Query
SELECT tblEntityRecord.*, tblAddress.StreetAddress, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.CountyID, tblAddress.MailingAddress
FROM tblEntityRecord LEFT JOIN tblAddress ON tblEntityRecord.EntityRecordID = tblAddress.EntityRecordID;

Third query to join two queries together
SELECT qtrClientEntryA.*, qtrClientEntryB.StreetAddress, qtrClientEntryB.City, qtrClientEntryB.State, qtrClientEntryB.ZipCode, qtrClientEntryB.CountyID, qtrClientEntryB.MailingAddress
FROM qtrClientEntryA INNER JOIN qtrClientEntryB ON qtrClientEntryA.EntityRecordID = qtrClientEntryB.EntityRecordID;

I left the join for last query unspecified, as no matter how I specify the join, it's either totally nonupdatable or partially updatable. If I join A to B, I can update A but not B, and if I join B to A, I can update B but not A.

1. Did you create a single query that included both left joins? That should have worked.
2. If #1 doesn't work, try changing the RecordSetType property of the form to Inconsistant updates but don't do this until you have determined that #1 doesn't work.

1) That was what I did at first. I left-joined Client and Address to Entity in single query but I had same problem of partial updating.

Is there other suggestions before I try #2?

I haven't done this in a while so I'm winging it here. Make sure that you have the foreign key fields selected.

Technically you don't need a left join for the entity-client since you know you are working with client records so you could change that to an inner join which might help. After that, I'm fresh out of ideas. The inconsistant updates setting sounds dangerous to me so I don't like to use it.

Whooo, wait a minute. If you have a 1-m with addresses, you should be using a subform for the address data. That definitely works. The main form is a query with entity/client. The subform is a query with just address. The master/child links keep the forms sync'd. I was thinking you had a 1-1 with address also in which case you would be using a main form only.

Yay!

Subform does work- and better yet, I got it to be transparent so users'll never know there was a subform. :D

You're a treat, Pat!

Just goes to show - you never know what piece of information is going to be the key to the solution.

Why did you decide on a 1-m relationship if there will only ever be one address?

I have to be able to store both mailing and physcial address in case if they are not the same. Rare case, but that is my requirement from uppers. Also, since they will also relate to business, who may have multiple office, this give me more scalablity.