About Average

[suratbali]

Hi… I Have a problem about average
This the example that explain my problem :

Date Amount Customer
01/01/03 245 John
06/01/03 623 John
06/01/03 823 John
Total 1691

I Want to make Average that result is 845.5 (1691 / 2). If I use the Aggregate Function in Access I will have result : 563.67 (1691 / 3).

How Do I Do That ?? Thank’s

 

[Steve101]

(a) Create a query to determine the number of distinct dates associated with a customer; ie.
SELECT DISTINCT [Customer], [Dt]
FROM tblYourTable;

Replace the tablename and fieldnames as appropriate

(b) I'll assume that the query is called qryYourQuery
Sample code then is:

ThisCustomer = "John"
Numerator = DSum("Amount", "tblYourTable", "Customer = '" & ThisCustomer & "'&quot
Denom = DCount("dt", "qryYourQuery", "Customer = '" & ThisCustomer & "'&quot
If Denom <> 0 Then
YourAve = Numerator / Denom
Else
YourAve = 0
End If

This should give you the distinct daily average that you want.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[GDGarth]

I set up a table (YourTable), with three fields (DateField, Amound, Customer). Then I created two queries (YourTable Query1 and YourTable Query2). The SQL's for those are here.

Query1:

SELECT YourTable.DateField, Sum(YourTable.Amount) AS SumOfAmount, YourTable.Customer
FROM YourTable
GROUP BY YourTable.DateField, YourTable.Customer;

Query2:

SELECT Avg([YourTable Query1].SumOfAmount) AS AvgOfSumOfAmount, [YourTable Query1].Customer
FROM [YourTable Query1]
GROUP BY [YourTable Query1].Customer;


This seems to work OK on the test table I set up.

 

[NaoTriste]

I presume that what you are saying is that you want to calculate the average amount per day and therefore you are sub-totalling the amount for the 06/01/03 first.
If that is the case you need a sub-query as you can't do it in one go.
I don't know how well versed you are with Sql but if you prefer to use the generator do:

Query1 - SELECT Table1.date, Table1.customer, Sum(Table1.amount) AS SumOfamount
FROM Table1
GROUP BY Table1.date, Table1.customer;

Query2 - SELECT Query1.customer, Avg(Query1.SumOfamount) AS AvgOfSumOfamount
FROM Query1
GROUP BY Query1.customer;

Result of query2 is :Customer Average For Customer
-------------------------------

John 845.50

 

[MichaelRed]

Actually, I'm just curious as to WHY you think the value should EVER be 845.5 (1691 / 2)? The 1691 is OBVIOUSLY the sum of all three amounts, but given that there ARE three (3) values, the division by two (2) is not anything like an AVERAGE

To obtain an average by date can easily be accomplished with a simple aggregate query. To obtain the value you seem to want 'just doesn't compute', you might as well (in my opinion) just manually enter it.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Steve101]

Michael,

Wanting to find out the average amount PER DAY (there are two days), and is quite different to the average amount per entry (there are 3 entries). They are both legitimate business metrics; hense I guess the post in the first place.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

but ... but ... but ... go back to the original post AND my first post. The specifiction is the Total of ALL THREE values divided by the the TWO WHATEVERS

I 'guess' you could describe it as the average of all sales if they occured on fewer days then there were sales for?? If THAT makes sense to you -or you have a more coherent explination- I'll be happy to hear it. Until then (or some one else volunteers) I will remain curious.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GDGarth]

Look at the original data:

Date Amount Customer
01/01/03 245 John
06/01/03 623 John
06/01/03 823 John
Total 1691

Note that there are three entries, but only two dates. It looks as though he wants to sum the total sales for each date, then compute an average for each day per customer.

I don't think it's odd that he wants it summarized this way - I can imagine several reasons for wanting this data, instead of the average per transaction.

 

[Steve101]

Thanks GDGarth; my sentiments exactly;

Michael; the two &quot;WHATEVERS&quot;, are two dates - pretty clear to me, as is the requirement to produce a daily average. Sorry, your first post does'nt make a lot of sense to me. Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[suratbali]

Thanks alot for the help

 

[MichaelRed]

O.K. one last attempt to communicate.

Assuming this is a standard 'business' operation and -at least for the sample shown- U.S. Date format.

The two dates represent represent a Wednesday (New Years's Day) and the following Monday. So the ?daily? average is for the three sale which occured over 6 calendar days and perhaps between four and six (?normal?) working days, depending on the 'business rules'.

To me, calculating the &quot;average&quot; for just the two days where sales actually occured doesn't compute to a daily average of sales, unless the business rules (weirdly) mark only these two dates as 'open for business'. Further, it SEEMS LIKE -even assuming the preceeding is 'correct'- that another problem will be encountered, as there is not a 'normal' pattern to the business days, and it may be possible to have a business day without any sales, so the weird ?average? would perhaps need to include these.

Is this a still &quot; ... does'nt make a lot of sense ... &quot;? For me the original concept is one.

What is the point of an average ~~ the sum of all of this over the selected values of that? ~~ Sum of Sales / Days where sales actually occured?

A real &quot;Daily Average&quot; would be &quot;Sum of Sales / Business Days&quot;?

I gtuess that suratbali is hapy with your answer(s), so it must not be worth persuing, I'm just 'weirded out' with the concept and trying to understand this new prinicpal of bussiness &/or accounting.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GDGarth]

MichaelRed:

I'm having trouble following your train of thought. It looks to me like the dates are Jan 1 and Jun 1, but that could be different, depending on the date format used.

Regardless, here's a scenario that makes this summary worthwhile:

Let's say you're in a service organization that provides their service when requested by the customer. The time frame may vary from several months between calls to several in one day.

Let's say the customers are complaining about paying for two service calls in the same day - they want to pay one price, and if you have to come back and finish the job, it's at your expense.

The boss, in order to determine just how much to charge for a service call, might want exactly this kind of summary.

Now, I don't know what the original poster wanted the summary for. I don't use exactly this type of summary in my job, but that doesn't mean nobody else should, does it?

 

[MichaelRed]

and trying to understand this new prinicpal of bussiness &/or accounting]/i] about sayait all.

I have prticipated in a fair number of businesses, including service operations 'similar' to what you describe, including frequency of X' calculation, averages of this, that, those and whatevers - w/o encountering this particular calculation.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GDGarth]

MichaelRed,

OK, I won't argue the point anymore. You're certainly entitled to your opinion.

 

[Steve101]

I sell product at a market on ad hoc days. I made one sale on 1/1/3. I made two sales on 6/1/3 . Wanting to know the daily average of the days I make sales at the market is both clear and legitimate (... who cares whether its US dates or not). If I happen to go to market on a third day, and make NO sales then I might have a problem in terms of my average, as no entry will exist; however, being a good diligent merchant, I might enter a single sale with a zero value, to maintain the integrity of my desired calculation.

Anyway, I wont flog a dead horse any longer. Nice one guys, there's actually quite a lot of substance in this post if one reads between the lines. Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Craig0201]

Have to admit, had trouble working out the concept at first....

Isn't this &quot;Sum of sale/number of days on which sales occurred?&quot; or similar? So if I buy a £200 product and a £100 product on one day and a £50 on a second day, I have bought £350 worth of goods over two days. Hence by average total value of purchase by day is £175?

Craig