Calculating an Average

  • Thread starter Thread starter Nona
  • Start date Start date
N

Nona

I am trying to calculate an average. The database I am working on tracks
contracts -- the services provided per the contract, the invoice amount, the
invoice date, the paid date, the paid amount etc. plus a lot of other fields
not relevant to this question.

On a data entry form, fields capture the billing date and the paid date for
each of twelve months for each contract.

Management wants to know the average number of days between the billing date
and the payment date for each contract.

A query calculates the number of days between the billing and the payment
received, so I have the number of days for each month.

I need the average of the twelve months for each contract, but I don’t know
how to calculate the average.

This seems like it would be a simple task, but for some reason I cannot
figure it out. Could someone help, please!

Thank you!
 
Not quite clear but lets suppose you want to get the average days between
billing date and payment date for each contract. You already have the
daysBetween for each contract transaction by contract. So in the query
design panel add the ContractID (whatever you call it) as a field and add
the DaysBetween as a field.

From the toolbar select totals. On the "Total:" line of the query, under
ContractID select
"Group By". Under the DaysBetween, select "Avg"

Regards

Kevin
 
Thanks for responding, Kevin. Maybe I haven’t designed, nor explained, this
the right way. My data entry has a billing date and a payment date for each
month for each of 21 contracts.

So I have the contract name “Billy Bob†with 12 billing and 12 payments
dates. The datediff function calculates the number of days between bill and
pays for each month. That results in “Julydaysâ€, “Augdaysâ€, etc. - the
difference for each bill and pay for each month for that contract.

A query sums the Julydays, Aug days, etc. for the 12 months, but I can’t get
the average of those 12 figures.

What am I doing wrong?

Many thanks for your help.
 
Nona,
Can you provide your table structure, significant records, and how they
should appear?
Do you have only one record per contract or does each contract have 12
records?
 
The database has a number of tables. The two relevant ones for the averaging
are the Contracts table, which holds the names, contract dates, contract amt,
billing, payments, invoice type, etc. All the contracts pertain to services
provided to people with disabilities. Some contracts are for set amounts;
others are for hourly services, with a maximum limit to the amount that can
be billed.

Th Services table holds all the data related to the actual service provision
per the contract (dates, type of service, number of hours, etc.) Another
table holds the rates paid for the various service.

There are 21 contracts. At management request, each record is the actual
invoice that is billed for the contract to the contractor (another table).
The invoices very, depending on the service, but most are computed by the
hours and the rate of service for the type of service provided.

Each contract has at least 12 invoices – one for each month. If a contract
covers several different types of service, then there are multiple invoices
under the same contract (just under 500 records). These are collected via a
crosstab query for each month for each contract.

When the db was first established a couple of years ago, they wanted a way
to track the implementation of the contract dollars so that all the available
dollars were applied but not overspend the contract amount. All this has
worked well.

This is a recent request for the average number of days. The only way I have
been able to figure out how to do this is to take the data entered manually
in a data entry form – the billing date and the paid date for each contract
for each month.

If there is a better way, I would really appreciate knowing about it! Thanks
for your time and your help!
 
Can you get a query that has Contract, BillingDate, and corresponding
PaymentDate as a row? If so, you could use DateDiff() to get the difference.
Then it should be simple to average the difference over any time period.
 
I understand and I agree that it should be simple! Here is my query where I
get the number of days for each month and then a total number of days. Please
tell me how to get the average, if that's possible! (You may notice that
this query is incomplete because the pdDates for the last six months are not
included. I was testing the query with first six months of July data.)

SELECT tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6,
DateDiff("d",[BillDate1],[pd1Date]) AS JulyDays,
DateDiff("d",[BillDate2],[pd2Date]) AS AugDays,
DateDiff("d",[BillDate3],[pd3Date]) AS SepDays,
DateDiff("d",[BillDate4],[pd4Date]) AS OctDays,
DateDiff("d",[BillDate5],[pd5Date]) AS NovDays,
DateDiff("d",[BillDate6],[pd6Date]) AS DecDays,
Sum([JulyDays]+[AugDays]+[SepDays]+[OctDays]+[NovDays]+[DecDays]) AS TotalDays
FROM tblContracts
GROUP BY tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6
ORDER BY tblContracts.ContractName;

Thank you for taking your time to do this.
 
I understand and I agree that it should be simple! Here is my query where I
get the number of days for each month and then a total number of days. Please
tell me how to get the average, if that's possible! (You may notice that
this query is incomplete because the pdDates for the last six months are not
included. I was testing the query with first six months of July data.)

SELECT tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6,
DateDiff("d",[BillDate1],[pd1Date]) AS JulyDays,
DateDiff("d",[BillDate2],[pd2Date]) AS AugDays,
DateDiff("d",[BillDate3],[pd3Date]) AS SepDays,
DateDiff("d",[BillDate4],[pd4Date]) AS OctDays,
DateDiff("d",[BillDate5],[pd5Date]) AS NovDays,
DateDiff("d",[BillDate6],[pd6Date]) AS DecDays,
Sum([JulyDays]+[AugDays]+[SepDays]+[OctDays]+[NovDays]+[DecDays]) AS TotalDays
FROM tblContracts
GROUP BY tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6
ORDER BY tblContracts.ContractName;

Thank you for taking your time to do this.
Click to expand...

