Is a query with a field subquery updatable?

W

Winnetou

To speed things up, I would like to replace a DLookup expression in a
query with a field subquery. The field is defined as follows Price:
(Select ...) where the expression in brackets refers to a price
calculation query. Previously, the field was set to DLookup(...) with
the same Select ... reference query.

I am able to correctly pull out the Price value with the field
subquery, but the source query has now become un-updatable. With the
DLookup approach, the query remains updatable. Is there a work-around?

Thanks for any suggestions.

Mark
 
J

John Viescas

Hard to guess without seeing the SQL from your query. A simple Select
subquery should not affect updatability.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
W

Winnetou

John Viescas said:
Hard to guess without seeing the SQL from your query. A simple Select
subquery should not affect updatability.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
news:[email protected]...
Click to expand...

John, here we go:

1. Non-updatable qryPrice SQL with field subquery (Note: I used table
aliases in the field subquery):

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit, (SELECT
CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS
IngredDetailsPrice
FROM (IngredDetailsTable AS IngredDetailsTableA INNER JOIN UnitTable
ON IngredDetailsTableA.IngredDetailsUnit = UnitTable.UnitBase) INNER
JOIN IngredTable AS IngredTableA ON (UnitTable.Unit =
IngredTableA.IngredMCUnit) AND (IngredDetailsTableA.IngredID =
IngredTableA.IngredID)
WHERE (((IngredDetailsTableA.IngredDetailsID)=(IngredDetailsTable.IngredDetailsID))))
AS Price
FROM IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID;

2. Updatable qryPrice SQL with DLookup:

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQtyTxt,
IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit,
CSng(DLookUp("[IngredDetailsPrice]","qryUnitCnvrt","[IngredDetailsID]
= " & [IngredDetailsID])) AS IngredDetailsPrice
FROM IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID;

where the qryUnitCnvrt SQL is:

SELECT CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS
IngredDetailsPrice, IngredDetailsTable.IngredDetailsID
FROM (IngredDetailsTable INNER JOIN UnitTable ON
IngredDetailsTable.IngredDetailsUnit = UnitTable.UnitBase) INNER JOIN
IngredTable ON (IngredTable.IngredID = IngredDetailsTable.IngredID)
AND (UnitTable.Unit = IngredTable.IngredMCUnit);

Thanks for your help.

Mark
 
J

John Viescas

Well, the compiler is "optimizing" the subquery and restating it something
like:

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit,
CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS Price
FROM (IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID) INNER JOIN UnitTable ON (UnitTable.Unit =
IngredTable.IngredMCUnit);

... and the result is not updatable because you end up with UnitTable M <-- 1
IngredTable 1 --> M IngredDetailsTable.

It ignores the DLookup as not optimizable, so that one is updatable.

You could try including qryUnitConvert in the FROM clause and joining that
to IngredDetailsTable instead of IngredTable. Or, rethink your table
relationships...

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Winnetou said:
"John Viescas" <[email protected]> wrote in message
Hard to guess without seeing the SQL from your query. A simple Select
subquery should not affect updatability.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
news:[email protected]...
Click to expand...

John, here we go:

1. Non-updatable qryPrice SQL with field subquery (Note: I used table
aliases in the field subquery):

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit, (SELECT
CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS
IngredDetailsPrice
FROM (IngredDetailsTable AS IngredDetailsTableA INNER JOIN UnitTable
ON IngredDetailsTableA.IngredDetailsUnit = UnitTable.UnitBase) INNER
JOIN IngredTable AS IngredTableA ON (UnitTable.Unit =
IngredTableA.IngredMCUnit) AND (IngredDetailsTableA.IngredID =
IngredTableA.IngredID)
WHERE (((IngredDetailsTableA.IngredDetailsID)=(IngredDetailsTable.IngredDetailsID)
)))
AS Price
FROM IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID;

2. Updatable qryPrice SQL with DLookup:

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQtyTxt,
IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit,
CSng(DLookUp("[IngredDetailsPrice]","qryUnitCnvrt","[IngredDetailsID]
= " & [IngredDetailsID])) AS IngredDetailsPrice
FROM IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID;

where the qryUnitCnvrt SQL is:

SELECT CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS
IngredDetailsPrice, IngredDetailsTable.IngredDetailsID
FROM (IngredDetailsTable INNER JOIN UnitTable ON
IngredDetailsTable.IngredDetailsUnit = UnitTable.UnitBase) INNER JOIN
IngredTable ON (IngredTable.IngredID = IngredDetailsTable.IngredID)
AND (UnitTable.Unit = IngredTable.IngredMCUnit);

Thanks for your help.

Mark
Click to expand...
 
W

Winnetou

John,

Thanks for the reply. I'll give it a try otherwise I'll stick to the
DLookup version.

As a matter of principle without going too much into arcane
intricacies, is it fair to assume that once you use a non-updatable
query in an updatable query, the query becomes non-updatable?

Mark

PS: Years ago, I bought your book "Running Microsoft Access 97".
Well-written (although somewhat too long :)), this is really what got
me started in Access.
 
K

Ken Snell

John Viescas said:
Well, the compiler is "optimizing" the subquery and restating it something
like:

SELECT IngredDetailsTable.MenuItemID,
IngredDetailsTable.IngredDetailsID, IngredDetailsTable.IngredID,
IngredTable.IngredDescription, IngredTable.IngredPUPrice,
IngredTable.IngredMCQty, [IngredPUPrice]/[IngredMCQty] AS UnitPrice,
IngredTable.IngredMCUnit, IngredDetailsTable.IngredDetailsQty,
IngredDetailsTable.IngredDetailsUnit,
CSng([IngredDetailsQty]*[IngredMCCost]/[UnitScale]) AS Price
FROM (IngredTable INNER JOIN IngredDetailsTable ON IngredTable.IngredID
= IngredDetailsTable.IngredID) INNER JOIN UnitTable ON (UnitTable.Unit =
IngredTable.IngredMCUnit);

.. and the result is not updatable because you end up with UnitTable M <-- 1
IngredTable 1 --> M IngredDetailsTable.

It ignores the DLookup as not optimizable, so that one is updatable.

You could try including qryUnitConvert in the FROM clause and joining that
to IngredDetailsTable instead of IngredTable. Or, rethink your table
relationships...
Click to expand...

Hmmmm.....John, your solution looks very similar to the one I posted for
Winnetou in m.p.access. < g >

Looks as if both of us have been answering the same question in two
different newsgroups. Winnetou, if you feel it's appropriate to post the
same question to more than one newsgroup, use "multiposting" (list all those
newsgroups in the To line, separate by commas). That way, repliers can see
all answers to the question regardless of which newsgroup the replier uses.
That way, John and I both wouldn't have been spending redundant time
answering the same question without knowledge of what the other was doing.
Thanks.
 
J

John Viescas

If the "non updatable" query is a Totals query, absolutely yes. Otherwise,
if Access can figure out that the query is on the "one" side of a many-one
Join, then it still might be updatable. Your mileage may vary. <s>

Hey, you think "Running Access 97" was too long, "Inside Out Access 2003" is
*50%* longer. What can I say, it's a complex product.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
K

Ken Snell

John Viescas said:
Hey, you think "Running Access 97" was too long, "Inside Out Access 2003" is
*50%* longer. What can I say, it's a complex product.
Click to expand...


And worth every page! < s >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I Need a subquery to replace Dlookup 7
Referring to fields in a subquery 1
Creating a FAST updatable query 5
update query on a JOIN Table with a query Not Work 1
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
Cannot edit field in a joined table-subquery query 10
updatable query 1
Query not updatable - advise on how to change it 4

Top