Update query help

[Neal Ostrander]

I am just learning to use queries and the book I am learning from ask for an
update query to increase the list price of all products for 'supplier A' by
3.25. I wrote the following update query but it give a 'type mismatch in the
critera expresion' error.

UPDATE Products
SET [List Price] = [List Price] + 3.25
WHERE [Supplier IDs] = 'Supplier A'

So I thought I would try a select query to find how many products 'Supplier
A' has and the list price. This is the query I wrote.

SELECT [Supplier IDs], [List Price]
FROM Products
WHERE [Supplier IDs] = 'Supplier A'

This throws a 'multi-valued field [Supplier IDs] cannot be used in a WHERE
or HAVING clause' error. I have never delt with multi-valued fields before.
Is there a resource where I can learn about them.
And could I get an explination of why the update query doesn't work as
written.

Thanks in advance for any help.
Neal

 

[Allen Browne]

If SupplierID is a multi-valued field, you may be able to get the SELECT
query to work by adding Value to the field:
SELECT [Supplier IDs], [List Price]
FROM Products
WHERE [Supplier IDs].Value = 'Supplier A'

If that still doesn't work perhaps Supplier ID is actually a Number field,
with a combo that hides the number that is actually stored there.

Once you have solved that, you may find that the UPDATE still doesn't work
becuase the query is 'not updatable.'

Multi-valued fields (MVF) were introduced in Access 2007, and I don't know
any serious developers who use them. They really are a pain to work with. In
code you have to treat MVFs as a recordset within a recordset. There are
cases where they don't work to external databases (even ACCDBs), e.g. IN to
specify a file in the FROM clause. Their OldValue is not maintained
properly. There are problems with filters, and as you found, WHERE and
HAVING clauses don't work the way you are used to. And they won't upsize to
other kinds of databases.

IMHO, it would be a much better choice to create a related table for these
things instead of using a multi-valued field. Access does actually use a
super-hidden related table to manage the MVF. You are much better of
creating one you can see and manipulate like a normal table.

 

[Neal Ostrander]

Thanks Allen that was a big help. I'm fairly new to access but from what I've
learned so far it seems that Multi-valued fields go against the rules of
normalization.
Neal

If SupplierID is a multi-valued field, you may be able to get the SELECT
query to work by adding Value to the field:
SELECT [Supplier IDs], [List Price]
FROM Products
WHERE [Supplier IDs].Value = 'Supplier A'

If that still doesn't work perhaps Supplier ID is actually a Number field,
with a combo that hides the number that is actually stored there.

Once you have solved that, you may find that the UPDATE still doesn't work
becuase the query is 'not updatable.'

Multi-valued fields (MVF) were introduced in Access 2007, and I don't know
any serious developers who use them. They really are a pain to work with. In
code you have to treat MVFs as a recordset within a recordset. There are
cases where they don't work to external databases (even ACCDBs), e.g. IN to
specify a file in the FROM clause. Their OldValue is not maintained
properly. There are problems with filters, and as you found, WHERE and
HAVING clauses don't work the way you are used to. And they won't upsize to
other kinds of databases.

IMHO, it would be a much better choice to create a related table for these
things instead of using a multi-valued field. Access does actually use a
super-hidden related table to manage the MVF. You are much better of
creating one you can see and manipulate like a normal table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am just learning to use queries and the book I am learning from ask for
an
update query to increase the list price of all products for 'supplier A'
by
3.25. I wrote the following update query but it give a 'type mismatch in
the
critera expresion' error.

UPDATE Products
SET [List Price] = [List Price] + 3.25
WHERE [Supplier IDs] = 'Supplier A'

So I thought I would try a select query to find how many products
'Supplier
A' has and the list price. This is the query I wrote.

SELECT [Supplier IDs], [List Price]
FROM Products
WHERE [Supplier IDs] = 'Supplier A'

This throws a 'multi-valued field [Supplier IDs] cannot be used in a WHERE
or HAVING clause' error. I have never delt with multi-valued fields
before.
Is there a resource where I can learn about them.
And could I get an explination of why the update query doesn't work as
written.

Thanks in advance for any help.
Neal