The reason you're having so much trouble is that your table design is *simply
wrong*. It's a good spreadsheet, but just incorrect for a normalized table! If
you have a one (contract) to many (bill dates or paid dates) you should have
two tables in a one to many relationship: e.g. a table of Bills, with fields
for BillID (autonumber primary key), ContractID, BillDate, Amount, and other
details about that bill); payments should be in another table, with PaymentID
(primary key), ContractID, PaymentDate, Amount, etc. You may or may not want
to link specific payments to a specific bill, that's a business decision on
your part.

Take a look at some of the resources here for setting up a properly normalized
set of tables; you'll find it makes your life much easier (once you get up the
steep and rocky learning slope).

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Hi Nona,

So what you want is the average of each payment lag for each contract, for
each month.

So change what I said before to add a grouping for the Month. Something
like:

From the toolbar select totals.
On the "Total:" line of the query,
under ContractID select "Group By".
Under the MonthOfData select "GroupBy"
Under the DaysBetween, select "Avg"

Yes?

Regards

Kevin
 
John W. Vinson said:
I understand and I agree that it should be simple! Here is my query where
I
get the number of days for each month and then a total number of days.
Please
tell me how to get the average, if that's possible! (You may notice that
this query is incomplete because the pdDates for the last six months are
not
included. I was testing the query with first six months of July data.)

SELECT tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6,
DateDiff("d",[BillDate1],[pd1Date]) AS JulyDays,
DateDiff("d",[BillDate2],[pd2Date]) AS AugDays,
DateDiff("d",[BillDate3],[pd3Date]) AS SepDays,
DateDiff("d",[BillDate4],[pd4Date]) AS OctDays,
DateDiff("d",[BillDate5],[pd5Date]) AS NovDays,
DateDiff("d",[BillDate6],[pd6Date]) AS DecDays,
Sum([JulyDays]+[AugDays]+[SepDays]+[OctDays]+[NovDays]+[DecDays]) AS
TotalDays
FROM tblContracts
GROUP BY tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6
ORDER BY tblContracts.ContractName;

Thank you for taking your time to do this.
Click to expand...

The reason you're having so much trouble is that your table design is
*simply
wrong*. It's a good spreadsheet, but just incorrect for a normalized
table! If
you have a one (contract) to many (bill dates or paid dates) you should
have
two tables in a one to many relationship: e.g. a table of Bills, with
fields
for BillID (autonumber primary key), ContractID, BillDate, Amount, and
other
details about that bill); payments should be in another table, with
PaymentID
(primary key), ContractID, PaymentDate, Amount, etc. You may or may not
want
to link specific payments to a specific bill, that's a business decision
on
your part.

Take a look at some of the resources here for setting up a properly
normalized
set of tables; you'll find it makes your life much easier (once you get up
the
steep and rocky learning slope).

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
Click to expand...
 
salut nona moi lakhdar celibataire age 50 ans jamais marier ton num mobil
00213778994111 et 002132547
 
Thank you. I will definitely study these!
--
Nona


John W. Vinson said:
I understand and I agree that it should be simple! Here is my query where I
get the number of days for each month and then a total number of days. Please
tell me how to get the average, if that's possible! (You may notice that
this query is incomplete because the pdDates for the last six months are not
included. I was testing the query with first six months of July data.)

SELECT tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6,
DateDiff("d",[BillDate1],[pd1Date]) AS JulyDays,
DateDiff("d",[BillDate2],[pd2Date]) AS AugDays,
DateDiff("d",[BillDate3],[pd3Date]) AS SepDays,
DateDiff("d",[BillDate4],[pd4Date]) AS OctDays,
DateDiff("d",[BillDate5],[pd5Date]) AS NovDays,
DateDiff("d",[BillDate6],[pd6Date]) AS DecDays,
Sum([JulyDays]+[AugDays]+[SepDays]+[OctDays]+[NovDays]+[DecDays]) AS TotalDays
FROM tblContracts
GROUP BY tblContracts.ContractName, tblContracts.PD1, tblContracts.PD2,
tblContracts.PD3, tblContracts.PD4, tblContracts.PD5, tblContracts.PD6,
tblContracts.PD7, tblContracts.PD8, tblContracts.PD9, tblContracts.PD10,
tblContracts.PD11, tblContracts.PD12, tblContracts.BK1, tblContracts.BK2,
tblContracts.BK3, tblContracts.BK4, tblContracts.BK5, tblContracts.BK6,
tblContracts.BK7, tblContracts.BK8, tblContracts.BK9, tblContracts.BK10,
tblContracts.BK11, tblContracts.BK12, tblContracts.Pd1Date,
tblContracts.Pd2Date, tblContracts.Pd3Date, tblContracts.Pd4Date,
tblContracts.Pd5Date, tblContracts.Pd6Date, tblContracts.BillDate1,
tblContracts.BillDate2, tblContracts.BillDate3, tblContracts.BillDate4,
tblContracts.BillDate5, tblContracts.BillDate6
ORDER BY tblContracts.ContractName;

Thank you for taking your time to do this.
Click to expand...

The reason you're having so much trouble is that your table design is *simply
wrong*. It's a good spreadsheet, but just incorrect for a normalized table! If
you have a one (contract) to many (bill dates or paid dates) you should have
two tables in a one to many relationship: e.g. a table of Bills, with fields
for BillID (autonumber primary key), ContractID, BillDate, Amount, and other
details about that bill); payments should be in another table, with PaymentID
(primary key), ContractID, PaymentDate, Amount, etc. You may or may not want
to link specific payments to a specific bill, that's a business decision on
your part.

Take a look at some of the resources here for setting up a properly normalized
set of tables; you'll find it makes your life much easier (once you get up the
steep and rocky learning slope).

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
Click to expand...
 
Back
Top