Update Query

[Anna]

Hi:
There are two tables in my database one is tbl_PPVResearch and other is
tbl_Events. Both tables have same field TicketNum. tbl_Events has
duplicate TicketNum.

tbl_Events
----------
TicketNum Number
PPVVOD_Amt Currency

TicketNum PPVVOD_Amt
241
241 $8.95
241 $675.20
299 $9.95
319
319 $18.85
321 $179.00
323 $4.95
323 $8.95
323 $29.85

tbl_PPVResearch
------------------------
TicketNum AutoNumber
Status Text
AmountofPPVDispute Currency

Required Update in tbl_PPVResearch
-------------------------------------------------
TicketNum AmountofPPVDispute
241 $684.15
299 $9.95
319 $18.85
321 $179.00
323 $43.75

I need to update those records in tbl_PPVResearch which are in
tbl_Events (Both have same field TicketNum). The update will be
tbl_PPVResearch.AmountofPPVDispute from tbl_Events.PPVVOD_Amt and
tbl_PPVResearch.Status="Pending" and
tbl_PPVResearch.AmountofPPVDispute not zero.

I try it but it gives error
You tried to execute a query that does not include the specific
expression 'AmountofPPVDispute' as part of an aggregate function.

Update tbl_PPVResearch, tbl_Events
SET tbl_PPVResearch.AmountofPPVDispute = sum(tbl_Events.PPVVOD_Amt)
WHERE tbl_PPVResearch.TicketNum = tbl_Events.TicketNum
AND tbl_PPVResearch.Status = "Pending"
AND tbl_PPVResearch.AmountofPPVDispute <> 0

 

[strive4peace]

Hi Anna,

try this:

Update tbl_PPVResearch
SET AmountofPPVDispute =
nz(dSum("PPVVOD_Amt","tbl_Events", "TicketNum =" & [TicketNum]),0)
WHERE Status = "Pending"
AND AmountofPPVDispute <> 0

if TicketNum is text, you will need to do this:
"TicketNum ='" & [TicketNum] & "'"



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*