Query dates to calculate Monthly

[dab1477]

Thanks in advance for your assist.

How do I get a query to pull daily records from a master
table and Calculate (not sum) various fields as a weekly
or monthly format.

My master table collects data from a form and holds a
series of calculations within the master table

Ex. Date Item Uptime Efficiency FPY OEE

The last four fields are calculated from data within the
form and stored within these fields. I want to query the
dates over a week and month and recalculate the data for
the Uptime, efficiency, fpy, and oee fields for this
period,

I've tried the "BETWEEN AND" function in the criteria.
Doing so gives me a series of dates. I want to show as a
week or month.

I've tried moving the same query into a report. The
report only sums the fields. I need to calculate based
upon other data in the master table.

Hope I've been clear and not rambling.

Thank you

 

[Michel Walsh]

Hi,


You can use a total query (use the summation button on the toolbar, a
new line, total, appear in the grid), but all the data that you do not group
onto, GROUP BY, should be aggregated (SUM, MIN, MAX, AVG, ... ) or be an
arithmetic expression using aggregated result ( COUNT(x) * SUM( x) -
SUM( x ^2 ) as example ). You can group on expression, like

GROUP BY DatePart( "ww", myDateField)



Hoping it may help,
Vanderghast, Access MVP