?
Solved

Update Query

Posted on 2005-03-03
43
Medium Priority
?
275 Views
Last Modified: 2008-02-01
Hi Guys,

We have Agents that look after particular areas (specified by their postcode).  When a customer phones to make an enquiry, they will be put on the database, their postcode written down, and then it will automatically store the Agent that corresponds to the Postcode.  Note:  A field is required rather than just a select query since there will need to be an override feature on the agent generated, and this will need to be stored.

Here's where I am:

I have 3 tables:

tblAgent:  (Probably not relevant to the question)
AgentID > Autonumber > Primrary Key
CompanyName
Title
FirstName
LastName
... and so on

tblPostcode:
PostcodeFirst > Primrary Key (note:  UK postcode, only first half)
Location > The location that the postcode points to
AgentID > Long Int > The agent who looks after that postcode

tblEnquiry:
EnquiryID > Primrary Key, Autonumber for now
Date
PostcodeFirst
PostcodeSecond
AgentID
... and so on

Relationships: (all with update referential integrity)
tblPostcode.postcodefirst -> tblEnquiry.postcodefirst
tblEnquiry.AgentID->tblAgent.AgentID
tblAgent.AgentID->tblPostcode.AgentID

Resulting in a triangle of relationships, which as far as my testing goes, works.

Here's what i've tried:
UPDATE tblEnquiry SET AgentID = (SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[Enter in a Postcode]) WHERE EnquiryID=[What record to put the Agent that's responsible for the given postcode];

When this works, a VB procedure will put values where the []'s are once a postcode is typed in.

My Problem:
I get this error message:  Operation must use an updatable query

Where the problem is:
(SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[Enter in a Postcode]), since if you replace that with 1 (for example), it will run correctly

I havent used a join query (so i tried nesting) simply because i've forgotton how to use them (I havent touched access or queries for about a year), however i'll be happy with receiving a SQL string, which i'll be able to examine later once i've refreshed myself on joins.

Thanks in Advance
Lee
0
Comment
Question by:pitmanromford
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 12
  • 9
43 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13450717
Rather than use a prompt in your query, set up a form for your data input. You can then use some code like this:

Dim AgentID As Long

AgentID=DLookUp("[AgentID]","tblPostcode","[PostcodeFirst='" & Me!txtPostcode & "'")
CurrentDb.Execute "UPDATE tblEnquiry SET AgentID=" & AgentID & " WHERE EnquiryID=" & Me!txtRecord
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13467665
CurrentDb.Execute "UPDATE tblEnquiry SET AgentID=" & AgentID & " WHERE EnquiryID=" & Me!txtRecord didnt work when inputting a new record on a form, I would imagine simply because UPDATE requires an already existing record, simple workaround:

Dim iAgentID As Long
iAgentID=DLookUp("[AgentID]","tblPostcode","[PostcodeFirst='" & Me!txtPostcode & "'")
AgentID = iAgentID

While this works (and keeps my client smiling), I would still like to know how to do this using a query (like i tried originally), and possibly an explanation of why mine didn't work.

Cheers
Lee
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13467685
How were you executing the original query? So far as I can see, it ought to be updatable. What version of Access are you using?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:pitmanromford
ID: 13472603
Clicking the Red !

2002

Any Ideas?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13472616
Hmm - I'm not sure then, as I've tried a similar query in a test database and it works. I thought you might be running the query from VBA. It's possible that the prompt strings you are providing are too long - does it work if you reduce their length?
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13482268
"Also note that I do not, under ANY circumstances, look at databases in relation to any question posted on EE." - just reading your profile

get same problem using:
 UPDATE tblEnquiry SET AgentID = (SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[PC])
WHERE EnquiryID=[RC];

I've explained in detail my table structure above, so if your test is made the same as that, I can put forward little suggestoin as to why it doesnt work.

If i strip the VBA code from the DB and zip it- would you accept it by email? - or is that a defo nono?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13482292
Sorry - the reason that I don't accept DBs is because I write database applications for a living, and it isn't fair on my paying clients if I look at your database for free (whereas they have to pay). I'll see if I can reproduce your problem, but the quick test that I did with two tables definitely allowed an update.

What about if you only remove one of the prompts and keep the other in?
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13484104
The problem lies in the nested SELECT, however on its own it runs fine.  My only guess can be that it's due to the way that the 3 tables are joined together (in the triangle fassion) - as that's the only thing i seem to be doing different from all my other databases i've done in the past

From Access help:  Operation must use an updatable query. (Error 3073)

Possible causes:

You attempted to run a query that tried to update a field that cannot be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship.

You tried to use the obsolete OpenQueryDef method on a query that is in a database opened for read-only access.

Cheers
Lee
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13484992
You say three tables - do you mean two, rather than three?
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13485225
tblAgent:  (Probably not relevant to the question)
AgentID > Autonumber > Primrary Key
CompanyName
Title
FirstName
LastName
... and so on

tblPostcode:
PostcodeFirst > Primrary Key (note:  UK postcode, only first half)
Location > The location that the postcode points to
AgentID > Long Int > The agent who looks after that postcode

tblEnquiry:
EnquiryID > Primrary Key, Autonumber for now
Date
PostcodeFirst
PostcodeSecond
AgentID
... and so on

:)

