Posted on 2005-03-03
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
... and so on
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
EnquiryID > Primrary Key, Autonumber for now
... and so on
Relationships: (all with update referential integrity)
tblPostcode.postcodefirst -> tblEnquiry.postcodefirst
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.
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