By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Calculate Average of a field

P: 25
I have two tables, the first stores data related to observations visits for patients and the second stores data used to create an executive dashboard monthly. What I need to do is take one of the fields in the observations table and average it out and store it in the dashboard table for monthly and YTD reporting. Does anyone know how to calculate the average of a field in one table and store it in another?

I have the SQL that accurately calculates the average of the field, I just can't get it to store in the other table's field.

Expand|Select|Wrap|Line Numbers
  1. SELECT Avg(ObservationData.HrsIPAdm2OBSOrd) AS AvgOfHrsIPAdm2OBSOrd, ObservationData.ReportMonthYear
  2. FROM DashboardData INNER JOIN ObservationData ON DashboardData.DashReportMonthYear = ObservationData.ReportMonthYear
  3. GROUP BY ObservationData.ReportMonthYear
  4. HAVING (((ObservationData.ReportMonthYear)=[forms]![internalProcesses]![TempRMY]));
Jun 28 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,355
You shouldn't store it. That breaks the rules of normalization. You should just calculate it when needed.
Jun 28 '12 #2

Expert Mod 15k+
P: 31,411
See Database Normalisation and Table Structures for why that's important.
Jun 29 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.