The problem may lie in the way they're linked, so I think you should try all 3
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13485378
Ah, I think I might have it - you can have more than one agent per postcode right? When you do, which AgentID do you want it to use?
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13485934
No, one postcode per agent, but each agent can "control" many areas (postcodes)

(The triangle of relationships is required since an override is necessessary)

Hope that makes sense
Lee
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13486615
Then I'm a little stumped - you can actually place the DLookUp in the query itself like this:

UPDATE tblEnquiry SET AgentID = DLookUp("[AgentID]","tblPostcode","[PostcodeFirst]='" & [Enter in a Postcode] & "'") WHERE EnquiryID=[What record to put the Agent that's responsible for the given postcode]

I think that would work, but it doesn't really answer why it doesn't work in the original statement in your database, it's just another workaround. The only thing I can think is that there is something in the relationships that is causing the query to be non-updateable.
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13486781
OK, here's a little more info, shows quite clearly where the problem is, but not why:

SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[Enter a Postcode];
-Works, and will always return only 1 value

UPDATE tblEnquiry SET AgentID = (SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[Enter a Postcode]) WHERE EnquiryID = [What Record to put the Agent that's responsible for the given postcode];
-Doesnt Work

UPDATE tblEnquiry SET AgentID = (1) WHERE EnquiryID = [What Record to put the Agent that's responsible for the given postcode];
-however Works

ehh???
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13486892
Does this work:

UPDATE tblEnquiry SET AgentID = (SELECT AgentID FROM tblPostcode WHERE PostcodeFirst=[Enter a Postcode]) WHERE EnquiryID = 1

?
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13487901
Same error :/ - have you tried making the 3-way table database? - perhaps there's a problem with the structure (however much i doubt)

Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13490788
Hi Lee,

Have been looking into the structure of your tables and I would advise to drop the relation:
tblEnquiry.AgentID->tblAgent.AgentID

Basically the AgentID in tblEnquiry is only to be used when it's the "not postcode" agent who's doing the enquiry and in other cases the field could even remain empty. A better name would probably have been "AlternateAgent" and the relation can be maintained easily by using a combo bound to the agent table, so without the referential constraint.

I would probably also use one postcode per row unless you use a Range and first and second must be read as Start and End.

When it still doesn't work drop it in my mail (hope you have no problem with that Shane) and I'll have a look. I'm of the lazy kind :-)

Nic;o)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13490824
No, go for it Nico, I've got no objections - I only don't look at databases as it's a personal viewpoint of mine. I used to do it before I realised it wasn't really fair on my clients - and if they found out they could get the same advice from me for free by coming onto EE, I'd be out of a job :)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13491356
Can understand, I'm not working as a database programmer for a living so my perspective is different.

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13494795
I understand where you're coming from nico, the reason this 3-way relationship was chosen, was to remove the problem of:

If you change what Agent looks after a given postcode, you dont want it to then change the agent for all previous enquiries.  Whereas if the data is calculated via a query, it would requery, looking at the new agent.

Would it help for you to have the database?

Any Ideas? (Of course you have! :)
Lee
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13494846
Sorry, forgot to mention about the postcodes

The beginning of the postcode i.e. RM1 - stands for the town romford, the second part of the postcode i.e. 2ES, just makes things more specific to a street address, and hence is not required for the query.

If I understand you correctly, I don't believe a postcode range can be done i.e. RM (1-15)?  Since two agents can handle different parts of a postcode area, possibly overlapping between many postcode areas.

Hope this helps
Lee
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13494983
I tried removing the relation and renaming the field to AlternativeAgentID - same problem :/
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13503781
Hmm, just zip it up and drop in the mail with this URL in the mailtext :-)

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13514860
Hmm, "solved" it by changing the design of the PostcodeFirst field to a combobox extracting not only the existing PostcodeFirst values, but also the AgentID.
Thus the AfterUpdate event can be used to set an empty AgentID to the value of the "linked" agent and no UPDATE statement is needed.

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13516878
Hi

Thanks for the "solve", but the problem with that is if the agent for a given postcode changes in the future, then historic enquiries will be automatically show the current agent rather than the agent who dealt with it originally.

This is the reason why initially i went for an update query - so the agent is stored, and hence cannot change automatically.  Is my thinking wrong on this, or am I using an update query in the wrong way?

Also, my next step is to do a more complex allocation of agent depending on what types of products they can handle which is why I would prefer to us a query, rather than many dLookups

Also as well as getting it to work I would like to understand how to write an update query with a nested select query
For example, could it be possible that you just can't tell it to update a value of a result of a select query? - syntax?

I cant really understand this, but it might help you clever people :)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconDeterminingWhenCanUpdateDataQueryS.asp

