A
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
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