UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> How to do an UPDATE query with sums...    
 
   
dibblethewrecker
post Aug 30 2006, 09:01 AM
Post #1

New Member
Posts: 13
I have the following two queries, both return a sum:

days_remaining

CODE
SELECT placements.year_of_placement AS year_a, Sum(placements.days_remaining) AS days_a
FROM placements
GROUP BY placements.year_of_placement
HAVING (((Right([placements]![year_of_placement],2))=Right([forms]![DIS Calculation]![dis_year],2)));


days_to_FY_end

CODE
SELECT placements.year_of_placement AS year_b, Sum(placements.days_to_end_of_FY) AS days_b
FROM placements
GROUP BY placements.year_of_placement
HAVING (((Left([placements]![year_of_placement],2))=Right([forms]![DIS Calculation]![dis_year],2)));


Then I have an update query which is supposed to place the results into a table.

DIS_sum
CODE
UPDATE days_remaining, days_to_FY_end, dis_info SET dis_info.days_remaining_sum = days_remaining!days_a,
dis_info.days_from_sum = days_to_FY_end!days_b, dis_info.total_days = (days_to_FY_end!days_b)+(days_remaining!days_a)
WHERE (((dis_info.dis_year)=[Forms]![DIS Calculation]![dis_year]));

Due to the restrictions of Access I can't do this. I'm pretty sure it can be re-written using dsum but I'm really not sure how.
Go to the top of the page
 
+
ScottGem
post Aug 30 2006, 09:40 AM
Post #2

UtterAccess VIP / UA Clown
Posts: 25,041
From: LI, NY
Why are you doing this? As a general rule we do NOT store calculated values. You have the queries to get those values, no need to store them.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 03:03 AM