Hope you can help
Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13517468
It's only changed when there's no AgentID in the enquiry record, thus when it's the default on a new record it will be set, but it won't replace the value for old rows, unless the user would blankout the AgentID.

As long as the above nested select returns one row it should work when you substitute the needed paramaters in the WHERE clauses.

The agent depending products is outside the scope of this UPDATE problem and EE will require a different Q for that....

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13518078
Hi Nico,

No problems with posting another question with new points for the agent depending products !!!!! I'll do that later.

I don't quite understand what you were saying in your last posting, could you expand incase I am missing something vital

However, my original question was about why the Update query doesn't work.  Both suggestions that have been made work around that, but do not answer the fundimental problem.  Is there an answer to why the update query doesn't work?

If in any given enquiry we rely on the agent only being derived from the postcode, over a period of months there may be many agents who have dealt with a postcode, and we would be unable to identify which agent at the time dealt with the specific enquiry. So at any time a past enquiry would yield the current agent for the postcode rather than the agent who delt with it at the time.
 
Thanks
Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13518302
The query works when you supply a valid PostcodeFirst.

Use e.g. this:

    IF IsNull(DLookup("[AgentID]", "tblPostcode", "[PostcodeFirst]='" & Me.PostcodeFirst & "'"))
       MsgBox "There is No Agent that Looks After that Postcode"
    else
       CurrentDb.Execute "UPDATE tblEnquiry SET AgentID=" & DLookup("[AgentID]", "tblPostcode", "[PostcodeFirst]='" & Me.PostcodeFirst & "'") & " Where EnquiryID =" & Me.EnquiryID
    endif

Havin a combo will prevent the selection of a wrong PostcodeFirst :-)

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13518601
Hi Nico,

Thanks for the expansion of info which I will play with:

Any answer to the other part of my last posting....

"However, my original question was about why the Update query doesn't work.  Both suggestions that have been made work around that, but do not answer the fundimental problem.  Is there an answer to why the update query doesn't work?

If in any given enquiry we rely on the agent only being derived from the postcode, over a period of months there may be many agents who have dealt with a postcode, and we would be unable to identify which agent at the time dealt with the specific enquiry. So at any time a past enquiry would yield the current agent for the postcode rather than the agent who delt with it at the time"

I'm not wanting to get you angry as I value your assistance, and you hav given me so much help, but is there an answer?  Please!!! :)
(followed by humble bow)
Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13519666
I would probably use a logon form and register on every entered row the userID and the datetime the row has been inserted and on an updated row the last one that did the update and the datetime.
In general this "light" tracking is sufficient.
It would also eliminate the need for the UPDATE entirely and the AgentID can be made invisible.

Takes a lot more to get me angry <LOL>

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13917499
That's really confused me :-s

Do you mean to do a many-many with agents and postcodes, specifying how long the agent covered the area for, and comparing it to the date of the enquiry?

If I understand you correctly, that still wouldn't solve the problem with the exeptions.
Such as if an agent is on holiday, or we decide to choose another for whatever reason, there needs to be a way to overide (hence why I stored the AgentID value)

