update query on lookup field produces errors

[smwabill]

I have an orders table and I have a product table

the orders table has a lookup or link to the products table. the fields I'm linked to in the lookup is productnumber and productname the number is masked
so only the name shows.

but, when I try to update the productname in an update-query showing the orders, it won't let me do it
unless I put in the product number, and I don't want to use the product number, I prefer using the name.

Thanks.

 

[lespaul]

do you mean that you have a lookup field in your table?

You should first read The Evils of Lookup Fields in Tables.

The problem is that the relationship is actually on the product number. Access is being "helpful" by displaying the product name in your orders table. You should be displaying this information in a form and not allowing direct access to the tables.

HTH

leslie

 

[smwabill]

Yes, I read "The Evils of Lookup Fields in Table". So, how do you an update query on the product name in the Orders Table or is that not possible?

Thanks for you quick response.

smwabill+

 

[lespaul]

I'm still not clear on what you are trying to accomplish. You are trying to update the orders table and you need to change a product that was ordered?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[smwabill]

yes,

for example

for some reason there was a certain product,
for whatever reason that was missing from the database.

150 orders from customer(JuiceRack) are missing apple juice from certain orders and I want to do an update query and fill in the missing info. And there are a few other customers with same problem.

I can do an update query on the number but would prefer not to start working with product nos. as it gets quite confusing and might get mixed up. For example apple juice might be productno. 19347849 and Orange Juice might be product 19347749, but it is far easier to use the names and there is no mixup.

 

[lespaul]

First off, if you want to add another record to an order, you will need an INSERT query (adds a new record) not an UPDATE query (changes existing record).

you will need all the information to be inserted:

Table: ORDERS
Fields: OrderNumber, CustomerNumber, ProductNumber, Quantity, any other fields that need to have information.

Then something like this may work:

INSERT INTO ORDERS (OrderNumber, CustomerNumber, ProductNumber, Quantity, any other fields that need to have information) VALUES (SELECT "OrderNumberHere", "CustomerNumberHere", (SELECT ProductNumber FROM Products WHERE ProductName = "Apple Juice"), 15 FROM Products)

 

[smwabill]

Thanks for your answer, but it is not a new record, just one of the fields is missing information.

Appreciate your continued help.

smwabill+

 

[lespaul]

Again, what you are seeing in the table is just Access being helpful. The relationship is actually between ProductNumber.

So, you want to update the ORDERS table. What does the table look like? What field do you want to add information too? What SQL have you already tried?

 

[smwabill]

just want to update missing product name--that's all.

just tried straight update query nothing else.

Thanks,

smwabill+

 

[smwabill]

Leslie,

Looking at part of your select statement

select productnumber from products where productname="Apple Juice"

is there a way to use this in doing an update query
could you write it out for me.

Thanks.
smwabill+

 

[lespaul]

UPDATE ORDERS SET ProductNumber = (SELECT ProductNumber from PRODUCTS WHERE ProductName = "Apple Juice") WHERE [b]SomeOtherCondition[/b]

You will need to complete the SomeOtherCondition or this will update ALL THE RECORDS!!! You should have an order number or something to reference. I usually write a SELECT query that returns all the records I want to change and then use the WHERE clause in the UPDATE query to make sure I only update the records I'm interested in.

HTH

leslie

 

[smwabill]

Leslie,

Thanks

the sql as you wrote it,
won't run, gives this error.

"Operation must use an updatable query"

maybe SET statement is problem, I put it just as you wrote it with parenthesis and all.

Thanks for your continued help
smwabill+

 

[lespaul]

what is the actual SQL you tried?

 

[smwabill]

Leslie,

UPDATE [ORDERS TBL] SET PRODNO = (SELECT PRODNO FROM
[PRODUCTS TBL] WHERE PRODNAME = "APPLE JUICE";

I took out SomeOtherCondition until this part works

Thanks
smwabill

 

[smwabill]

forgot to add parenthesis, but just in this corresp.

Leslie,

UPDATE [ORDERS TBL] SET PRODNO = (SELECT PRODNO FROM
[PRODUCTS TBL] WHERE PRODNAME = "APPLE JUICE");

I took out SomeOtherCondition until this part works

Thanks
smwabill

just added parenthesis, it was in my coding just forgot to add it here--Bill

 

[lespaul]

Does the subquery return what you are looking for?

SELECT PRODNO FROM
[PRODUCTS TBL] WHERE PRODNAME = "APPLE JUICE

 

[smwabill]

Yes it returns the number from apple juice

Bill+

 

[lespaul]

I have no clue why it's not working!!

Sorry!

Leslie

 

[smwabill]

Leslie,

Well, thanks for trying.

One last thing. It's funny that the select statement will work by itself and the update and set statement will work by themselves but the whole thing will not work together.
I'm really clueless as to why it keeps saying not updatable. I've even created a couple of basic table with almost nothing in them and it does the exact same thing.
I wonder if the relationship or join type have anything to do with it.

Well again thanks for trying and like my mother always says, "this problem once solved will be simple"

Bill+

 

[lespaul]

I think I found your solution if you still need it. Instead of using a subquery you need to use a DLOOKUP. But I don't know if you can reference a table in the DLOOKUP, maybe.....

UPDATE [ORDERS TBL] SET PRODNO = DLOOKUP(you need to find the parameters that would go here!)

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell