How to do this using MS ACCESS

[Mark]

I am producing a report that requires me, among many other things, to
calculate the average contracts for about 1000 companies during the yr
2003. The "quirk" is that all the companies do not have the same
number of months. When using DB2 the below code counts the average
contracts of each group for 2003. What code can I use in MS ACCESS to
produce the same result?

SELECT

COMPANY_NAME,
SUM(CONTRACTS)/COUNT(DISTINCT FIRST_DAY_OF_MONTH) contracts

FROM TABLE
Thanks

[Larry Linson]

Instead of expecting us to read DB2 SQL, could you just state the problem a
little more clearly? Access has Totals Queries with Group By and Avg, so I
suspect not only can Access do what you want, but do so easily without your
actually having to write your own SQL. There's a dandy Query Builder.

Specifically I'm not quite sure I know what you mean by "calculate the
average contracts for ... during the yr" and "... do not have the same
number of months".

Larry Linson
Microsoft Access MVP

"Mark" <mf******@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...

I am producing a report that requires me, among many other things, to
calculate the average contracts for about 1000 companies during the yr
2003. The "quirk" is that all the companies do not have the same
number of months. When using DB2 the below code counts the average
contracts of each group for 2003. What code can I use in MS ACCESS to
produce the same result?

SELECT

COMPANY_NAME,
SUM(CONTRACTS)/COUNT(DISTINCT FIRST_DAY_OF_MONTH) contracts

FROM TABLE
Thanks

[Mark]

"> Specifically I'm not quite sure I know what you mean by "calculate
the

average contracts for ... during the yr" and "... do not have the same
number of months"."

First I would like to thank you for responding to my question and
appologize for my lack of clarity. I have been using DB2 on a
mainframe for about 3 years now at work.(I am an actuary for an
insurance company). Recently I had a OBDC driver installed on my PC. I
have never used ACCESS before so I thought this might be a good
oportunity to learn it. Lets say that I need to count the average
number of monthly contracts for Wal-Mart("contracts" are the insured
individuals or families.) Wal-Mart has been with my company for the
entire year and this is October so I have 10 months of contracts to
count/average. I add up the number of contracts for each month, which
fluctuates, and divide by the number of months to get an average. If
all the 100 or so groups had a full 10 months of contracts it would be
simple. I would just divide the sum for each group by 10. The first
part of the problem arises when a group like United Air or Food Lion
comes on board in April or May. In this case I do not have 10 months
of data so I cant just divide by 10. I have to divide by 5 or 4
months respectively. The second part of the problem is that each group
can have thousands of entries on the DB2 table that I am hitting. To
count the months I took advantage of the fact that every month has a
1st day. Jan 1st, Feb 1st....Oct 1st. I count the number of distinct
1st days of each month and divide it into the total number of
contracts. This simple technique gives me accurate results when using
DB2. More about the report: The report is simple in description but
very difficult in practice(the story of my life) I am looking at all
the monies we charge a group(admin fees) and all the monies we are
paying out(primarily network access fees) on a per state basis. So I
really need to know the average number of contracts in each
state/country(which I break out later). The contract counts are a
VERY small part of the report. The DB2 proc that I hope to replace
with access runs 17 queries pulling data from 9 different DB2 tables.
When all is said and done the final report is about 250 pages. So
what I need to know is how to calculate the average number of
contracts per month over the number of months in the current year that
each particular group has been a client of my company.(Wow thats a
mouth full) One more thing I know that my method is probably not the
best way of accomplishing the task at hand but I am "self taught" so
have had no formal SQL training thus a limited tool box. In college I
was a physics/math major programming primarily with fortran and LaTex.

Thanks