Just to summarise my current table structure (it's been a while since I replied)
One Agent can cover Many Postcode Areas
One Postcode Area can have many enquiries in it
One Agent can have Many Enquiries

So just thinking out loud:

Talking about real world relationships, The Agent IS tied to the Enquiry - no doubt.  And an Agent also always coveres a set of Postcode Areas.  However, USUALLY the agent used on the enquiry is the one that covers the postcode, but not always.  So is the problem within that relationship? - just a thought.

I understand the question has moved from originally talking about an update query to table structure, yet the solution to the Query, promoted the problem of the table structure - would this be more appropriate in another question?

Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13953162
Basic approach would be to fill the agentID handling an Enquiry by default from the Agent/Postcode relation (or leave it empty), but override this when the logged in agent is different as the default.
This the "Handling" agentid field in the enquiries is related to agents and the Postcode/Agent default relation is stored separately.

Nic;o)

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13954648
Then you would have to always fill in the agentID in the seperate field.  Since it must not be allowed that if agents (for example) swap postcodes, the database will then think that the other agent handled the enquiry, when in fact the first one did.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13958358
> Then you would have to always fill in the agentID in the seperate field.
Yes, there needs to be a field in the enquiry to record this. When you have a logon form you can do this "hidden" by storing the logged in user and place his/her agentID in the field "AgentIDhandledEnquiry".

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13960198
Hi Nico

I'm not sure what you mean by logon form (you first mentioned it in your reply 03/11/05.  Can you explain in a little more detail please.

Is what you're saying to do almost what i had before, but drop the relationship, and do a comparison?

Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13963662
I use in general a logon form for my users to get access to the application. That's one way to get also the users AgentID.
There's however also the possibility when your users connect to windows using a userid and password to use the Environ function to get their ID. That ID can be used to get their AgentID (when different from their username) when you store the userID (The result of the Environ("username") function) in the user/agent table.

Confused now :-)

The relationship between the AgentID from tblAgent and the AgentID in the table "Enquiry" can be modelled in the database as each AgentID needs to exist, just the filling can be done from code.

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 13966910
Hi Nico,

I think I may have misled or confused you.

Each of our 30 agents works in different areas of the country.

We collect enquiries for them at head office.  Head office enters the enquiry onto the main database, and then forwards the enquiry to the relevant agent.  The agents ID is allocated by us at head office to ensure that every Agent is unique.

Normally, the Database works out which agent the enquiry should be sent to based upon the postcode table, (say 80% of the time) however, there are occasions when we choose to override that automatic selection which may be during agents holidays, when an agent has not paid their bill or because for some reason we want another agent to handle the enquiry.

We have to make sure that if we access the enquiry again on the database to update the information on that enquiry that it does not re-calculate the agent that was originally given - which is why i placed and AgentID field in the Enquiry table, making the "circular reference" which I understand is bad database design and will give problems with making queries work properly.

The next enquiry that comes in may be passed to them on the basis of the automatic calculation in the postcode table.

As of such the agents do not have direct access into the database, we send out electronic or paper enquiries for them to follow up.

I struggle to understand how the light tracking you speak of can handle that.  Am I missing something fundamental or just being thick? :-)

Lee
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 14134632
While my question was not fully answered, and it is dissapointing that it has just been left, I have valued the help given, which will no doubt be of use in later projects.  And hence do not want this simply deleted.

My chosen solution was to Drop the Postcode-Agent relationship, and just place safeguards in through code, and this seems to be working fine (database has been in use for about a month, and so far has performed everything expected of with to perfection, with further developments on plan.

I have assumed, that performing an Update Query of the syntax:

UPDATE table SET field = (**Query Result of one field**) WHERE condition;

Is simply not possible, since as far as access is concerned, that sub-query could result in more than one field, and would then produce problems.  (I dont know, this is just an assumption).

I've also chosen to use ADO instead of a dlookup, for spead, flexibility, and less error handeling to do. (dlookup flags an error everytime it cant find something - not the desired solution, but put me on the right track).

I hope to hear a response within 3 days, so that a better grade can be given
Lee
0
 
LVL 54

Expert Comment

by:nico5038
ID: 14138047
Sorry Lee, lost track of this one and got very busy at work.
As you found a workaround I'm fine when you ask for a delete & refund.

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 14146088
No problems, we all get busy!!

Is the assumption regarding the syntax in my last post correct?  Since my question was related to a SQL statement, if it's simply not possible, than the help i've received deserves a B.

Note:  Dont just say it's not possible to get the points :D

Lee
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 1500 total points
ID: 14149421
That's indeed not possible, Access will see it as a non updatable query :-(

Nic;o)
0
 
LVL 2

Author Comment

by:pitmanromford
ID: 14151446
:( - Ah Well

Cheers
Lee
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question