Problem with Averages

[Guest]

Okay, here's the situation. I was getting no value on my expression for
averaging 52 separate fields. With the help of everyone here, I figured out
the problem. If one of the fields in the equation is null, it won't perform
the query.

This is the new problem. I am averaging attendance records, so, if a week
hasn't come yet, the value is "0". However, this affects the average
adversely. Is there a way to average based on the values that have been
collected?

Here's the equations I have:

sum1:
([wk1])+([wk2])+([wk3])+([wk4])+([wk5])+([wk6])+([wk7])+([wk8])+([wk9])+([wk10]+([wk11])+([wk12])+([wk13])+([wk14])+([wk15]))

average1: [sum1]/52

I guess I'm hoping there's a way for the database to figure out the number
to divide by based on the number of weeks that have value and not use the
"52".

Any thoughts??

Michaela

 

[Allen Browne]

The problem here is that you have a table that's set up like a spreadsheet
instead of like a relational database.

Whenever you see repeating columns like that, it always indicates that you
need a related table. For example, if these are salesman's figures for the
week, you need to have a Salesman table with just the name and bio details,
and then a second table with fields like this:
SalesmanID relates back to the Salesman table.
WeekNo a number (1 to 53)
Amount the dollar value you want to store.

At the end of the year, each salesman will then have 52 *records* (not
columns) in this table.

With that structure, you can easily sum, count, and average the data
correctly. Just depress the Total button (on the toolbar on query design),
and it falls together from there.

(In actually, it would be better to have a date field with the first date of
each week instead of a WeekNo field, because the structure will then keep
working for years.)

 

[Guest]

I'm with you so far. Next question (I've done the separate table and set the
relationship): How can I set up my form so that when each person is entering
each of the 52 "records" they don't have to enter their name and ministry
each time?

Basically, I need it to look something like this:

John Smith Youth Ministry

3/10/05 52
3/17/05 66
3/24/05 72
Average 63

The problem here is that you have a table that's set up like a spreadsheet
instead of like a relational database.

Whenever you see repeating columns like that, it always indicates that you
need a related table. For example, if these are salesman's figures for the
week, you need to have a Salesman table with just the name and bio details,
and then a second table with fields like this:
SalesmanID relates back to the Salesman table.
WeekNo a number (1 to 53)
Amount the dollar value you want to store.

At the end of the year, each salesman will then have 52 *records* (not
columns) in this table.

With that structure, you can easily sum, count, and average the data
correctly. Just depress the Total button (on the toolbar on query design),
and it falls together from there.

(In actually, it would be better to have a date field with the first date of
each week instead of a WeekNo field, because the structure will then keep
working for years.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Okay, here's the situation. I was getting no value on my expression for
averaging 52 separate fields. With the help of everyone here, I figured
out
the problem. If one of the fields in the equation is null, it won't
perform
the query.

This is the new problem. I am averaging attendance records, so, if a week
hasn't come yet, the value is "0". However, this affects the average
adversely. Is there a way to average based on the values that have been
collected?

Here's the equations I have:

sum1:
([wk1])+([wk2])+([wk3])+([wk4])+([wk5])+([wk6])+([wk7])+([wk8])+([wk9])+([wk10]+([wk11])+([wk12])+([wk13])+([wk14])+([wk15]))

average1: [sum1]/52

I guess I'm hoping there's a way for the database to figure out the number
to divide by based on the number of weeks that have value and not use the
"52".

Any thoughts??

Michaela

 

[Allen Browne]

Create a main form for the staff.
Use a subform for the entries.
The subform will automatically pick up the StaffID field from the main form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm with you so far. Next question (I've done the separate table and set
the
relationship): How can I set up my form so that when each person is
entering
each of the 52 "records" they don't have to enter their name and ministry
each time?

Basically, I need it to look something like this:

John Smith Youth Ministry

3/10/05 52
3/17/05 66
3/24/05 72
Average 63

The problem here is that you have a table that's set up like a
spreadsheet
instead of like a relational database.

Whenever you see repeating columns like that, it always indicates that
you
need a related table. For example, if these are salesman's figures for
the
week, you need to have a Salesman table with just the name and bio
details,
and then a second table with fields like this:
SalesmanID relates back to the Salesman table.
WeekNo a number (1 to 53)
Amount the dollar value you want to store.

At the end of the year, each salesman will then have 52 *records* (not
columns) in this table.

With that structure, you can easily sum, count, and average the data
correctly. Just depress the Total button (on the toolbar on query
design),
and it falls together from there.

(In actually, it would be better to have a date field with the first date
of
each week instead of a WeekNo field, because the structure will then keep
working for years.)

Okay, here's the situation. I was getting no value on my expression
for
averaging 52 separate fields. With the help of everyone here, I
figured
out
the problem. If one of the fields in the equation is null, it won't
perform
the query.

This is the new problem. I am averaging attendance records, so, if a
week
hasn't come yet, the value is "0". However, this affects the average
adversely. Is there a way to average based on the values that have
been
collected?

Here's the equations I have:

sum1:
([wk1])+([wk2])+([wk3])+([wk4])+([wk5])+([wk6])+([wk7])+([wk8])+([wk9])+([wk10]+([wk11])+([wk12])+([wk13])+([wk14])+([wk15]))

average1: [sum1]/52

I guess I'm hoping there's a way for the database to figure out the
number
to divide by based on the number of weeks that have value and not use
the
"52".

 

[Guest]

Thanks, you're my hero!

Michaela

Create a main form for the staff.
Use a subform for the entries.
The subform will automatically pick up the StaffID field from the main form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm with you so far. Next question (I've done the separate table and set
the
relationship): How can I set up my form so that when each person is
entering
each of the 52 "records" they don't have to enter their name and ministry
each time?

Basically, I need it to look something like this:

John Smith Youth Ministry

3/10/05 52
3/17/05 66
3/24/05 72
Average 63

The problem here is that you have a table that's set up like a
spreadsheet
instead of like a relational database.

Whenever you see repeating columns like that, it always indicates that
you
need a related table. For example, if these are salesman's figures for
the
week, you need to have a Salesman table with just the name and bio
details,
and then a second table with fields like this:
SalesmanID relates back to the Salesman table.
WeekNo a number (1 to 53)
Amount the dollar value you want to store.

At the end of the year, each salesman will then have 52 *records* (not
columns) in this table.

With that structure, you can easily sum, count, and average the data
correctly. Just depress the Total button (on the toolbar on query
design),
and it falls together from there.

(In actually, it would be better to have a date field with the first date
of
each week instead of a WeekNo field, because the structure will then keep
working for years.)

Okay, here's the situation. I was getting no value on my expression
for
averaging 52 separate fields. With the help of everyone here, I
figured
out
the problem. If one of the fields in the equation is null, it won't
perform
the query.

This is the new problem. I am averaging attendance records, so, if a
week
hasn't come yet, the value is "0". However, this affects the average
adversely. Is there a way to average based on the values that have
been
collected?

Here's the equations I have:

sum1:
([wk1])+([wk2])+([wk3])+([wk4])+([wk5])+([wk6])+([wk7])+([wk8])+([wk9])+([wk10]+([wk11])+([wk12])+([wk13])+([wk14])+([wk15]))

average1: [sum1]/52

I guess I'm hoping there's a way for the database to figure out the
number
to divide by based on the number of weeks that have value and not use
the
"52".