Tables and Relationships (1 Viewer)

[LadyDiss]

Hi all! Seems like my problems always deal with relationships!! Anyway, I am working on another project too, have a table with some client information, first name, last name, address, and zip. Well, got it to work based on the ZIP, it populates the protection class and territory # (this is a separate table with ZIP as the primary key) ... however, I also want to base on the protection class and territory # and if the construction is frame or masonry, populate a premium group field for me. I think it works in the query, however, it won't let me add any more records, says the recordset is not updateable. Also, can't add that to a form, since it won't let me update. I was using another table. What am I doing wrong?

 

[Pat Hartman]

You will also find that the query is not updateable. A number of things will make a query not updateable. Some of them are:

1. ODBC tables without primary keys
2. ODBC tables for which you don't have update permission
3. Aggregate functions in a query
4. the Distinct predicate

Post your query if the above list doesn't help you.

 

[LadyDiss]

Well, it is possible that I don't have things set up the most efficient way? I am still in the learning mode ... here is what I have ... three tables ... one Client Tbl, one ZIP code Tbl and one Premium Group tbl.

 

[cogent1]

Please post the query, not just the tables involved in it...

 

[LadyDiss]

Here is what I have:
SELECT tblClient.ID, tblClient.[First Name], tblClient.[Last Name], tblClient.Construction, tblClient.ZIP, tblZIP.City, tblZIP.[Protection Class], tblZIP.Territory, tblPremiumGroup.[Premium Group]
FROM (tblZIP INNER JOIN tblClient ON tblZIP.[ZIP Code] = tblClient.ZIP) INNER JOIN tblPremiumGroup ON (tblPremiumGroup.Construction = tblClient.Construction) AND (tblZIP.[Protection Class] = tblPremiumGroup.[Protection class]) AND (tblZIP.Territory = tblPremiumGroup.Territory)
WITH OWNERACCESS OPTION;

 

[Pat Hartman]

The "owner" of the query does not have update permission to one or both of the tables.

 

[LadyDiss]

Ok, so now how do I figure out which one and how to fix it? Thanks!

 

[Pat Hartman]

Open the security manager and determine who the owner of the query is and then determine if that person has appropriate permissions for the necessary tables.

 

[LadyDiss]

I created all the tables and should be able to edit any of them. I am the original creator of the database. Why would this be happening?

 

[Pat Hartman]

Sorry, I looked too quickly at the SQL and didn't notice the third table. The join is very unusual:

tblZip ==> tblClient ==> tblPremiumGroup
tblZip ============> tblPremiumGroup

This may be what is causing the query to be not updateable. Rather than joining tblZip to tblPremiumGroup, move what you have as join criteria for those two tables to the where clause -

SELECT tblClient.ID, tblClient.[First Name], tblClient.[Last Name], tblClient.Construction, tblClient.ZIP, tblZIP.City, tblZIP.[Protection Class], tblZIP.Territory, tblPremiumGroup.[Premium Group]
FROM (tblZIP INNER JOIN tblClient ON tblZIP.[ZIP Code] = tblClient.ZIP) INNER JOIN tblPremiumGroup ON (tblPremiumGroup.Construction = tblClient.Construction))
WHERE tblZIP.[Protection Class] = tblPremiumGroup.[Protection class] AND tblZIP.Territory = tblPremiumGroup.Territory
WITH OWNERACCESS OPTION;

 

[LadyDiss]

I am getting the Syntax error in FROM clause

 

[LadyDiss]

And the record set is still not updateable. Any ideas?

 

[Pat Hartman]

Looks like an extra right paren.

 

[LadyDiss]

I had caught the extra paren, however, it is still not updateable.