UPDATE QUERY - Aggregate Function Error; Totals Option Disabled

[bs0d]

I'm trying to update a field in a properties table to reflect the minimum date found in a daily readings table where the property id's match.

Can someone look at my SQL and let me know where I'm going wrong with this?

Thanks!

UPDATE tblDailyReadings INNER JOIN tblProperties ON tblDailyReadings.item_KEY = tblProperties.WH_IDX SET tblProperties.WH_Custom4 = Min([tblDailyReadings].[ReadingDate])
WHERE (((tblProperties.WH_IDX)=[tblDailyReadings].[item_KEY]));

 

[bs0d]

I created a make-table query to build a list of the min(readingDates) and item_key's.
Then ran the update query using the dates in the make-table.... this worked.

Still interested if it can be done as I attempted above, or if someway you need to have a nested query. if so, I'd like to see how that may look.

Thanks,

 

[Joe4]

Why are you storing the calculation in a Table at all?
It is usually a violation of the Rules of Data Normalization to store values which can simply be calculated from other values (which can lead to data integrity problems). Calculations are usually done at the Query level in a calculated field.

Note, that there are some exceptions to this rule (i.e. if the data used in the calculations is purged after being used).

 

[bs0d]

Why are you storing the calculation in a Table at all?
It is usually a violation of the Rules of Data Normalization to store values which can simply be calculated from other values (which can lead to data integrity problems). Calculations are usually done at the Query level in a calculated field.

Note, that there are some exceptions to this rule (i.e. if the data used in the calculations is purged after being used).

This may also be an exception, as the first date of an item will never change. Once the first date is established, it's etched in stone.

I may not need to store it ... I don't consider myself a query guru so any feedback is appreciated. But it's easier for me to run queries off the values if they're in a field, that to run a query that could be 3 nested queries if that's even possible. I also suspect that would take longer for the query to execute.