Hey folks,
I'm having a problem getting my update to actually work properly. This
is running on a WinXP box (server is on Win2k Server) with MSAccess for
my database.
The problem is when I output my query result to the browser, it gives me
what I expect. When I try to put it into the database table via an
Update query, for any record that has multiple entries (eg. there may be
more than one record for an account number, but each has a separate
episode date), if there were no charges for the supplies it is adding up
all the supply charges for that account and entering it instead of
leaving it at $0.00. My code is below. What am I missing or doing wrong?
Judith
<!--- Then create query to basically add up all the charges for each
episode. Then, update the PPSEpisodeTotals table with the correct supply
charges PER EPISODE!!! --->
<cfquery name="getSupplies" datasource="PPSData">
SELECT tblPPSEpisodeTotals.AccountID,
tblSupplyCharges.EpisodeDate,
Sum(tblSupplyCharges.TotalCharge) AS SuppliesCharged
FROM tblPPSEpisodeTotals INNER JOIN tblSupplycharges ON
(tblPPSEpisodeTotals.AccountID =
tblSupplyCharges.Account) AND
(tblPPSEpisodeTotals.EpisodeStart =
tblSupplyCharges.EpisodeDate)
WHERE (tblPPSEpisodeTotals.EpisodeStart =
tblSupplyCharges.EpisodeDate)
GROUP BY tblPPSEpisodeTotals.AccountID,
tblSupplyCharges.EpisodeDate
</cfquery>
<cfoutput query="getSupplies">
<!--- Test output to make sure that I'm getting the right amounts for
each episode. --->
<!--- #AccountID# #DateFormat(EpisodeDate, "mm/dd/yyyy")#
#SuppliesCharged#<br /> --->
<!--- Now perform the update. --->
<cfquery name="updateSupplycharges" datasource="PPSData">
UPDATE tblPPSEpisodeTotals
SET SupplyCharges = #SuppliesCharged#
WHERE (EpisodeStart = #CreateODBCDate(EpisodeDate)#)
AND AccountID = '#AccountID#'
</cfquery>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:648
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
