Unable to edit fields in a select query

[Guest]

I have the following select query:

SELECT Agents.*, ZipCodes.PrintOpt
FROM Agents INNER JOIN ZipCodes ON Agents.Field11 Like ZipCodes.Zip
WHERE (((ZipCodes.PrintOpt)="Review & Print" And Not
(Agents.Reviewed)="Skip") And Not (Agents.Reviewed)="Reviewed");

Works just fine, no issue there. However, when I run the query which
displays in datasheet view, I am unable to edit any of the fields. If I try
to type in a field Access beeps at me.

From SQL View I right-click on the title bar and select properties. There is
a property called Record Locks which is set to "No Locks" just like any
"normal" query I've ever created.

Can someone explain why I can't edit fields in this query? I created the
query specifically so I can edit the fields from the datasheet view.

 

[Guest]

You have create a recordset that is not updatable. It may take some
experimenting, but try this first:

SELECT Agents.* FROM Agents INNER JOIN ZipCodes ON Agents.Field11 Like
ZipCodes.Zip
WHERE (((ZipCodes.PrintOpt)="Review & Print" And Not
(Agents.Reviewed)="Skip") And Not (Agents.Reviewed)="Reviewed");

 

[John Spencer]

Do you have a primary key in both tables?

Why are you using LIKE in the join clause and not equal?

Have you included both primary keys in the query?

Guessing that ZIP is primary key in ZipCodes, I would try the following

SELECT Agents.*, ZipCodes.PrintOpt, ZipCodes.Zip
FROM Agents INNER JOIN ZipCodes ON
Agents.Field11 = ZipCodes.Zip
WHERE (((ZipCodes.PrintOpt)="Review & Print"
And (Agents.Reviewed)<>"Skip")
And (Agents.Reviewed)<>"Reviewed");

Check the help Topic
When Can I Update from a Query
For an expanded list of when a query is updatable and when it is (or may)
not be.

 

[Guest]

Do you have a primary key in both tables?

Yes, Zip in ZipCodes and LicNo in Agents

Why are you using LIKE in the join clause and not equal?

The ZipCodes table contains a list of zip codes like this:
34102*
34103*
etc.
The Zip field in the Agents table can have a zip code like "34102-1010" and
I need to match the first 5. The Agents table comes from an external
database, I cannot change its format to break the zip code field into two
parts.

I discovered that changing "Like" to "=" makes it editable. That doesn't
make any sense. What difference would that make? I can't obviously leave it
as "=", it won't query the correct records.

 

[John Spencer]

First of all, using like without any wildcards will give you the exact same
results as using the equal. That said, you can try using nested queries -
see the example below

QueryOne - Save as AgentsMod:
SELECT Agents.*, Left(Agents.Field11,5) as MatchOn
FROM Agents
WHERE Agents.Reviewed NOT IN ("Skip","Reviewed")

Now using the AgentsMod query in place of the Agents table build a second
query.
SELECT AgentsMod.*, ZipCodes.PrintOpt, ZipCodes.Zip
FROM AgentsMod INNER JOIN ZipCodes ON
AgentsMod.MatchOn = ZipCodes.Zip
WHERE ZipCodes.PrintOpt="Review & Print"

Another option might be to use a subquery (if you don't need PrintOpt
displayed)
SELECT Agents.*
FROM Agents
WHERE Left(Agents.Zip,5) in
(SELECT Zip
FROM ZipCodes
WHERE ZipCodes.PrintOpt="Review & Print")
And Agents.Reviewed<>"Skip"
And Agents.Reviewed<>"Reviewed"

 

[Guest]

That's awesome, thank you!

One of these days I suppose I'll actually learn SQL - yuk!