Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: Query to find average between two dates.

    Hi All,

    I want to come up with a query in MS Access which will find the average between two dates. Below is the data sample which I have :

    L1 L2 Retx Prd As of Date Amt
    ABC XYZ AFX XXF 11/29/2010 112123
    IFA SDF BBS STR 11/28/2010 112841
    SFS FDS KBS SDF 11/27/2010 154422
    SLS SDR KSS SLF 11/26/2010 164222
    RES QSS REA ASE 11/25/2010 174212

    Now I have the above data in one of my access database table. I want to come up with an average of Amt between two dates .i.e. As of Date 11/25/2010 to 11/29/2010 and I want the query in such a way that it will ask for the user input for entering the dates. .i.e. From Date to To Date.
    i.e. a input box should reflect on the screen when the user will run the query.

    Thanks a lot for your help in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to use the average function in Access. that can either be as a domain function (DAVG) or as a SQL clause (AVG)
    Calculate the average of the values in a field - Access - Microsoft Office
    you need to limit the rows processed using a WHERE clause, and given that this is a date you could use BETWEEN
    Between...And Operator - Access - Microsoft Office
    to use a parameter
    Why does Access want me to enter a parameter value? - Access - Microsoft Office
    ..personally I prefer to use a form and pull the parameters from that form... why? well it means the same parameters can be used for a report suite without having to retype. you can validate the values to make certain they are sane before running. and you don't bombard you users with questions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2010
    Posts
    84
    Have you thought of this in the attached?
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2009
    Posts
    79

    Query to find average between two dates.

    Hi eremija,

    Thanks a lot for your reply, I have refered to your file and I have slight changes to the same as per my requirement. Here is the revised code :

    Code:
    SELECT sample.L1, sample.L2, sample.Retx, sample.Prd, Avg(sample.Amt) AS AvgOfAmt
    FROM sample
    WHERE (((sample.[As of Date]) Between [StartDate] And [EndDate]))
    GROUP BY sample.L1, sample.L2, sample.Retx, sample.Prd;
    One more thing is that I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items of two different L1 categories on the basis of date. for e.g. two line are of different category but same date will be added then the average will be taken for those 5 line items instead of 10 line items. Can we do this .i.e. first adding the amounts of two lines on the basis of date and then taking the average of it.
    Last edited by abhay_547; 12-08-10 at 08:48.

  5. #5
    Join Date
    Sep 2009
    Posts
    79
    Hi All,

    The query which was posted by eremija is not working as per my requirement. I modified it from my end but still it's not working as per my requirement so I need the help on the same. I think that you guys have misunderstood my issue. So I am attaching a excel file with the calculation which I need to do in the access query. I would request you guys to look into the attached excel file in order to understand my requirement. Please..


    Thanks a lot for your help in advance.
    Attached Files Attached Files
    Last edited by abhay_547; 12-08-10 at 08:56.

  6. #6
    Join Date
    Nov 2010
    Posts
    84
    Excuse me for my poor English. I'm not sure I understand, if you want it attached...
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you down to modify that query so it does meet your requirement
    what steps have you taken to understand what that SQL is doing?

    Code:
    SELECT Avg(ANumericColumn) AS AverageValue
    FROM MyTable
    WHERE MyTable.ADateColumn between CDate([Start Date?]) AND CDate([End Date?]);
    you will however need to change the column names and table name to match your names

    before using this SQL you need to do some testing and make certain it meets your requirement, then you need to do some testing to make certain its robust enough for use in your applications.
    as said before by using a parameter you have no control as to whether the user gives 'sane' information/. using a date picker control forces the user to supply a date, it also gets round some of the issues over date formats
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2009
    Posts
    79
    Hi healdem,

    Thanks a lot for your reply, I have modified the query code posted by you as per my requirement and here is the revised code :

    Code:
    SELECT mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, Avg(mydata.Amt) AS AvgOfAmt
    FROM mydata
    WHERE (((mydata.[As of Date]) Between [startdate] And [Enddate]))
    GROUP BY mydata.L1, mydata.L2, mydata.Retx, mydata.Prd;
    But still I need the help on the below. Can you please have look into the cell H7 in Sheet1 of the attached excel file in order to understand my requirement.

    One more thing is that I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items of two different L1 categories on the basis of date. for e.g. two line are of different category but same date will be added then the average will be taken for those 5 line items instead of 10 line items. Can we do this .i.e. first adding the amounts of two lines on the basis of date and then taking the average of it.
    Thanks a lot for your help in advance.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread...
    dems dare rules

    it would have helped had you said this first

    why do you think you need the grouping?
    rethink your column naming [as of date] is going to bite you at some stage
    whether you use CamelCase, lowercase or UPPERCASE I don't care, but avoid using spaces inside the column names you can either run the words together (asofdate, AsOfDate, ASOFDATE) or use an underscore (as_of_date, As_Of_Date, AS_OF_DATE)

    ..as of now I won't look at your Excel spreadsheet, I'm very very wary of downloading any thing from a source I don't know

    you can probably do what you want using a sub select.
    what I'd suggest you do is learn up about sub selects and try to make it work.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •