Query wont update

[BoxRH]

I am trying to update a field (CustID)in a table (customers). There are no primary keys defined. I have a lookup table called lookup, which contains two fields, group and prefix. The prefix is the first letter of customers lastname in the table customers. The group is a number code for the prefix, a, b, c =1, d, e, f= 2 and so on up z which is 9

What Iam trying to do is to take the first letter of the surname and assign a customer ID. So if for example a customers surname is Adam, the customer ID is 1001, and if I add a second customer whos surename begins with A, the ID would be 1002. For names begining with B, ID would read 2001, 2002 and so on.

When I try to update, I get a message saying that 'operation must use an updateable query'.

Here is the quey that I am trying to use.

UPDATE Customers SET Customers.CustID = 1+(select max(customers.[CustID])
from table1, lookup
where left(customers.[last name],1)=lookup.[prefix]
and lookup.[group] =(abs(customers.[custID]/1000)))
WHERE Customers.[CustID] In (Null," ");

What am I doing wrong?

 

[Pat Hartman]

You need to define a unique primary key.

PS, I guess none of us persuaded you to change your mind about this. I hope you do realize that you are severely limiting the number of customers you can add to this table.

[This message has been edited by Pat Hartman (edited 11-01-2001).]

 

[BoxRH]

Even with the primary key defined, I still get the same message. Thanks again for your help.

 

[BoxRH]

Any Ideas?