Is a query with a field subquery updatable?

  • Thread starter Thread starter Winnetou
  • Start date Start date
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
 
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)
 
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
 
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...
 
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.
 
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.
 
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)
 
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 >
 
Back
Top