Basic Update Query

[Hyder]

I have the following two tables Invoice-InvoiceProduct and there's a many to
one relation between them, I use the following query to work out the total
base on all the InvoiceProducts that share the same InvoiceNumber but it
returns empty
anythoughts?

UPDATE Invoice
SET Invoice.Total = Sum(InvoiceProduct.SubTotal)
WHERE (((Invoice.InvoiceNumber)>0));

InvoiceProduct
=========
InvoiceNumber ItmeID Quantity Price SubTotal
10 1 2 $100 $200 <- this
update query works fine
11 2 5 $8 $40
11 5 4 $15 $60
-----------------------------------------------------------
Invoice
=====
InvoiceNumber Total
10 ?
11 ?

 

[Ken Snell \(MVP\)]

Try this:

UPDATE Invoice
SET Invoice.Total =
(SELECT Sum(InvoiceProduct.SubTotal) AS SIP
FROM InvoiceProduct
WHERE InvoiceProduct.InvoiceNumber =
Invoice.InvoiceNumber)
WHERE Invoice.InvoiceNumber>0;

Your original query did not provide any data from the InvoiceProduct table
to the query, so no data could be updated.

 

[Hyder]

Thanks Ken
I've tried the mentioned query but i still get a blank result although the
number of empty cells is correct.

when i run it throws an error: operation must use an updateable query
does SQL know the correspondng Invoice.InvoiceNumber when it Select/reads
the InvoiceProduct Table?

 

[John W. Vinson]

Thanks Ken
I've tried the mentioned query but i still get a blank result although the
number of empty cells is correct.

when i run it throws an error: operation must use an updateable query
does SQL know the correspondng Invoice.InvoiceNumber when it Select/reads
the InvoiceProduct Table?

No Totals query, nor any query including a Totals query, is updateable - even
when, as in this case, it logically ought to be.

You may want to consider using the DSum() function to sum the values instead:

UPDATE Invoice
SET Invoice.Total = DSum("[Subtotal]", "InvoiceProduct", "[InvoiceNumber] = "
& [InvoiceNumber]);


John W. Vinson [MVP]

 

[Ken Snell \(MVP\)]

Thanks Ken
I've tried the mentioned query but i still get a blank result although the
number of empty cells is correct.

when i run it throws an error: operation must use an updateable query
does SQL know the correspondng Invoice.InvoiceNumber when it Select/reads
the InvoiceProduct Table?

No Totals query, nor any query including a Totals query, is updateable -
even
when, as in this case, it logically ought to be.

You may want to consider using the DSum() function to sum the values
instead:

UPDATE Invoice
SET Invoice.Total = DSum("[Subtotal]", "InvoiceProduct", "[InvoiceNumber]
= "
& [InvoiceNumber]);


John W. Vinson [MVP]

Thanks, John. I forgot about that when I wrote that SQL statement.

 

[Hyder]

I appreciate your help, but i still get blank result, All fields: SubTotal,
Total & Price are Currency :: type and I'm amazed why i get empty response!

can i use another method, maybe splitting the query;

Thanks Ken
I've tried the mentioned query but i still get a blank result although the
number of empty cells is correct.

when i run it throws an error: operation must use an updateable query
does SQL know the correspondng Invoice.InvoiceNumber when it Select/reads
the InvoiceProduct Table?

:

Try this:

UPDATE Invoice
SET Invoice.Total =
(SELECT Sum(InvoiceProduct.SubTotal) AS SIP
FROM InvoiceProduct
WHERE InvoiceProduct.InvoiceNumber =
Invoice.InvoiceNumber)
WHERE Invoice.InvoiceNumber>0;

No Totals query, nor any query including a Totals query, is updateable -
even
when, as in this case, it logically ought to be.

You may want to consider using the DSum() function to sum the values
instead:

UPDATE Invoice
SET Invoice.Total = DSum("[Subtotal]", "InvoiceProduct", "[InvoiceNumber]
= "
& [InvoiceNumber]);


John W. Vinson [MVP]

Thanks, John. I forgot about that when I wrote that SQL statement.

 

[John Spencer]

The following might work for you.

UPDATE Invoice
SET Invoice.Total = DSum("SubTotal","InvoiceProduct","InvoiceNumber=" &
Chr(34) & Invoice.InvoiceNumber & Chr(34))
WHERE Invoice.InvoiceNumber>0

Make sure you
-- BACKUP your data before trying this
-- Run the query by using Query: Run from the menu or by pressing the
red exclamation button. Switching to datasheet view does not execute
the query, it only shows you what will be updated.
-- Be prepared for this to be slow with large amounts of data.

If it is too slow, you will need an auxiliary table to populate with new
records based on an aggregate (totals) query. Then you can update
Invoice table using values saved into the auxiliary table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I appreciate your help, but i still get blank result, All fields: SubTotal,
Total & Price are Currency :: type and I'm amazed why i get empty response!

can i use another method, maybe splitting the query;

On Fri, 30 Nov 2007 16:17:01 -0800, Hyder
<[email protected]>
wrote:

Thanks Ken
I've tried the mentioned query but i still get a blank result although the
number of empty cells is correct.

when i run it throws an error: operation must use an updateable query
does SQL know the correspondng Invoice.InvoiceNumber when it Select/reads
the InvoiceProduct Table?

:

Try this:

UPDATE Invoice
SET Invoice.Total =
(SELECT Sum(InvoiceProduct.SubTotal) AS SIP
FROM InvoiceProduct
WHERE InvoiceProduct.InvoiceNumber =
Invoice.InvoiceNumber)
WHERE Invoice.InvoiceNumber>0;
No Totals query, nor any query including a Totals query, is updateable -
even
when, as in this case, it logically ought to be.

You may want to consider using the DSum() function to sum the values
instead:

UPDATE Invoice
SET Invoice.Total = DSum("[Subtotal]", "InvoiceProduct", "[InvoiceNumber]
= "
& [InvoiceNumber]);


John W. Vinson [MVP]

Thanks, John. I forgot about that when I wrote that SQL statement.