Update a table from a series of queries

[hceonetman]

Using Access 2000 I have two tables that list empl. absences - Absences lists hours by date, Reasons lists a reason code, i.e. sick, vacation, etc. The relationship is many (absences) to one (reasons), that is a single reason may relate to an absence of more than one day.
I have a query (REASONS_NF) that totals sick time by employee ID as follows:

SELECT Reasons.ReasonCat, Reasons.PayID, Reasons.ReasonID
FROM Reasons
WHERE (((Reasons.ReasonCat)=&quot;Sick&quot AND ((Reasons.ReasonType)<>&quot;08-FamilyDeath&quot; Or (Reasons.ReasonType) Is Null));

This query works as intended. I then sum total hours absent with the query TAKEN_NF_SICK_HOURS_YTD:

SELECT Reasons_NF.PayID, Sum(Absences.Hours) AS SumOfHours
FROM Reasons_NF INNER JOIN Absences ON Reasons_NF.ReasonID = Absences.ReasonID
GROUP BY Reasons_NF.PayID;

The results of this query are also what I expect. I'd now like to update a field in a table with the total hours. I tried:

UPDATE Updt_BaseDate, [Taken_NF_Sick_Hours_YTD] SET Updt_BaseDate.Tot_Sick_Hours = [Taken NF_Sick Hours YTD]![SumOfHours];

The result is &quot;Operation must use an updateable query.&quot; It appears an Access query may not reference an aggregate from one query in a subsequent query. How do I get the total hours into the second query? I also tried not totaling the value in the middle query and using the domain function DSUM in the third query, but my syntax must have been off. It not only failed, it locked up Access.

Any help would be appreciated. I hope I've provided enough info.
HCEONETMAN

If today was a fish, I'd throw it back.

 

[Golom]

Your query is not updatable because of the cross-join
Updt_BaseDate, [Taken_NF_Sick_Hours_YTD]. Try an INNER JOIN like

UPDATE Updt_BaseDate INNER JOIN [Taken_NF_Sick_Hours_YTD]
       ON Updt_BaseDate.PayID = [Taken_NF_Sick_Hours_YTD].PayID 

SET Updt_BaseDate.Tot_Sick_Hours = [Taken NF_Sick Hours YTD]![SumOfHours];

 

[lespaul]

Looking at your update query, you seem to have selected two updates, one in the table and one in your query?

UPDATE Updt_BaseDate, [Taken_NF_Sick_Hours_YTD] SET Updt_BaseDate.Tot_Sick_Hours = [Taken NF_Sick Hours YTD]![SumOfHours];

The bolded section is your query name right?

First of all, once you add an aggregate function (like SUM or MAX) the result set becomes read only by default. So you couldn't update that query anyway!

perhaps if you change it to just update the table:

UPDATE Updt_BaseDate SET Updt_BaseDate.Tot_Sick_Hours = [Taken NF_Sick Hours YTD]![SumOfHours];


HTH


Leslie

 

[lespaul]

Well, I was ALMOST right!

Leslie

 

[hceonetman]

Golom, Lespaul,
Thanks for the quick response. However, I plugged your code in as a new query and still get the 'Operation must use an updateable query' msg from either one. The help screen repeats what Lespaul said - once I use aggregate functions in the middle query I can't access fields from it in a calling query. If I omit the sum function in the second query can I then simultaneously sum by PayID and update this value into the table in the third query. I know there must be a way short of running a do loop in VB. Or is there a way to ADD the value from the query to the existing value of the field. If I do this with all records, the result should be the same.

HCEONETMAN