Access Sumifs - Update Query

[polishfc]

Hi All,

I’m looking to transitions updates done to a database from excel to access. The bulk of the database consists of sumifs that I was hoping could be mimicked in Access, but running into an issue (i.e. probably don’t know how to do it properly)

In the database file (Hours File) I have a column for location code, period end date, and budgeted hours budgeted. The budgeted hours are derived from a budget file that break down the hours by person, location codes, and period end date. What I am looking for access to do is to update budgeted hours for each location and each period end date.

The sumifs that I am trying to replicate in Access can be found in column C and the data would be update in [Hours File]![Hours Budget] column.

Appreciate all the help in advance.

[CJ_London]

your query would be something like

SELECT tblHours.LocationID,tblHours.PeriodEndDate, Sum(tblBudget.Hours) as HoursBudget
FROM tblHours INNER JOIN tblBudgets ON tblHours.LocationID=tblBudget.LocationID AND  tblHours.PeriodEndDate=tblBudget.PeriodEndDate
GROUP BY tblHours.LocationID, tblHours.PeriodEndDate

edit: note that access is not like excel, one significant difference being you do not store calculated data (such as this) in a table, you calculate it as and when required.