Query Formula Help

[Access_Headaches]

I've set up a database for a friend who owns a workout center. The only thing I have left is to set-up a formula in the query to find out the average calorie intake daily. (Find the Median)

I'm thinking something like:

Average_Cal_Intake: [End_Day_Calories]\X

My problem is "X", how do I make that number change as daily records get entered. Simply put, I'm looking for a formula to find the median that keeps up with records as they change.

 

[lcook1974]

What is "X"? is that the number of days?

 

[dkinley]

Just click on the the Totals (epsilon) in the QBE and in the Totals row use 'Average'.

-dK

 

[boblarson]

Just click on the the Totals (epsilon) in the QBE and in the Totals row

That is SIGMA, not Epsilon, by the way

 

[dkinley]

Hahaha ... aye it is! Epsilon was stuck in my head from strain tolerance ... or was that Freudian slip?

Apologies!
-dK

 

[boblarson]

Hahaha ... aye it is! Epsilon was stuck in my head from strain tolerance ... or was that Freudian slip?

Apologies!
-dK

No problem. I see a lot of people get tripped up by the fact that is LOOKS like an E (sort of). But I guess in your case it was just a matter of "matter over mind."

 

[Access_Headaches]

Thanks everyone, Yes "X" was total days.

 

[Access_Headaches]

It wont let me because the source is not from a table but from:


Query:
Daily_Cal_Total: ([morning_Calories]+[morning_Snack]+[Lunch]+[Afternoon_Snack]+[Dinner]+[Midnight_Snack]-[Workout_Cal_Burned]-[Workout_Cal_Burned_2])

(Daily_Cal_Total is what I'm trying to average)

Also, My form wont update after trying, giving this message: The Record set is not up-datable. I can however, update in the table just fine.

 

[boblarson]

It wont let me because the source is not from a table but from:


Query:
Daily_Cal_Total: ([morning_Calories]+[morning_Snack]+[Lunch]+[Afternoon_Snack]+[Dinner]+[Midnight_Snack]-[Workout_Cal_Burned]-[Workout_Cal_Burned_2])

It won't let you do WHAT?

(Daily_Cal_Total is what I'm trying to average)

Yes, and you will need a subquery to get the sum of all of the daily totals in order to get the average.

Also, My form wont update after trying, giving this message: The Record set is not up-datable. I can however, update in the table just fine.

Of course the query won't let you. You have aggregations going on. If you want to be able to update the data from the form then you can't base the form's recordset on a query that has aggregations in it.

 

[Access_Headaches]

I deleted the formula (attempt) from the form and main query BUT I left the SIGMA button highlighted.

Sub Query worked, Thanks!