UtterAccess Discussion Forums > How to do an UPDATE query with sums...

Full Version: How to do an UPDATE query with sums...

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

dibblethewrecker

Aug 30 2006, 09:01 AM

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.

ScottGem

Aug 30 2006, 09:40 AM

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.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.