Average only if there is a value

[Cranky]

Hi

I have a database of around 400 people who shown an interest in getting
financial assistance. Of those, some have shown the interest, but not
returned an application form, some have returned the form and are
awaiting processing, and some have been processed. The information that
defines these are stored in fields called 'DateFormRecd',
'HBW£','HBA£','CTW£' and 'CTA£'

How do I structure a query to give me an average of only those who've
returned the form?

Thanks in advance

S

 

[Joseph Meehan]

Hi

I have a database of around 400 people who shown an interest in
getting financial assistance. Of those, some have shown the interest,
but not returned an application form, some have returned the form and
are awaiting processing, and some have been processed. The
information that defines these are stored in fields called
'DateFormRecd', 'HBW£','HBA£','CTW£' and 'CTA£'

How do I structure a query to give me an average of only those who've
returned the form?

Thanks in advance

S

I am not clear from what you wrote what criteria or set of criteria
would select only records with value. In any case, you need to select a
criteria that will select only records with value then your average will
only include those records.

 

[Cranky]

I am not clear from what you wrote what criteria or set of criteria
would select only records with value. In any case, you need to select a
criteria that will select only records with value then your average will
only include those records.

Hi Joseph, thanks for replying.

It would be to average those only where the 'DateFormRecd' field is
filled.

S

 

[Guest]

What do you want an average of. Can't really see how you can get an
"average" of people who have sent froms back or any other items in your list.
You could (for example) have a percentage of the total forms sent out, etc
but an average is simply a sum the total number / a count of the items you
wish to average and for that you need a number that you wih to divide the
total by.

Have you some more information or, better still a small sample of the data.

 

[Cranky]

Sorry, I'm being dense.

It's a benefit take-up campaign. A flier went out with Council Tax
bills (UK) asking people to return the tear-off if they were
interested. Those sent in had a full application form sent to them.

Those who returned their form are either awaiting assessment, or have
been assessed, for Housing Benefit, and/or Council Tax benefit. I have
to be able to show the elected members how much benefit has been paid
out as a result of the scheme. I can do the 'Sum of arrears paid' bit
for HB and CT easily, but hten I grind to a halt.

So, _of those who returned the application form (DateFormRec'd)_ I need
to know the average of each of the following:

Weekly Housing Benefit (HBW£)
Housing Benefit Arrears (HBA£)
Weekly Council Tax Benefit (CTW£)
Council Tax Benefit Arrears (CTA£)

There are around 400 people who showed an interest, of which 60 have
returned the full forms. So far around 30 have been assessed. I want to
be able to see these averages change as each new claim is assessed.
These aren't set rate payments, you see; each claim could get very
different amounts.

Cheers

S

 

[Guest]

Hi Cranky

There are many way to get the average you are after (where in the sql -
=iif(ect etc). But it would be a good idea to try and keep things as simply
as possible. So why not simply put in some criteria in your query to show
only who has sent back thier forms - if you have a yes no box on your form
you could set 1 as the criteria. Or you could use >1 for anyone who has
recieved anything ( or <1 if you work in arrears).
Once you have only the people you want simply press the totals option button
at the top of the screen and see what you get (there is an avg option on the
drop down)

Dont forget you can always use the query to feed another query (with totals)

Do I get a reduction in my council tax for this ?? (smile)

 

[Joseph Meehan]

Hi Joseph, thanks for replying.

It would be to average those only where the 'DateFormRecd' field is
filled.

S

Depending on the filed type that might be >0 or is not null etc.

 

[Cranky]

So why not simply put in some criteria in your query to show
only who has sent back thier forms - if you have a yes no box on your form
you could set 1 as the criteria. Or you could use >1 for anyone who has
recieved anything ( or <1 if you work in arrears).
Once you have only the people you want simply press the totals option button
at the top of the screen and see what you get (there is an avg option on the
drop down)

Great; thanks. I'll give that a go.
*update* Yup, works perfectly. Thank you!

Do I get a reduction in my council tax for this ?? (smile)

Hah! Nice try, wiseguy.
No. You don't.

Steve